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.
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 is an employee list with joining dates in column B and end dates in column C. I want the 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. If you get a #SPILL! error in a newer version, clear the cells below the formula. Spilling formulas also cannot be used inside an Excel Table’s calculated column.
Important: DATEDIF returns #NUM! when the joining date comes after the end date. Dates stored as text can also cause a #VALUE! error. Correct the source dates instead of hiding the error.
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 is a staff list with each employee’s joining date in column B. 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. DATEDIF then counts the completed years from each joining date through today.
TODAY is a volatile formula, so Excel recalculates it when the workbook opens or another calculation occurs.
Pro Tip: TODAY keeps changing. Use a fixed as-of date instead when you need 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 TODAY 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 TODAY in every row.
The same DATEDIF and TODAY setup can calculate the total time elapsed from a past date or calculate age from a date of birth.
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 is an employee list with joining dates in column B and end dates in column C. I want a result 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 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.” This formula also spills down automatically in dynamic-array versions of Excel.
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 is an employee list with joining dates in column B and end dates in column C. I want the full service period 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. It then splits that number into years and months and calculates the remaining days from the last monthly anniversary.
This avoids DATEDIF’s “md” argument. Microsoft warns that “md” can return an inaccurate result in some date combinations.
LET is available in Microsoft 365, Excel 2024, and Excel 2021. This formula stays per row because EDATE does not return this calculation as a clean spilling range.
Calculate Decimal Years of Service Using YEARFRAC
Sometimes you need service expressed as a decimal for a prorated benefit or calculation.
Below is an employee list with joining dates in column B and end dates in column C. 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)
YEARFRAC 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.
ROUND limits the displayed result to two decimal places. Remove ROUND if a later calculation needs the full precision.
The INT function can remove the decimal portion, but DATEDIF 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 is a list with joining dates in column B and the milestone years in column C. I want the anniversary date in column D.
Enter this formula in D2 and fill it down:
=EDATE(B2,C2*12)
EDATE moves a date by a specified number of months. Multiplying the milestone years by 12 converts them to the number of months EDATE 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?