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).
Search JabSto ::




Custom Search