Microsoft Office Tutorials and References

In Depth Information

**Date-Related Functions**

The following formula returns the number of days remaining in the year from a particular date

(assumed to be in cell A1):

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

When you enter either of these formulas, 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 formula that follows. This formula 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

corresponds 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.

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. 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)