Date FunctionsIntermediate

Build an employee tenure badge from years and leftover months with DATEDIF

DATEDIF's "y" unit counts only the years that have fully turned over between two dates, and its "ym" unit answers a different question on the same two dates — how many months are left once those years are set aside — so the two together cover a tenure figure that neither one alone would get right.

Task:

You maintain HR records for a mid-size consulting firm, and performance review packets are due to show each employee's tenure as a readable badge rather than a raw hire date. As of today's review date, October 18, 2026, use DATEDIF in column C to find each employee's completed years of service, and DATEDIF again in column D for the whole months left over beyond that last anniversary. Then in column E, join the two into a label like "7 years, 9 months" with &. Fill in rows 2 through 6.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

6 rows × 5 columns15 cells you fill in
ABCDE
1EmployeeHire DateYearsMonthsTenure
2Marcus Webb1/15/2019
3Priya Shah6/5/2021
4DeShawn Lee11/20/2015
5Elena Cho10/18/2020
6Omar Haddad2/29/2024
What this exercise teachesMay contain the answer

DATEDIF(B2,"10/18/2026","y") only counts an anniversary once it has fully turned over, which is why Marcus Webb, hired January 15, 2019, shows a clean 7 rather than anything fractional — his anniversary falls months before the October 18 review date, so every one of those seven years is already complete. DATEDIF(B2,"10/18/2026","ym") throws the years away and answers a narrower question on the same two dates: how many whole months sit between his most recent anniversary and the review date, landing on 9. DeShawn Lee is the case where getting this wrong would actually show: hired November 20, his 2026 anniversary hasn't happened yet by October 18, so "y" correctly holds him at 10 rather than rounding up to 11, while "ym" measures from his most recent anniversary — November 20, 2025 — forward to the review date, landing on 10 rather than wrapping around to some small number. Elena Cho's row checks the other edge: hired exactly six years earlier to the day, her "ym" comes back 0, which is the sign that "y" has already claimed every month there is. Joining C and D with & afterwards does no date arithmetic of its own — it only reads whatever those two DATEDIF calls already settled.