Date & Time
Next work anniversary date and completed years of service
Calculate completed employee tenure, exact upcoming anniversary date (leap-day safe), and 5-year service award milestones.
Use one consistent anniversary policy
This example treats a Feb 29 hire anniversary as Feb 28 in non-leap years, returning to Feb 29 in leap years. It calculates each anniversary from the original hire date, so clipping does not accumulate.
The next anniversary is strictly after the reference date. Completed service years use the same clipped-anniversary policy, avoiding a mismatch between DATEDIF and EDATE on Feb 28.
| HireDate (A) | AsOfDate (B) | Completed Yrs | Upcoming Anniversary | 5-Yr Award? |
|---|---|---|---|---|
| 2021-08-15 | 2026-05-01 | 4 | 2026-08-15 (5th) | TRUE |
| 2021-08-15 | 2026-08-15 | 5 | 2027-08-15 (6th) | FALSE |
Upcoming anniversary date and milestone award check
Compare the anniversary in the reference year with INT(B2); if it has already occurred, advance one more year from the original hire date.
=LET(y, YEAR(B2)-YEAR(A2), anniversary, EDATE(A2, y*12), TEXT(EDATE(A2, (y+IF(anniversary<=INT(B2), 1, 0))*12), "yyyy-mm-dd"))Completed years of tenure and 5-year award flag
Use the same anniversary comparison when counting completed years and deciding whether the next anniversary is a multiple-of-five milestone.
=LET(y, YEAR(B2)-YEAR(A2), y-IF(EDATE(A2,y*12)>INT(B2),1,0))Inputs and interpretation
Require valid date-only hire/reference inputs with reference >= hire. This does not account for breaks in service or contractual/legal tenure definitions. TEXT returns a display string; omit it if a numeric date is needed.
Try the example
Use the original values or change them to test your understanding.