Microsoft Office Tutorials and References

In Depth Information

**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
3

because the ﬁ rst day of the year 2013 falls on a Tuesday:

16

=WEEKDAY(DATE(2013,1,1))

The
WEEKDAY
function uses an optional second argument that speciﬁ es 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

function returns the week number for the data in cell A1:

=WEEKNUM(A1)

When you use the
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 includes a new function,
ISOWEEKNUM
. This function returns the same result as
WEEKNUM
with a second

argument of
21
. Use this function if your organization uses the ISO week date system for numbering weeks (for i scal

or other tracking purposes), including weeks with leap years.

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:

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

To modify this formula to ﬁ nd the date of a day other than Sunday, change the
1
to a

different number between
2
(for Monday) and
7
(for Saturday).