Microsoft Office Tutorials and References

In Depth Information

**Tip 38: Calculating a Person’s Age**

Calculating a Person’s Age

Calculating a person’s age is a bit tricky because the calculation depends on not only the current year

but also the current day. And then you have to consider the complications resulting from leap years.

In this tip, I present three methods to calculate a person’s age. These formulas assume that cell B1

contains the date of birth (for example, 2/16/1952) and that cell B2 contains the current date (calcu-

lated with the TODAY function).

Method 1

The following formula subtracts the date of birth from the current date and divides by 365.25. The

INT function then eliminates the decimal part of the result:

=INT((B2-B1)/365.25)

This formula isn’t 100 percent accurate because it divides by the average number of days in a year.

For example, consider a child who is exactly one year old. This formula returns 0, not 1.

Method 2

A more accurate way to calculate age uses the YEARFRAC function:

=INT(YEARFRAC(B2, B1))

The YEARFRAC function is normally used in financial calculations, but it works just fine for calculating

ages. This function calculates the fraction of the year represented by the number of whole days

between two dates. Using the INT function eliminates the fraction and returns an integer that

represents full years.

Method 3

The third method for calculating age uses the DATEDIF function. This undocumented function isn’t

described in the Excel Help system:

=DATEDIF(B1,B2,”Y”)