Microsoft Office Tutorials and References
In Depth Information
Using WEEKDAY to Group Dates by Day of the Week
Frustratingly, Excel does not automatically format the results of this
function as a time. Column B shows the result as Excel presents it. Column C
shows the same result after a time format has been applied.
to Group Dates by Day of the Week
The WEEKDAY function would not be so intimidating if people could just agree
how to number the days. This one function can give eight different results,
just for Monday.
WEEKDAY to Group Dates by Day of the Week
The WEEKDAY function returns the day of the week corresponding to a date.
The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by de-
fault. This function takes the following arguments:
serial_number — This is a sequential number that represents the date
of the day you are trying to find. Dates may be entered as text strings
within quotation marks (for example, "1/30/1998", "1998/01/30"), as
serial numbers (for example, 35825, which represents January 30,
1998), or as results of other formulas or functions (for example,
return_type — This is a number that determines the type of return
• If return_typeis 1 or omitted, WEEKDAY works like the calendar
on your wall. Typically, calendars are printed with Sunday on the
left and Saturday on the right. The default version of WEEKDAY
numbers these columns from 1 through 7.
• If return_typeis 2, you are using the biblical version of
WEEKDAY. In the biblical version, Sunday is the seventh day.
Working backward, Monday must occupy the 1 position.
• If return_typeis 3, you are using the accounting version of
WEEKDAY. In this version, Monday is assigned a value of 0, fol-
lowed by 1 for Tuesday, and so on. This version makes it very easy
to group records by week. If cell A2 contains a date, then
A2-WEEKDAY(A2,3) converts the date to the Monday that starts the