For Birthdates After 1900To 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.
For Birthdates Prior to 1900Although 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"
AGE = DateDiff(interval, birthdate, asofdate)