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? Or do you like calculating ages in Excel — let it do the arithmetic for you!
Excel DATEDIF Function
Fortunately, calculating ages in Excel is easy, 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 and WARNING: 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 below.)
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!)
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)
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”)
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”)
Problems With DATEDIF
Thanks to Sam and Rick, who commented below. They pointed out that 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”.
________________
The Bugs in DatedIF across versions are well documented in the link below along with a UDF which overcomes the bugs.
http://addinbox.sakura.ne.jp/Excel_Tips05_E.htm#chk
From a previous newsgroup posting of mine…
You might want to reconsider using the DATEDIF function. It is an undocumented (and, thus, probably an unsupported) Excel function which appears to be broken in XL2007 at Service Pack 2. Someone recently posted this message as part of a newsgroup question…
**********************************************************************
=DATEDIF(DATE(2009,6,27),DATE(2012,1,5),”md”)
In 2007, this gives me 122. This happens all the way up to the point
where the second date is 1/26/2012 and then it hits zero at 1/27/2012.
In 2002, however, it gives me the correct answer of 9.
**********************************************************************
An informal survey of fellow MVPs shows the above formula works correctly in the initial release of XL2007 and its SP1, but does not work correctly in SP2; hence, it appears to be broken at that level. The problem is that the extent of the breakage is unknown (and probably indeterminable). In addition, I would say, being an undocumented (and, thus, probably and unsupported) function, the odds of Microsoft spending the time to search down and fix whatever broke is slim. This would seem to mean that DATEDIF cannot be counted on to work correctly from XL2007 SP2 onward. And even if Microsoft did fix the problem in a subsequent Service Pack, any of your users who remained at SP2 would be subjected to incorrect result.
Thanks Rick and Sam, I’ve updated the article to include the bug information.
You could also use my Random Data Generator to create fake birthdates.
Thank-you very much for the info on the datedif function. I created a regular formula to calculate age by subtracting birth date from Today(), but the results were inaccurate… not sure why. I replaced the formula with the one you have here, and everything is working perfectly, now.
Re the DATEDIF bug, I see that =DATEDIF(DATE(2009,6,27),DATE(2012,1,5),”md”) returns 9 in Excel 2013 so perhaps this is fixed.