Best on desktop
The spreadsheet workspace needs more room than this screen offers. Switch to a laptop for the full experience.
An HR analyst's tenure report is under-counting by one year for three employees on the roster — but only those three. The pattern is subtle until you look at hire dates: the affected rows are all Feb 29 hires (2016-02-29, 2020-02-29, 2024-02-29). Their original formula in column C:
=DATEDIF(B2, DATE(2027, 2, 28), "Y")
The bug: DATEDIF treats Feb 29 anniversaries as occurring on Mar 1 in non-leap years. The "as-of" target date 2027-02-28 is one day before what DATEDIF considers the anniversary, so the most recent year doesn't count. A leap-day hire from 2016-02-29 shows 10 years completed instead of the 11 years the employee (and HR policy) would expect.
The fix: detect Feb 29 hires and shift them back one day to Feb 28 for the DATEDIF call, so anniversaries always fall on Feb 28 — which exists in every year:
=DATEDIF(IF(AND(MONTH(B2) = 2, DAY(B2) = 29), B2 - 1, B2), DATE(2027, 2, 28), "Y")
The IF wraps the hire-date input: for Feb 29 hires it shifts one day back; for everyone else it passes through unchanged. The downstream DATEDIF then sees a Feb 28 anniversary it can match against the target date, and the leap-day employees get credit for the year they've actually completed.
Your task:
Graded cells: C3 (2016-02-29 → 11), C6 (2024-02-29 → 3), C9 (2020-02-29 → 7) — all three leap-day rows.