Excel 2010 Bible

Chapter 12: Working with Dates and Times

267

Here’s the formula modified to use the current date:

=DATE(YEAR(TODAY()),12,31)-TODAY()

When you enter either formula, Excel applies date formatting to the cell. You need to apply a non-

date number format to view the result as a number.

To convert a particular day of the year (for example, the 90th day of the year) to an actual date in a

specified year, use the following formula, which assumes that the year is stored in cell A1 and that

the day of the year is stored in cell B1:

=DATE(A1,1,B1)

Determining the day of the week .............................................................................

The WEEKDAY function accepts a date argument and returns an integer between 1 and 7 that cor-

responds to the day of the week. The following formula, for example, returns 7 because the first

day of the year 2011 falls on a Saturday:

=WEEKDAY(DATE(2011,1,1))

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.

Tip
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 date of the most recent Sunday .....................................................

You can use the following formula to return the date for the previous Sunday (or any other day of

the week). If the current day is a Sunday, the formula returns the current 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 June 1, 2010. The