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".
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve 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.
| 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 teachesMay contain 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.