Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate employee tenure in Excel and Google Sheets
Excel & Google Sheets
=DATEDIF(A2,IF(B2="",TODAY(),B2),"Y")&" yr "&DATEDIF(A2,IF(B2="",TODAY(),B2),"YM")&" mo"Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put the hire date in column A and the end date in column B (leave B blank for currently-employed staff).
- 2In C2 enter: =DATEDIF(A2,IF(B2="",TODAY(),B2),"Y")&" yr "&DATEDIF(A2,IF(B2="",TODAY(),B2),"YM")&" mo"
- 3The IF(B2="",TODAY(),B2) trick uses TODAY() for active employees and the actual end date for former ones — so the same formula works for both.
- 4For a decimal years-only result (better for prorated bonuses): =YEARFRAC(A2,IF(B2="",TODAY(),B2)).
- 5For just whole years: =DATEDIF(A2,IF(B2="",TODAY(),B2),"Y").
Tips
- Store tenure as a plain number for sorting and aggregation. =DATEDIF(A2,TODAY(),"D") returns total days; display the formatted string in a separate column.
- For company-anniversary calculations (was hired exactly N years ago today): =IF(DATEDIF(A2,TODAY(),"YM")=0,DATEDIF(A2,TODAY(),"Y")&" yr anniversary","").
- Watch the argument order: DATEDIF errors if the start date is LATER than the end date. Wrap in MIN/MAX to be safe: =DATEDIF(MIN(A2,B2),MAX(A2,B2),"Y").
- Average tenure across a team: =AVERAGE(C2:C100) when C holds the DATEDIF day count, formatted as years by dividing by 365.25.
- Bucket tenure for headcount reports: =IF(DATEDIF(A2,TODAY(),"Y")<1,"<1 yr",IF(DATEDIF(A2,TODAY(),"Y")<5,"1-5 yr","5+ yr")).
- DATEDIF is hidden in Excel (not in the function picker) but it works — it's kept for Lotus 1-2-3 compatibility. Just type it; the function name autocompletes.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I calculate tenure for employees who have left?
Enter their termination date in column B. The IF(B2="",TODAY(),B2) portion uses TODAY() when B is blank (active employee) and the actual date when B has a value (former employee). The DATEDIF result is then frozen at their tenure on departure.
Can I show only full years?
Yes — =DATEDIF(A2,TODAY(),"Y") returns only the count of completed years. Use "M" for total months, "D" for total days, "YM" for months in the current year, "MD" for days in the current month.
Why does DATEDIF return #NUM!?
The start date is later than the end date — DATEDIF rejects that combination. Either fix the data (check for typos or swapped columns) or wrap with MIN/MAX: =DATEDIF(MIN(A2,B2),MAX(A2,B2),"Y").
What's the difference between DATEDIF "Y" and YEARFRAC?
DATEDIF "Y" returns whole completed years (an integer). YEARFRAC returns a decimal — 4.75 means 4 years and 9 months. Use DATEDIF for display, YEARFRAC for prorated calculations like bonuses or vesting.
How do I count working years (excluding parental leave or sabbaticals)?
DATEDIF only knows the calendar dates you give it. To exclude leave periods, store each leave's start/end and subtract their cumulative duration: =DATEDIF(A2,TODAY(),"D")-SUM(LeavesTable[Days]). Format the result as a number, divide by 365.25 for years.
Can I get tenure broken down as years, months, AND days?
Yes — concatenate three DATEDIFs: =DATEDIF(A2,TODAY(),"Y")&"y "&DATEDIF(A2,TODAY(),"YM")&"m "&DATEDIF(A2,TODAY(),"MD")&"d". Returns e.g. "4y 7m 12d".