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

________________

You may also like...

6 Responses

  1. Rick Rothstein (MVP - Excel) says:

    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.

  2. sam says:

    The Bugs in DatedIF across versions are well documented in the link below along with a UDF which overcomes the bugs.

    http://www.h3.dion.ne.jp/~sakatsu/Excel_Tips05_E.htm#chk

  3. Thanks Rick and Sam, I’ve updated the article to include the bug information.

  4. JP says:

    You could also use my Random Data Generator to create fake birthdates.

  5. Wade says:

    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.

  6. Jeff Weir says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *