Calculating Ages in Excel
Can you remember how old you are? Or are you like me, and have to ask, “What year is it?” and then subtract your birth year?
Fortunately, Excel can help you with that calculation, if you use the handy, but poorly documented, DATEDIF function. You won’t find DATEDIF in Excel’s help, and the Microsoft website suggests a different method of calculating date differences. If anyone knows why, I’d love to hear about it.
Update: There is a bug in the DATEDIF function for some intervals. For details, see the article ktDATEDIF Function, which suggests a user defined function as an alternative. (Thanks to Sam and Rick for their comments.)
Create a List of Dates
It was my son’s birthday last weekend, and he was complaining about getting old. Geez, if he’s old, what does that make me? (Please don’t answer!) Yes, that’s him in the picture, wearing his Junior Whale Trainer shirt. And he still likes cookies, but those cheeks have disappeared.
To keep track of family birthdays and ages, you can create a list in Excel. In one column, enter everyone’s birth date. Here’s an example, with birth dates created using the RANDBETWEEN function.
Using the DATEDIF Function
To calculate each person’s current age, you can use the DATEDIF function, to compare their birth date with today’s date. The DATEDIF has 3 arguments:
=DATEDIF(Date1, Date2, Interval)
Date1 must be earlier than Date2, or the result will be a #NUM! error.
Interval is the unit of time that you want to show the results in, such as:
- y for years
- m for months
- yd for calendar days between dates, ignoring the years
For example, to find the current age, in years, for the birth date in cell B2, enter this formula in cell C2:
Change the Interval
To see a person’s age in months, you can use “m” as the interval, instead of “y”.
Problems With DATEDIF
Excel 2007 and Excel 2003 may give incorrect results in some situations, especially for the “yd” and “md” intervals. You should use the more reliable user defined ktDATEDIF Function to calculate these date differences, or use Microsoft’s suggested formula for calculating date differences.
More DATEDIF Information
Chip Pearson has more information on the DATEDIF function, which he describes as “treated as the drunk cousin of the Formula family.” He shows several examples of using DATEDIF, and explains how to use it with leap years.
Microsoft has DATEDIF details for SharePoint, which looks the same as the missing DATEDIF info for Excel.
And finally, don’t confuse the Excel worksheet function, DATEDIF, with the VBA function DATEDIFF, or the Access DATEDIFF function. Although the functions have similar results, the DATEDIFF functions have different arguments, and use different interval settings. For example, “yyyy” is the setting for year, instead of “y”.