Excel guide · 4 min read
How to calculate years of service in Excel
=DATEDIF(B2,$E$2,"y"), where B2 is the hire date and E2 is the date you are measuring to. It returns complete years of service. For "15 years 7 months", join two DATEDIFs: =DATEDIF(B2,$E$2,"y")&" years "&DATEDIF(B2,$E$2,"ym")&" months".Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Hire date | Leave date | As of | |
| 2 | Rosa Delgado | Mar 14, 2011 | Oct 31, 2026 | ||
| 3 | Kwame Mensah | Jul 2, 2018 | |||
| 4 | Hannah Kraus | Nov 8, 2021 | |||
| 5 | Luis Ortega | Jan 25, 2016 | Jun 30, 2025 | ||
| 6 | Aiko Tanaka | Feb 29, 2024 | |||
| 7 | Ben Carter | Aug 17, 2026 | |||
| 8 | Fatima Rahman | Oct 31, 2009 | |||
| 9 | Josh Miller | Apr 6, 2020 | Sep 15, 2023 |
Download this sheet as an Excel practice file (.xlsx)
Complete years with DATEDIF
DATEDIF(start, end, unit) counts complete units between two dates. "y" means whole years, so a year only counts once the anniversary has passed. That is how most service awards, notice periods and benefit thresholds work.
Put the "as of" date in its own cell rather than using TODAY(). A report dated 31 October should still say the same thing when someone opens it in December. Use =TODAY() in E2 only for a live dashboard.
Result: 15
15: Rosa Delgado joined on 14 March 2011, so she completed her 15th year in March 2026.
Why YEAR minus YEAR gives the wrong answer
=YEAR($E$2)-YEAR(B4) gives 5 for Hannah Kraus, who started on 8 November 2021. On 31 October 2026 she is 8 days short of her fifth anniversary, so she has 4 complete years. Subtracting years ignores the month and day, and is wrong for everyone whose anniversary is still ahead in the year.
Dividing the day count by 365 or 365.25 is close but not exact either, and can tip someone over a threshold a day early or late. Use DATEDIF.
Result: 4
4, not 5. Her fifth anniversary is 8 November 2026.
Years and months: "15 years 7 months"
The "ym" unit returns the months left over after the complete years, from 0 to 11. Join it to the years with & for a readable label. Ben Carter, hired in August 2026, shows "0 years 2 months", which is accurate if not pretty; wrap the years part in IF if you want to hide a zero.
For decimal years in a sortable number, divide complete months by 12: =DATEDIF(B3,$E$2,"m")/12 gives 8.25 for Kwame Mensah (8 years 3 months). Microsoft also has YEARFRAC for fractional years, which counts days instead of months.
Result: 15 years 7 months
15 years 7 months: from 14 March 2011 to 31 October 2026.
Leavers: stop the clock at the leave date
For former staff, service ends on the leave date, not today. Use IF to choose the end date: the leave date if there is one, otherwise the as-of date. One formula then works for the whole list.
DATEDIF returns #NUM! when the start date is after the end date, so a hire date typed in the wrong column or a future start date shows up as an error instead of a silent wrong number.
Result: 9
9: Luis Ortega worked from 25 January 2016 to 30 June 2025. Without the IF he would show 10 years.
Next work anniversary
Add one more than the completed years to the hire date with EDATE, which moves a date by whole months. Format the cell as a date. EDATE handles month ends correctly: Aiko Tanaka was hired on 29 February 2024, and her anniversaries fall on 28 February in years without a 29th.
On the anniversary itself the formula already shows next year's date. Fatima Rahman joined on 31 October 2009, and on 31 October 2026 her next anniversary is 31 October 2027. To flag anniversaries that fall on the as-of date itself, test =EDATE(B2,12*DATEDIF(B2,$E$2,"y"))=$E$2, which is TRUE for Fatima. Subtract $E$2 from the result to get days to go.
Result: Nov 8, 2026
8 November 2026: Hannah Kraus's fifth anniversary, 8 days after the as-of date.
Count staff with 5 or more years
Anyone hired on or before the date five years before the as-of date has at least five complete years. EDATE($E$2,-60) gives that date, so COUNTIFS can count straight from the hire dates without a helper column. The second condition keeps only current staff, whose leave date is blank.
For a breakdown by band (under 1, 1-4, 5-9, 10+), add a helper column with the DATEDIF years and use COUNTIFS on it, or put it in a PivotTable.
Result: 3
3: Rosa Delgado, Kwame Mensah and Fatima Rahman. Luis Ortega and Josh Miller have left, and Hannah Kraus is 8 days short.
Common mistakes
Subtracting the years
YEAR(end)-YEAR(start) counts a year that has not been completed yet for anyone whose anniversary is later in the year. Use DATEDIF with "y".
Using TODAY() in a report you send or print
The figures change every day. Put the as-of date in a cell and refer to it with $ so the report stays consistent.
Relying on the "md" unit
Microsoft warns that DATEDIF with "md" can return wrong results. For days, subtract EDATE(start, complete months) from the end date instead.
Hire dates stored as text
DATEDIF needs real dates. If the hire dates are left-aligned or ISTEXT returns TRUE, convert them first.
FAQ
What is the formula for years of service in Excel?
=DATEDIF(hire_date, as_of_date, "y"), for example =DATEDIF(B2,$E$2,"y"). It returns complete years, the way service awards and thresholds are usually counted.
How do I calculate years and months of service?
Join two DATEDIFs: =DATEDIF(B2,$E$2,"y")&" years "&DATEDIF(B2,$E$2,"ym")&" months". "y" gives whole years and "ym" the months left over.
Why can't I find DATEDIF in Excel?
DATEDIF is an older function that Excel still supports but does not list in Formula AutoComplete or the Insert Function dialog. Type it in full and it works in every current version.
How do I calculate tenure for employees who have left?
Use the leave date as the end date when there is one: =DATEDIF(B2,IF(C2="",$E$2,C2),"y"). Current staff are measured to the as-of date and leavers stop at their last day.
Functions used
DATEDIFWhole years, months or days betweenEDATESame day, months laterIFPicks a result from a testCOUNTIFSCounts with several conditionsYEARYear of a dateTODAYToday’s date
Related guides
- How to calculate age in Excel from date of birth
- How to calculate months between two dates in Excel
- How to add months to a date in Excel
- How to calculate days between two dates in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- Countdown in Excel: how many days until a date
Updated