Microsoft Office Tutorials and References

In Depth Information

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

(calculated 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 gets rid of the fraction and returns an integer.

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