Microsoft Office Tutorials and References

In Depth Information

**Calculating a person’s age**

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.

16

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))

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.

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

“Where’s the
DATEDIF
Function?”)

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

Where’s the DATEDIF Function?

One of Excel’s mysteries is the
DATEDIF
function. You may notice that this function doesn’t 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. The function has been available since Excel 5, but Excel 2000 is the only version that ever

documented it in its Help system.

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

quotation marks.)

Continues