Dates and reporting
DATEDIF function: calculate completed years and remaining months
Measure service tenure in completed years ("Y") and leftover months ("YM") without double-counting total months ("M"), and catch reversed start/end dates (#NUM!).
Completed anniversaries vs partial months
Suppose a technician joined on 20 May 2023 (serial 45066 in A2) and is reviewed on 10 August 2026 (serial 46244 in B2). Three full years pass by 20 May 2026, plus two full months to 20 July 2026; 10 August is still 10 days short of a third leftover month.
| Unit code | Meaning | 2023-05-20 to 2026-08-10 |
|---|---|---|
| "Y" | Completed full years | 3 |
| "YM" | Completed months after full years | 2 |
| "M" | Total completed months overall | 38 |
Pair "Y" with "YM" rather than "M"
Use "Y" for completed years (3) and "YM" for the remaining completed months past the last anniversary (2). Using "M" returns 38—the entire tenure in months—which would overstate tenure if displayed alongside 3 years.
=DATEDIF(A2,B2,"Y")Check the anniversary day and reversed dates
Advance the review date in B2 to 25 August 2026 (serial 46259): because day 25 has now passed day 20, DATEDIF(A2, B2, "YM") steps from 2 up to 3.
DATEDIF requires start_date <= end_date; if start_date is later than end_date, it returns #NUM!.
=DATEDIF(A2,B2,"YM")Try the example
Use the original values or change them to test your understanding.