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.
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.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Hire Date | Years | Months | Tenure |
| 2 | Marcus Webb | 1/15/2019 | |||
| 3 | Priya Shah | 6/5/2021 | |||
| 4 | DeShawn Lee | 11/20/2015 | |||
| 5 | Elena Cho | 10/18/2020 | |||
| 6 | Omar Haddad | 2/29/2024 |
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.