Calculate Age in Excel

There are many ways to calculate a person's age in Excel. However, most formulas are only valid for birthdates after 1/1/1900, because they rely on serial numbers.

For Birthdates After 1900

To calculate the age of a person on date since their birthdate:

=INT((date-birthdate)/365.25)

To figure their age as of today, date can be replaced with a function like TODAY() or NOW(). The INT() function is used to return only the number of complete years.

To calculate age in terms of years, months, and days, DATEDIF is a handy function. The following formula is similar to the one above.

=DATEDIF(birthdate,date,"y")

For Birthdates Prior to 1900

Although it is possible to create a really long formula to do the necessary text-to-date conversions necessary for dates prior to 1900, the simplest solution is to use a custom Excel function. The function AGE() below takes advantage of the Visual Basic DateDiff() function, which can handle all Gregorian dates. See the VBA help for details on DateDiff().
-->

Function AGE(birthdate As Variant, asofdate As Variant, _
             Optional interval As Variant) As Variant    If IsMissing(interval) Then        interval = "yyyy"
    End If    AGE = DateDiff(interval, birthdate, asofdate)End Function