Dates & Time

Excel formula for Years of service (tenure)

Show how long someone has been employed, in years and months.

Copy this formula

=DATEDIF(A2, TODAY(), "y") & "y " & DATEDIF(A2, TODAY(), "ym") & "m"

How the formula works

DATEDIF with "y" gives complete years and "ym" gives the leftover months. Concatenating them produces a tenure like "5y 4m". Replace TODAY() with an end date for past employees.

Examples

=DATEDIF(HireDate, TODAY(), "y")→5

Completed years of service.

=DATEDIF(DATE(2019,4,1), DATE(2026,10,7), "y")→7

Seven completed years between 1 April 2019 and 7 October 2026.

=YEARFRAC(DATE(2019,4,1), DATE(2026,10,7))→7.52

Tenure as a decimal number of years.

FAQ

How do I include days too?

Append DATEDIF(A2, TODAY(), "md") & "d" for the leftover days.

How do I calculate tenure in months only?

Use =DATEDIF(A2, TODAY(), "m") for the total number of completed months.

How do I calculate tenure for someone who has left?

Replace TODAY() with the leaving date: =DATEDIF(A2, B2, "y").

How do I find the next work anniversary?

Use =EDATE(A2, 12*(DATEDIF(A2, TODAY(), "y")+1)). It adds one more year than the completed years of service.

Related formulas

Calculate age from a birth date Days between two dates

Functions used

DATEDIF TODAY

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free