Calculating Ages in Excel

J01 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: In some versions of Excel, 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.
=RANDBETWEEN($F$2,$G$2)
AgeCalc01

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:
=DATEDIF(B2,TODAY(),”y”)
AgeCalc02

Change the Interval

To see a person’s age in months, you can use “m” as the interval, instead of “y”.
=DATEDIF(B3,TODAY(),”m”)
AgeCalc03

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.

DATEDIFF Function

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”.
________________