• Excel Book Excerpt

Excel Calculate Age in Years, Months, Days

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

Calculate Age in Years, Months, Days

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.

Figure 558 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.

Figure 559 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.

Figure 560 Less popular return codes are M, D, and YD.

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

Figure 561 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 how this happens.

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

For more resources for Microsoft Excel: