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.

Search JabSto ::

Custom Search