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.