Excel 2010 Bible

Part II: Working with Formulas and Functions

266

Determining the day of the year ..............................................................................

January 1 is the first 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

continued

DATEDIF is a handy function that calculates the number of days, months, or years between two dates.

The function takes three arguments: start_date, end_date, and a code that represents the time unit

of interest. Here’s an example of a formula that uses the DATEDIF function (it assumes cells A1 and A2

contain a date). The formula returns the number of complete years between those two dates.

=DATEDIF(A1,A2,”y”)

The following table displays valid codes for the third argument. (You must enclose the codes in quota-

tion marks.)

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.