Excel 2010 Bible

Chapter 12: Working with Dates and Times

265

Calculating the number of years between two dates ................................................

The following formula calculates the number of years between two dates. This formula assumes

that cells A1 and B1 both contain dates:

=YEAR(A1)-YEAR(B1)

This formula uses the YEAR function to extract the year from each date and then subtracts one year

from the other. If cell B1 contains a more recent date than the date in cell A1, the result is negative.

Note that this function doesn’t calculate full years. For example, if cell A1 contains 12/31/2010 and

cell B1 contains 01/01/2011, the formula returns a difference of one year even though the dates

differ by only one day. See the next section for another way to calculate the number of full years.

Calculating a person’s age

A person’s age indicates the number of full years that the person has been alive. The formula in the

previous section (for calculating the number of years between two dates) won’t calculate this value

correctly. You can use two other formulas, however, to calculate a person’s age.

The following formula returns the age of the person whose date of birth you enter into cell A1.

This formula uses the YEARFRAC function.

=INT(YEARFRAC(TODAY(),A1,1))

Note
In versions prior to Excel 2007, the YEARFRAC function was available only when the Analysis ToolPak add-in
was installed. The function is now part of Excel, and does not require an add-in. n

The following formula uses the DATEDIF function to calculate an age. (See the sidebar, “Where’s

the DATEDIF Function?”)

=DATEDIF(A1,TODAY(),”Y”)

One of Excel’s mysteries is the DATEDIF function. You may notice that this function does not appear in

the drop-down function list for the Date & Time category, nor does it appear in the Insert Function

dialog box. Therefore, when you use this function, you must always enter it manually.

The DATEDIF function has its origins in Lotus 1-2-3, and apparently Excel provides it for compatibility

purposes. For some reason, Microsoft wants to keep this function a secret. The function has been avail-

able since Excel 5, but Excel 2000 is the only version that ever documented it in its Help system.

continued

Where’s the DATEDIF Function?