19 December 2016

Calculate age in years (and months) in Excel

It is often desirable to calculate a person's age from their date of birth relative to some other date (e.g., the date when they completed some test). This is pretty easy to do in Excel.

Calculating age in years
The simplest way to do this is using the following formula

=yearfrac(start_date,end_date,1)

where start_date is the cell containing the person's date of birth.

Using the above formula for someone with a D.O.B. of 20/3/1990 who was tested on 19/9/2016 will return the value 26.5017238, which may be reduced to fewer decimal points if so desired.

Calculating age in years and months
To calculate age in years and months, rather than using a decimal point, use the following formula

=concatenate(datedif(start_date,end_date,"Y"," years, ",datedif(start_date,end_date,"YM")," months")

Using this formula will return the value 26 years, 5 months. This formula can be modified to return other commonly used formats such as 26;5.


No comments: