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)

Search JabSto ::

Custom Search