Determining the day of the week

Determining the day of the week

The WEEKDAY function accepts a date argument and returns an integer between 1 and 7 that
corresponds to the day of the week. The following formula, for example, returns 7 because the
first day of the year 2011 falls on a Saturday:

  =WEEKDAY(DATE(2011,1,1))

The WEEKDAY function uses an optional second argument that specifies the day numbering sys-
tem for the result. If you specify 2 as the second argument, the function returns 1 for Monday, 2
for Tuesday, and so on. If you specify 3 as the second argument, the function returns 0 for
Monday, 1 for Tuesday, and so on.

             You can also determine the day of the week for a cell that contains a date by applying a
              custom number format. A cell that uses the following custom number format displays
              the day of the week, spelled out:

                 dddd

Determining the date of the most recent Sunday

You can use the following formula to return the date for the previous Sunday. If the current day is
a Sunday, the formula returns the current date. (You will need to format the cell to display as a
date.)

  =TODAY()-MOD(TODAY()-1,7)

To modify this formula to find the date of a day other than Sunday, change the 1 to a different
number between 2 (for Monday) and 7 (for Saturday).

Determining the first day of the week after a date

This next formula returns the specified day of the week that occurs after a particular date. For
example, use this formula to determine the date of the first Monday after June 1, 2010. The for-
mula assumes that cell A1 contains a date and that cell A2 contains a number between 1 and 7 (1
for Sunday, 2 for Monday, and so on).

  =A1+A2-WEEKDAY(A1)+(A2<WEEKDAY(A1))*7

If cell A1 contains June 1, 2010 (a Tuesday), and cell A2 contains 7 (for Saturday), the formula
returns June 5, 2010. This is the first Saturday after June 1, 2010.