Microsoft Office Tutorials and References

In Depth Information

**Determining Specific Dates**

Determining Specific Dates

This tip contains a number of useful formulas that return a specific date.

Determining the day of the year

January 1 is the first day of the year, and December 31 is the last day. What about all those days

between them? The following formula returns the day of the year for a date stored in cell A1:

=A1-DATE(YEAR(A1),1,0)

For example, if cell A1 contains the date February 16, 2010, the formula returns 47 because that

date is the 47th day of the year.

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

Determining the day of the week

If you need to determine the day of the week for a date, the WEEKDAY function does the job.

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 6 because the

first day of the year 2010 falls on a Friday:

=WEEKDAY(DATE(2010,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

Keep in mind that the cell really contains the full date, not just the day number.