If you have an employee’s joining date, you can calculate their years of service in Excel with a short formula.
Depending on the report, you may need completed years, decimal years, or a result that includes months and days. In this article, I’ll show you the formula for each result, including one that updates automatically every day.
Follow along with the example file
Calculate Years of Service Excel.xlsx
Choose the Right Years-of-Service Formula
Use the table below to match the result you need with the best Excel approach.
| Result You Need | Recommended Approach |
|---|---|
| Completed years between two dates | DATEDIF with “y” |
| Years of service through the current date | DATEDIF with TODAY |
| Years and remaining months | DATEDIF with “y” and “ym” |
| Years, months, and days | LET with EDATE |
| Decimal years | YEARFRAC |
| Future service anniversary date | EDATE |
Calculate Completed Years of Service Using DATEDIF
Use this method when you have both a joining date and an end date, and you only need the number of completed years.
Below I have employee joining and end dates, and I want their completed years of service in column D.
Enter this formula in cell D2:
=DATEDIF(B2:B6,C2:C6,"y")
The DATEDIF formula measures the gap between the two dates. The “y” argument tells Excel to return only completed years.
In Microsoft 365 and newer versions of Excel that support dynamic arrays, this single formula spills all five results into column D.
Pro Tip: If your Excel version does not spill the results, use =DATEDIF(B2,C2,"y") in D2 and fill it down.
In a newer version, a #SPILL! error can appear if cells below the formula are in the way. Clear those cells so the results can spill.
Spilling formulas cannot be used in an Excel Table’s calculated column, so if your data is in a Table, use the per-row formula above instead.
Important: The DATEDIF function returns #NUM! when the joining date comes after the end date.
Dates stored as text can also cause a #VALUE! error. If you get either error, correct the source dates instead of hiding it.
Calculate Years of Service in Excel Using TODAY
This method is useful for current employees because the result updates on its own as time passes.
Below I have a staff list with joining dates, and I want their completed years of service through today in column C.
Enter this formula in cell C2:
=DATEDIF(B2:B6,TODAY(),"y")
The TODAY() formula supplies the current date. The DATEDIF function then counts the completed years from each joining date through today.
The TODAY function is a volatile formula, so Excel recalculates it when the workbook opens or another calculation occurs.
Pro Tip: The TODAY function keeps updating the result, so use a fixed as-of date for a historical report that should not change later.
If your list contains current and former employees, add an End Date column. Leave it blank for anyone who still works at the company.
Use this formula to take the end date from column C when one exists, or use the TODAY function when the cell is blank:
=IF(B2:B6="","",DATEDIF(B2:B6,IF(C2:C6="",TODAY(),C2:C6),"y"))
This gives you one formula for a mixed employee list without placing the TODAY function in every row.
You can also use the DATEDIF and TODAY functions to calculate the total time elapsed from a past date or calculate age from a date of birth.
Follow along with the example file
Calculate Years of Service Excel.xlsx
Calculate Service in Years and Months
Completed years are often enough for a report, but an employee may want to see the remaining months as well.
Below I have employee joining and end dates, and I want their service in years and months, such as “6 Years 9 Months”, in column D.
Enter this formula in cell D2:
=DATEDIF(B2:B6,C2:C6,"y")&" Years "&DATEDIF(B2:B6,C2:C6,"ym")&" Months"
The first DATEDIF function returns the completed years. The second uses “ym” to return the months left after those complete years are removed.
The ampersand joins both numbers with the words “Years” and “Months.”
In versions of Excel that support dynamic arrays, this formula spills the results down automatically, so you only need to enter it once.
Pro Tip: To calculate service through today, replace both references to C2:C6 with TODAY().
Calculate Service in Years, Months, and Days
If you need a more detailed service period, you can show the remaining days after the completed years and months.
Below I have employee joining and end dates, and I want the full service period in years, months, and days in column D.
Enter this formula in D2 and fill it down:
=LET(start,B2,finish,C2,n,12*(YEAR(finish)-YEAR(start))+MONTH(finish)-MONTH(start),wholeMonths,n-(EDATE(start,n)>finish),QUOTIENT(wholeMonths,12)&" Years "&MOD(wholeMonths,12)&" Months "&finish-EDATE(start,wholeMonths)&" Days")
The formula first finds the number of completed calendar months and splits that number into years and months.
It then calculates the remaining days from the last monthly anniversary to the end date.
This avoids the DATEDIF function’s “md” argument. Microsoft warns that “md” can return an inaccurate result in some date combinations.
The LET function is available in Microsoft 365, Excel 2024, and Excel 2021.
Enter this formula separately for each row because the EDATE function does not return this calculation as a spilling range.
Calculate Decimal Years of Service Using YEARFRAC
Sometimes you need service expressed as a decimal for a prorated benefit or calculation.
Below I have employee joining and end dates, and I want each service period rounded to two decimal places in column D.
Enter this formula in D2 and fill it down:
=ROUND(YEARFRAC(B2,C2,1),2)
The YEARFRAC function returns the fraction of a year between the start and end dates. The third argument, 1, tells Excel to use the actual number of days in the relevant years.
The ROUND function limits the displayed result to two decimal places. If a later calculation needs the full precision, remove the ROUND function.
The INT function can remove the decimal portion, but the DATEDIF function is clearer when you specifically need completed years.
Calculate a Future Service Anniversary Date Using EDATE
You may also need the date when an employee completes a service milestone, such as 5 or 10 years.
Below I have employee joining dates and milestone years, and I want the anniversary date for each milestone in column D.
Enter this formula in D2 and fill it down:
=EDATE(B2,C2*12)
The EDATE function moves a date by a specified number of months. Multiplying the milestone years by 12 converts them to the number of months the function needs.
If Excel displays a serial number instead of a date, apply a date format to the result cells.
Important: Check how your organization treats employees hired on February 29 before using a calculated anniversary date. The recognized anniversary can differ in a non-leap year.
I covered the formulas for completed years, decimal years, detailed service periods, and future service anniversaries.
Use a fixed end date for historical reports and TODAY when the result should keep updating. I hope you found this article helpful.
Other Excel Articles You May Also Like:
- How to Get the Number of Days in a Month in Excel?
- Calculate Fiscal Year from Date in Excel
- Get Day Name from Date in Excel (Easy Formulas)
- How to SUM values between two dates (using SUMIFS formula)
- Calculate the Number of Months Between Two Dates in Excel
- How to Calculate the Number of Days Between Two Dates in Excel
- How to Add Months to Date in Excel (Easy Formula)
- Check IF a Date is Between Two Given Dates in Excel (Easy Formula)
In the examples you have, there are examples with joining dates. But I have birth date, retirement date. In this situation, how can the left of service be taken out?