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.
- 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.
- 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.
- Less popular return codes are M, D, and YD.
I've seen people get fancy with DATEDIF, using formulas such as these.
- 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.
- There are 29 days between 1/31 and 3/1. Not quite a month.