Age Calculation in Excel

AGE CALCULATION IN EXCEL

You can calculate a persons age based on their birthday and today's date.

The calculation uses the DATEDIF() function.

The DATEDIF() is not documented in Excel 5, 7 or 97, but it is in 2000.

What Does It Do?

This function calculates the difference between two dates.
It can show the result in weeks, months or years.

Syntax

 =DATEDIF(FirstDate,SecondDate,"Interval")

FirstDate : This is the earliest of the two dates.
SecondDate : This is the most recent of the two dates.
"Interval" : This indicates what you want to calculate.
These are the available intervals.

                "d"    :  Days between the two dates.
                "m"   :  Months between the two dates.
                "y"    :  Years between the two dates.
                "yd"  :  Days between the dates, as if the dates were in the same year.
                "ym" :  Months between the dates, as if the dates were in the same year.
                "md" :  Days between the two dates, as if the dates were in the same month and year.  

Example

Birth Date  :   1 Apr 1991

Age as on 5 June 2014 is 

           Years     :   23    =DATEDIF("1-Apr-1991","5-June-2014","y")

            Months :    2     =DATEDIF("1-Apr-1991","5-June-2014","ym")

            Days     :    4     =DATEDIF("1-Apr-1991","5-June-2014","md")

You can put this all together in one calculation, which creates a text version.

Age is 23 Years, 2 Months and 4 Days

="Age is " & DATEDIF("01-Apr-1991","5-Jun-2014","y") & " Years " & DATEDIF("01-Apr-1991","5-Jun-2014","ym") & " Months " & DATEDIF("01-Apr-1991","5-Jun-2014","md") & " Days"