Microsoft Office Tutorials and References
In Depth Information
The following formula returns the number of days remaining in the year from a particular date
(assumed to be in cell 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.
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:
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:
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