Calculate Age in Excel (2) - the age at the nearest birthday

Several functions in EXCEL can be used to determine the age such as, DATEDIF(), YEARFRAC().

Which one should be applied depends on the definition of the age.

The age is defined as age at nearest birthday.

·       Method 1,    DATEDIF(DATE1,DATE2,”m”) Combined with ROUND()

For example,

DOB (Date of Birth)                 5/20/1976

DOT (Date of Target)               11/20/2012

AGE at DOT                                                  37

In Cell B1, Type in 5/20/1976

In Cell B2, Type in 11/20/2012

In Cell B3, Formula= =ROUND(DATEDIF(B1,B2,"m")/12,0)

Thus, The Formula is ROUND(DATEDIF(DOB, DOT, ”m”)/12,0)

·       Method 2, YEARFRAC(DATE1,DATE2,0) Combined with INT()

In Cell B1, Type in 5/20/1976

In Cell B2, Type in 11/20/2012

In Cell B3, Formula= =INT(YEARFRAC(B1,B2,0)+0.5)

Thus, The Formula is INT(YEARFRAC(DOB, DOT, 0)+0.5)

© Copyright Exceltipsandkeys All Rights Reserved.