Retirement Plan Vesting
Five years of service, counted properly — not by subtracting calendar years.
Task:
You work in HR administration. The retirement plan vests employees who have completed at least 5 years of service, measured as of 1 August 2026. From each hire date, work out the completed years of service in column C, then mark column D "Vested" or "Not vested".
Learning Objectives:
- Count complete years between two dates with DATEDIF
- See why a year only counts once its anniversary has passed
- Layer a threshold IF on top of a date calculation
Solve without hints for +5 XP
Interactive Spreadsheet
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.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Hire Date | Years of Service | Vested? |
| 2 | Priya Shah | 3/15/2019 | ||
| 3 | Owen Kessler | 11/2/2021 | ||
| 4 | Dana Okafor | 6/30/2016 | ||
| 5 | Mateo Ruiz | 1/10/2023 |
What this exercise teaches (contains the answer)
DATEDIF's "Y" unit only counts a year once the anniversary has actually arrived, which is why Owen shows 4 years rather than the 5 that 2026 minus 2021 would suggest — his service anniversary falls in November, three months after the 1 August cut-off. Keeping Vested? as its own IF, reading the completed-years column rather than repeating the date maths, means the five-year rule sits in a formula HR can find and edit without touching how the years are counted.