Excel: Calculate Age in Years, Months, Days

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I work in Human Resources. On our employee census, I need to calculate age in years and months.

Strategy: Use the super-secret DATEDIF function. Microsoft documented this function in Excel 2000 and never spoke of it since. Yet, it has been in Excel since the mid-nineties.

Use =DATEDIF(Earlier Date, Later Date, Return_Code).

The return codes are not entirely intuitive. They are shown here.

  1. The third argument of DATEDIF.

    From that list, the Y and YM codes would solve the question at the top of this topic. The following shows Years, Months, and Days.

  2. DATEDIF calculates years, months, and days.

    The less popular return codes are M for a complete count of full months, D for a complete count of days, and YD for the number of days in excess of full years.

  3. Less popular return codes are M, D, and YD.

    I've seen people get fancy with DATEDIF, using formulas such as these.

  4. Concatenating multiple DATEDIF functions.

    The formula in K is

    =DATEDIF(B4,C4,"Y")&" years, "œ&DATEDIF(B4,C4,"YM")&" Months, "œ&DATEDIF(B4,C4,"MD")&" days."

    The formula in L is

    =DATEDIF(B4,C4,"Y")&" years, "œ&DATEDIF(B4,C4,"YD")&" days."

    The formula in M is


    Gotcha: Here is the reason why Microsoft stopped documenting DATEDIF. When you calculate the DATEDIF between January 31 and March 1, you get 1 month and negative two days. It was probably easier to stop documenting DATEDIF than to explain how this happens.

  5. There are 29 days between 1/31 and 3/1. Not quite a month.