Calculate Age in Years, Months, Days

July 04, 2022 - by Bill Jelen

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.

DateDif codes: Y for Years, M for Months, D for Days, YM for months in excess of full years, MD for days in excess of full months, YD for days in excess of full years. — Figure 551. 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.

WIth name in A, date of birth in B, today in C, the DATEDIF formula can return something like 34 years, 2 months and 30 days as an age. — Figure 552. 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.

Other arguments M=Months, D=Days, and YD= Days beyond full years — Figure 553. Less popular return codes are M, D, and YD.

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

Three ways to use DateDif to show an age. "34 years, 2 months, 30 days" or "34 years, 91 days" or "34.2" which means 34 years and 2 months. — Figure 554. 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

=DATEDIF(B4,C4,”Y”)&”.”&DATEDIF(B4,C4,”YM”)

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 why this happens.

The reason Microsoft won't speak of DATEDIF. If the start date is January 31 and the end date in March 1, DATEDIF says 1 month and negative 2 days. — Figure 555. There are 29 days between 1/31 and 3/1. Not quite a month.

This article is an excerpt from Power Excel With MrExcel

Title photo by Joe Dudeck on Unsplash