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.

Using

Using
WEEKDAY

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

Syntax

=WEEKDAY(serial_number,return_type)

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

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,

DATEVALUE("1/30/1998")).

•
return_type

return_type
—
This is a number that determines the type of return

value:

•
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

week.