30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / How to Calculate Years of Service in Excel
By Puneet Gogia (Microsoft MVP)

How to Calculate Years of Service in Excel

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

To calculate years of service, use =DATEDIF(A2,B2,"y") where A2 is the joining date and B2 is the leaving date. The “y” returns completed years only, so someone who joined on 10-Jan-2020 and left on 5-Jan-2026 shows 5, not 6.

For current employees, swap the end date for TODAY: =DATEDIF(A2,TODAY(),"y"). To show the full tenure in years, months and days, chain three DATEDIFs: =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months, "&DATEDIF(A2,B2,"md")&" days".

If you need a decimal instead — 17.35 years, useful for pro-rata leave or gratuity — use =YEARFRAC(A2,TODAY()). And to handle both leavers and current staff in one column, wrap the end date in IF: =DATEDIF(A2,IF(C2="Working",TODAY(),B2),"y").

Two things to watch: DATEDIF doesn’t appear in Excel’s function list as you type, so you have to know the name, and it returns #NUM! if the start date is later than the end date.

Let’s say your company grants additional paid vacation days based on the length of service of its employees. Now you are the HR manager and need to calculate each employee’s exact years of service to determine their vacation benefits accurately.

What is Years of Service?

Years of service refers to the total time someone has worked for a company, from their first job day until now. Employers use it to track employees’ tenure, often to determine benefits like vacation time or bonuses. This number helps companies recognize and reward their employees’ loyalty and experience.

In this tutorial, you will learn to write a formula to calculate years of service in Excel. And for this, you are going to use the DATEDIF function. If you have never heard of this function, you can learn about it here. Apart from this, we will also learn a lot of other formulas as well. So, let’s get started…

Excel Formula to Calculate Years of Service

DATEDIF calculates the difference between two dates; you can specify whether you want the result in days, months, or years. It helps calculate how much time has passed between two specific dates, like an employee’s start date and today or an end date.

In the following example, we have a start date and an end date.

year-of-service

And now let’s use the following steps:

  1. Type “=DATEDIF(” in cell C2.
  2. Refer to the start date, which is A2, and enter a comma.
  3. Refer to the end date, which is B2, and enter a comma.
  4. Enter “y”, enter the closing parenthesis, and hit Enter to get the result.
get-the-year-of-service

Let me explain: When you use “y” in the DATEDIF function, it returns the complete years that you have between the start date and the end date.

=DATEDIF(A2,B2,"y")

Now let’s say you want to get a complete period of service between two dates, which includes the years, months, and days. You can use the following formula.

=DATEDIF(A2,B2,"y")&" year(s), "&DATEDIF(A2,B2,"ym")&" month(s), "&DATEDIF(A2,B2,"md")&" day(s)."
get-years-months-date-of-service

Here, “ym” returns the months left after the complete years, and “md” returns the days left after the complete months. For 31-Mar-2015 to 29-Feb-2024, it returns 8 year(s), 10 month(s), 29 day(s). Microsoft notes that the “md” unit can give a wrong result in some cases (around month ends), so double-check the days part when it matters.

And if you only need the years and months, use the following formula:

=DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months"

Get Years of Service from Today’s Date

Now let’s say you want to calculate years of service by using today’s date. Following is the formula that you need to use:

=DATEDIF(A2,TODAY(),"y")&" year(s)"
get-year-of-service-from-today

This formula finds out how many full years have passed since the date in cell A2 (01-Jan-2007) up to today’s date and adds the text ” year(s)” to show the result as a number followed by “year(s)”.

Use YEARFRAC for Years of Service Formula

The YEARFRAC function returns the fraction of the year represented by the number of whole days between two dates. It can be helpful for precise service years calculation, including partial years.

=YEARFRAC(A2, TODAY())

In cell A2, we have 01-Jan-2007, and today is 07-May-2024. In the result, the formula has returned 17.35, which means 17.35 years is the years of service.

If you want whole years only, wrap YEARFRAC in INT, which drops the decimal part. The 1 in the third argument (basis) makes YEARFRAC use the actual number of days in each year, which is more accurate for long periods:

=INT(YEARFRAC(A2,TODAY(),1))

Or to keep the decimal but show only two places, use ROUND:

=ROUND(YEARFRAC(A2,TODAY(),1),2)

Calculate Years of Service with a Condition

Let’s think like this: you have a list of employees where some have left the company, and the rest are still working. You need to write a formula to check if the employee is still working and use the current date. If the employee has already left, use the last day of their work to calculate years of service.

=YEARFRAC(B1,B2)+YEARFRAC(B3,IF(B5="Working",TODAY(),B4))
  • YEARFRAC(B1,B2) – This part of the formula calculates the years of service from the First Join Date to the First Leaving Date.
  • YEARFRAC(B3,IF(B5=”Working”,TODAY(),B4)) – In this part, the formula checks if the employee is still working or not. If the status in B5 is “Working”, the formula uses TODAY to get the current date. But if that person is not working anymore and the status is anything other than “Working”, it takes the date from cell B4, which is the second leaving date.

In the end, it returns the total years of service in the result. This formula helps you see exactly how long someone has been with the company, counting only the time they were actively employed.

Years of Service for Employees with Multiple Tenure

There might be a situation where you want to calculate the total years of service for employees who have had multiple tenures with a company. For this, you can use a custom function with VBA.

Function TotalYearsOfService(FirstJoinDate As Date, FirstTermDate As Date, SecondJoinDate As Date, CurrentOrSecondTermDate As Date) As Double
    Dim FirstPeriod As Double
    Dim SecondPeriod As Double

    ' Calculate the duration of the first period of service
    FirstPeriod = (FirstTermDate - FirstJoinDate)

    ' Calculate the duration of the second period of service
    SecondPeriod = (CurrentOrSecondTermDate - SecondJoinDate)

    ' Convert days to years and sum both periods
    TotalYearsOfService = (FirstPeriod + SecondPeriod) / 365.25
End Function

This function calculates the total years of service based on the four dates you specify, and it makes sure that the period when the employee wasn’t employed is not included. It needs all four dates, so for an employee who is still working, use TODAY() as the last argument, like =TotalYearsOfService(B1,B2,B3,TODAY()). Add the code to a standard module (Insert ➜ Module) so you can use it in a cell.

Points to Consider

  • Decide how you want to handle partial years. YEARFRAC calculates the exact fractional year between two dates, which is useful for precise calculations.
  • If an employee has multiple periods of employment with gaps in between, ensure each period is calculated separately and then summed up.
  • All dates need to be entered in the correct format in Excel to ensure that Excel correctly uses them as dates rather than text.

Frequently Asked Questions

Why does DATEDIF return the #NUM! error?

The start date is after the end date. DATEDIF needs the earlier date first, so check that the joining date is in the first argument. Also note that DATEDIF isn’t listed in Excel’s function list, so you need to type it in full.

Should I use DATEDIF or YEARFRAC?

Use DATEDIF when you need complete years (like 16), for example, to decide a benefit level. Use YEARFRAC when you need the years with a decimal (like 16.35), for example, to calculate a pro-rated amount.

How do I get the months of service?

Use “m” as the unit in DATEDIF. It returns the complete months between the two dates, for example, 106 months for 31-Mar-2015 to 29-Feb-2024:

=DATEDIF(A2,B2,"m")
Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment