Calculate Age in Excel (1) - the age at the last birthday

Age is very important in some field such as retirement area. the full benefit age was 65

How can Excel help to determine the age?

Several functions can be used such as, DATEDIF(), YEARFRAC().

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

The age is defined as the age at the last birthday.

·       Method 1,    DATEDIF(DATE1,DATE2,”y”)

For example,

DOB (Date of Birth)                 5/20/1976

DOT (Date of Target)               5/1/2012

AGE at DOT                                                  35

In Cell B1, Type in 5/20/1976

In Cell B2, Type in 5/1/2012

In Cell B3, Formula= DATEDIF(B1,B2,"y")

Thus, The Formula is DATEDIF(DOB, DOT, ”y”)

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

In Cell B1, Type in 5/20/1976

In Cell B2, Type in 5/1/2012

In Cell B3, Formula= =INT(YEARFRAC(B1,B2,1))

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

1)    The age is defined as age at nearest birthday.

·       Method 1,    DATEDIF(DATE1,DATE2,”y”)

For example,

DOB (Date of Birth)                 5/20/1976

DOT (Date of Target)               5/1/2012

AGE at DOT                                                  36

In Cell B1, Type in 5/20/1976

In Cell B2, Type in 5/1/2012

In Cell B3, Formula= DATEDIF(B1,B2,"y")

Thus, The Formula is DATEDIF(DOB, DOT, ”y”)

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

In Cell B1, Type in 5/20/1976

In Cell B2, Type in 5/1/2012

In Cell B3, Formula= =INT(YEARFRAC(B1,B2,1))

Thus, The Formula is INT(YEARFRAC(DOB, DOT, 1))

© Copyright Exceltipsandkeys All Rights Reserved.