Microsoft Office Tutorials and References
In Depth Information
Determining the day of the year
continued
Unit Code
Returns
"y"
The number of complete years in the period
"m"
The number of complete months in the period
"d"
The number of days in the period
"md"
The difference between the days in start_date and end_date . The months
and years of the dates are ignored.
"ym"
The difference between the months in start_date and end_date . The days
and years of the dates are ignored.
"yd"
The difference between the days of start_date and end_date . The years of
the dates are ignored.
The start_date argument must be earlier than the end_date argument or else the function returns an error.
Determining the day of the year
January 1 is the ﬁ rst day of the year, and December 31 is the last day. But what about all
those days in between? The following formula returns the day of the year for a date stored
in cell A1:
=A1-DATE(YEAR(A1),1,0)
Here’s a similar formula that returns the day of the year for the current date:
=TODAY()-DATE(YEAR(TODAY()),1,0)
The following formula returns the number of days remaining in the year after a particular
date (assumed to be in cell A1):
=DATE(YEAR(A1),12,31)-A1
Here’s the formula modiﬁ ed to use the current date:
=DATE(YEAR(TODAY()),12,31)-TODAY()
When you enter either formula, Excel applies date formatting to the cell. You need to apply
a nondate 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 speciﬁ ed year, use the following formula, which 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)
This formula takes advantage of the fact that the DATE function accepts invalid dates
(such as the 90th day of January) and adjusts automatically. The 90th day of January is
actually the 90th day of the year.
Search JabSto ::

Custom Search