Calculate years and months of tenure between two dates
Turn a start date and today's date into a readable "3 years, 7 months" tenure figure with one DATEDIF formula, no manual date math.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →The "y" unit returns the number of complete years between the two dates, ignoring any leftover months — the whole-years part of the answer.
- →The "ym" unit returns the leftover months AFTER those complete years are subtracted out, so the two DATEDIF calls together add up to the exact tenure instead of double-counting a partial year.
- →Concatenating the two results with & and literal text turns a pair of numbers into one readable sentence, which a raw date subtraction never gives on its own.
The fine print
- →DATEDIF has no entry in Excel's function autocomplete or Insert Function dialog — Microsoft never documented it in the UI — but it has been stable across Excel versions for decades and behaves the same in Google Sheets.
- →Avoid the "md" unit (leftover days): it has a long-standing, Microsoft-acknowledged bug that returns wrong results across certain month boundaries. "y", "ym" and "yd" are the reliable units.
- →If end_date falls before start_date, DATEDIF returns #NUM! instead of a negative tenure.
Part by part
- =DATEDIF(
- An undocumented function kept for Lotus 1-2-3 compatibility. It works in every version of Excel and Sheets, and Excel's formula autocomplete will not suggest it.
- "y"
- Complete years between the dates. It counts whole years only, so it does not round up as a division by 365 would.
- "ym"
- Months remaining after those whole years — the crucial unit here. Plain "m" returns total months, which would double-count everything the years already covered.
- & " years, " &
- String concatenation joining the two numbers into a readable phrase. The spaces inside the quotes are what stop the words running together.
Common mistakes
=DATEDIF(B2, C2, "y") & " years, " & DATEDIF(B2, C2, "m") & " months"Plain "m" gives total months since the start, so five years and three months reports as "5 years, 63 months". The ym unit is what returns the remainder rather than the total.
=DATEDIF(B2, C2, "y") & " years, " & DATEDIF(B2, C2, "ym") & " months"=(C2-B2)/365Ignores leap years, so the figure drifts by roughly a day every four years and never lands on a clean anniversary. It also gives a decimal that has to be interpreted rather than a count.
=DATEDIF(C2, B2, "y")Arguments reversed. DATEDIF requires the earlier date first and returns #NUM! otherwise, which at least fails loudly — unlike most date arithmetic, which silently goes negative.
Questions
Why is DATEDIF not in the function list?
Microsoft kept it for compatibility with Lotus 1-2-3 and never documented it, so autocomplete does not offer it and the help does not describe it. It works reliably regardless, and Sheets supports it too.
What are the other unit codes?
"d" for total days, "m" for total months, "md" for days ignoring months and years, and "yd" for days ignoring years. The two-letter codes are the remainder forms and the single letters are totals.
How do I show tenure to today automatically?
Use TODAY() as the end date. Be aware it recalculates whenever the sheet opens, so a saved copy shows a different figure later — freeze the value if the number needs to be stable.
Does it handle the end of the month correctly?
Mostly, and the md unit has known oddities that can return a negative number in specific month-end cases. For anything contractual, prefer y and ym as this formula does, and avoid md.