This note describes problems with calculating ages in spreadsheets and the useful but undocumented DATEDIF function.
Age calculations are surprisingly difficult to do in spreadsheets. Why can’t everyone have their birthdays on January 1st!
For a particular birthdate many of the more obvious formulas work everyday except on the actual birthday or the day after. Let’s take an example from Ages Calculations example spreasheet (XLS 38 KB) and use it here. Suppose cells B2 and B3 are named BirthDate and EnqDate.
What formula should be used in cell B17 so that the calculation works for every possible enquiry date?
After a bit of thought you might come up with the formula = YEAR(EnqDate-BirthDate)-1900. (Of course the simpler YEAR(EnqDate)-YEAR(BirthDate) will only be correct on or after a person’s birthday!). For this particular birthdate and many, many others this formula gives the wrong answer on anniversaries of the birthdate. For example, it calculated the age above as 16 rather than 17. Another formula, using an approach widely used in software, is =INT((EnqDate-BirthDate)/365.25). In this formula the INT function is used to truncate the answer to a whole number and the 365.25 is to accommodate leap years. Unfortunately, in this case, it is also gives 16.
After some more thought (in my case a couple of days!) you might come up with the horrendous formula:
=IF(MONTH(EnqDate)>MONTH(BirthDate),YEAR(EnqDate)-YEAR(BirthDate), IF(AND(MONTH(EnqDate)=MONTH(BirthDate),DAY(EnqDate)>= DAY(BirthDate)), YEAR(EnqDate)-YEAR(BirthDate),YEAR(EnqDate)-YEAR(BirthDate)-1))
This formula works but it looks so complicated. Sharon Long, a colleague, came up with a simpler slightly cheating form:
=IF(MONTH(EnqDate)*31+DAY(EnqDate)>=MONTH(BirthDate)*31+
DAY(BirthDate), YEAR(EnqDate)-YEAR(BirthDate),YEAR(EnqDate)-YEAR(BirthDate)-1)
There is a very much simpler formula that works in Excel. It uses the unofficial and no longer documented DATEDIF function. The formula is =DATEDIF(BirthDate,EnqDate,"y").
About the DATEDIF function
The syntax is =DATEDIF(Date1,Date2,Interval) Where:
Date1 is the first date and Date2 is the second date (both must be stored as date serial numbers)
Interval indicates the unit of time of the result and is one of the following codes:
| Code |
Description |
| "m" |
Number of complete months between Date1 and Date2 |
| "d" |
Number of days between Date1 and Date2 |
| "y" |
Number of complete years between Date1 and Date2 |
| "ym" |
Number of months between Date1 and Date2, as if Date1 and Date2 were in the same year |
| "yd" |
Number of days between Date1 and Date2, as if Date1 and Date2 were in the same year |
| "md" |
Number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year |
The formula below gives a more sophisticated age calculation:
=DATEDIF(BirthDate,TODAY(),"y") & " years, " & DATEDIF(BirthDate,TODAY(),"ym") & " months and " &
DATEDIF(BirthDate,TODAY(),"md") & " days"
On the day of writing this note (6/11/2006), for the example above, it produced the output
17 years, 7 months and 25 days
This formula was developed after consulting a very useful reference found at
www.cpearson.com/excel