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”)
 
Search JabSto ::




Custom Search