Microsoft Office Tutorials and References

In Depth Information

The WEEKDAY function uses an optional second argument that specifies the day numbering system 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 week of the year

To determine the week of the year for a date, use the WEEKNUM function. The following formula returns the

week number for the data in cell A1:

=WEEKNUM(A1)

When you use WEEKNUM function, you can specify a second optional argument to indicate the type of week

numbering system you prefer. The second argument can be one of ten values, which are described in the Help

system.

Excel 2013 includes a new function, ISOWEEKNUM. This function returns the same res-

ult as WEEKNUM with a second argument of 21.

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 a particular date. The formula assumes that cell A1 con-

tains a date and that cell A2 contains a number between 1 and 7 (1 for Sunday, 2 for Monday, and so on).