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).
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:
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.
A more accurate way to calculate age uses the YEARFRAC function:
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.
The third method for calculating age uses the DATEDIF function. This undocumented function isn’t
described in the Excel Help system: