How to Calculate Years of Service in Excel (4 Easy Ways)

Suppose you have an employee who recently resigned from your company. You want to calculate his/her years of service at your company. There are several ways to calculate that. We can use many formulas for this. The main objective of this article is to explain how to calculate years of service in Excel in different ways. The service duration with days, months, and years will also be calculated.


Download Practice Workbook


4 Easy Ways to Calculate Years of Service in Excel

As I said earlier there are several ways to calculate years of service in Excel. Here, I will explain 4 easy and efficient ways of doing it. I have taken the following dataset to explain this article. It contains the Employee Name, Joining Date, and End Date. I will explain how to calculate Years of Service for them.

Calculate Years of Service in Excel


1. Using INT & YEARFRAC Functions to Calculate Years of Service

In this method, I will explain how you can use the INT function and the YEARFRAC function to calculate years of service in Excel. Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate the Years of Service. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
=INT(YEARFRAC(C5,D5))

Using INT & YEARFRAC Functions to Calculate Years of Service

Formula Breakdown

  • YEARFRAC(C5,D5) —-> Here, the YEARFRAC function will return a fraction of the year represented by the number of days between the dates in cells C5 and D6.
    • Output: 7.94166666666667
  • INT(YEARFRAC(C5,D5)) —-> turns into
    • INT(7.94166666666667) —-> Here, the INT function will return the integer number by rounding it down.
      • Output: 7
  • Thirdly, press ENTER to get the result.

  • After that, drag the Fill Handle to copy the formula to other cells.

Dragging Fill Handle to Copy the Formula to Calculate Years of Service in Excel

Finally, you can see that I have copied the formula to all the other cells and calculated Years of Service in Excel for every employee.


2. Employing DAYS360 and DATE Functions to Calculate Years of Service

In this second method, I will use the DAYS360 function and the DATE function to calculate years of service in Excel. Let me show you how it is done.

Steps:

  • Firstly, select the cell where you want to calculate the Years of Service. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
=INT(DAYS360(DATE(YEAR(C5),MONTH(C5),DAY(C5)),DATE(YEAR(D5),MONTH(D5),DAY(D5)))/360)   

Employing DAYS360 and DATE Functions to Calculate Years of Service in Excel

Formula Breakdown

  • DAY(D5) —-> Here, the DAY function will return the day number of the date in cell D5.
    • Output: 24
  • MONTH(D5) —-> Here, the MONTH function will return the month number of the given date in cell D5.
    • Output: 1
  • YEAR(D5) —-> Here, the YEAR function will return the year number of the given date in cell D5.
    • Output: 2022
  • DATE(YEAR(D5),MONTH(D5),DAY(D5)) —-> turns into
    • DATE(2022,1,24) —-> Here, the DATE function will return a serial number that represents a date from a given year, month, and day.
      • Output: 44585
  • DATE(YEAR(C5),MONTH(C5),DAY(C5)) —-> turns into
    • DATE(2014,2,15) —-> Again, the DATE function will return a serial number that represents a date from a given year, month, and day.
      • Output: 41685
  • DAYS360(DATE(YEAR(C5),MONTH(C5),DAY(C5)),DATE(YEAR(D5),MONTH(D5),DAY(D5))) —-> turns into
    • DAYS360(41685,44585) —-> Here, the DAYS360 function will return the number of days between the two given dates.
      • Output: 2859
  • INT(DAYS360(DATE(YEAR(C5),MONTH(C5),DAY(C5)),DATE(YEAR(D5),MONTH(D5),DAY(D5)))/360) —-> turns into
    • INT(2859/360) —-> Here, Here, the INT function will return the integer number by rounding it down.
      • Output: 7  
  • Thirdly, press ENTER to get the result.

  • After that, drag the Fill Handle to copy the formula to other cells.

Finally, you can see that I have copied the formula to all the other cells and calculated Years of Service for every employee.


3. Use of DATEDIF Function to Calculate Years of Service in Excel

Now, if you want to calculate the service duration of a person in years, months and days you can use the DATEDIF function. In this example, I will calculate the years of service in three ways. The 1st one will give the output as years, the 2nd one will give the result as years and months and the 3rd one will give the full result as years, months, and days.


3.1. Using DATEDIF Function to Calculate Years

In this method, I will use the DATEDIF function to calculate the Year of Service in years. Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate the Years of Service. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
=DATEDIF(C5, D5, "y")& " Years"

Use of DATEDIF Function to Calculate Years of Service in Excel

Formula Breakdown

  • DATEDIF(C5, D5, “y”) —-> Here, the DATEDIF function will return the number of years between the two given dates.
    • Output: 7
  • DATEDIF(C5, D5, “y”)& ” Years” —-> turns into
    • 7& ” Years” —-> Now, the Ampersand (&) operator will combine the text and formula. 
      • Output: “7 Years”
  • Thirdly, press ENTER to get the result.

  • Now, drag the Fill Handle to copy the formula.

Finally, you can see that I have copied the formula to all the other cells and calculated the Years of Service in Excel for every employee in years.


3.2. Applying DATEDIF Function to Calculate Years and Months

Here, I will apply the DATEDIF function to calculate the Years of Services in years and months. Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate the Years of Service. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
=DATEDIF(C5,D5,"y")&" Years, "&DATEDIF(C5,D5,"ym")&" Months"

Applying DATEDIF Function to Calculate Years and Months

Formula Breakdown

  • DATEDIF(C5,D5,”y”) —-> Here, the DATEDIF function will return the number of years between the two given dates.
    • Output: 7
  • DATEDIF(C5,D5,”ym”) —-> Here, the DATEDIF function will return the number of months between the two given dates ignoring the days and years.
    • Output: 11
  • DATEDIF(C5,D5,”y”)&” Years, “&DATEDIF(C5,D5,”ym”)&” Months” —-> turns into
    • 7&” Years, “&11&” Months”  —-> Now, the Ampersand (&) operator will combine the texts and formulas. 
      • Output: “7 Years, 11 Months”
  • Thirdly, press ENTER to get the result.

  • After that, drag the Fill Handle to copy the formula.

Finally, you can see that I have copied the formula to all the other cells and calculated the Years of Service for every employee in years and months.


3.3. Use of DATEDIF Function to Calculate Years, Months, and Days

In this method, I will use the DATEDIF function to calculate the Year of Service in EXcel in years, months, and days. Let’s see how it is done.

Steps:

  • Firstly, select the cell where you want to calculate the Years of Service. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
=DATEDIF(C5,D5,"y") & " Years, " & DATEDIF(C5,D5,"ym") & " Months, " & DATEDIF(C5,D5,"md") & " Days"

Use of DATEDIF Function to Calculate Years, Months, and Days

Formula Breakdown

  • DATEDIF(C5,D5,”y”) —-> Here, the DATEDIF function will return the number of years between the two given dates.
    • Output: 7
  • DATEDIF(C5,D5,”ym”) —-> Here, the DATEDIF function will return the number of months between the two given dates ignoring the days and years.
    • Output: 11
  • DATEDIF(C5,D5,”md”) —-> Here, the DATEDIF function will return the number of days between the two given dates ignoring the months and years.
    • Output: 9
  • DATEDIF(C5,D5,”y”) & ” Years, ” & DATEDIF(C5,D5,”ym”) & ” Months, ” & DATEDIF(C5,D5,”md”) & ” Days” —-> turns into
    • 7 & ” Years, ” & 11 & ” Months, ” & 9 & ” Days” —-> Now, the Ampersand (&) operator will combine the texts and formulas. 
      • Output: “7 Years, 11 Months, 9 Days”
  • Thirdly, press ENTER to get the result.

Use of DATEDIF Function to Calculate Years, Months, and Days

  • After that, drag the Fill Handle to copy the formula.

Finally, you can see that I have copied the formula to all the other cells and calculated the Years of Service for every employee in years, months, and days.


4. Employing IF and DATEDIF Functions

If you have employees who have worked less than a year then this method will be helpful for you. Here, I will use the IF function and the DATEDIF function to calculate the Years of Service in Excel. I will explain 2 examples with 2 different types of output.


Example-01: Returning Text String If Service Duration Is Less Than One Year

In this example, I will return a text string if the Years of Service is less than a year. Let’s see how it is done.

Steps:

  • Firstly, select the cell where you want to calculate the Years of Service. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
=IF(DATEDIF(C5,D5,"y")=0,"Less than a year",DATEDIF(C5,D5,"y")&" Years, "&DATEDIF(C5,D5,"ym")&" Months")

Employing IF and DATEDIF Functions

Formula Breakdown

  • DATEDIF(C5,D5,”y”) —-> Here, the DATEDIF function will return the number of years between the two given dates.
    • Output: 7
  • DATEDIF(C5,D5,”ym”)  —-> Here, the DATEDIF function will return the number of months between the two given dates ignoring the days and years.
    • Output: 11
  • IF(DATEDIF(C5,D5,”y”)=0,”Less than a year”,DATEDIF(C5,D5,”y”)&” Years, “&DATEDIF(C5,D5,”ym”)&” Months”) —-> turns into
    • IF(7=0,”Less than a year”,7&” Years, “&11&” Months”) —-> Now, the IF function will check the logical_test. If it is True, the formula will return “Less than a year”. And if it is False the formula will return the Years of Service in years and months.
      • Output: “7 Years, 11 Months”
  • Thirdly, press ENTER to get the result.

Using IF and DATEDIF Functions to Calculate Years of Service in Excel

  • After that, drag the Fill Handle to copy the formula.

Finally, you can see that I have copied the formula and calculated the Years of Service for every employee.


Example-02: Calculating Month If Service Duration Is Less Than One Year

In this example, I will calculate the Years of Service in months if it is less than a year. I will use the IF function and the DATEDIF function to calculate the Years of Service in Excel. Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate the Years of Service. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
=IF(DATEDIF(C5,D5,"y")=0,DATEDIF(C5,D5,"ym")&" Months",DATEDIF(C5,D5,"y")&" Years, "&DATEDIF(C5,D5,"ym")&" Months")

Calculating Month If Service Duration Is Less Than One Year

Formula Breakdown

  • DATEDIF(C5,D5,”y”) —-> Here, the DATEDIF function will return the number of years between the two given dates.
    • Output: 7
  • DATEDIF(C5,D5,”ym”)  —-> This DATEDIF function will return the number of months between the two given dates ignoring the days and years.
    • Output: 11
  • IF(DATEDIF(C5,D5,”y”)=0,DATEDIF(C5,D5,”ym”)&” Months”,DATEDIF(C5,D5,”y”)&” Years, “&DATEDIF(C5,D5,”ym”)&” Months”) —-> turns into
    • IF(7=0,11&” Months”,7&” Years, “&11&” Months”) —-> Now, the IF function will check the logical_test. If it is True, the formula will return the Years of Service in months. And if it is False the formula will return the Years of Service in years and months.
      • Output: “7 Years, 11 Months”
  • Thirdly, press ENTER to get the result.

  • Next, drag the Fill Handle to copy the formula.

Finally, you can see that I have copied the formula and calculated the Years of Service for every employee.

Doing Logical Test to Calculate Years of Service in Excel


Calculate Years of Service in Excel from Hire Date

In this section, I will show you how you can calculate Years of Service from the Hire Date to the current date. I will also show you how you can calculate the End Date of a service period from the Hire Date.


1. Using TODAY Function to Calculate Years of Service from Hire Date

There is a built-in function in Excel that can give you the current date. This function is the TODAY function. It is written in Excel as, =TODAY (). This function is categorized as Date/Time function in Excel. It can be used in a formula also. Like, in the previous examples we worked with the End Date. Instead of the End Date, if you want to find out the Years of Service from the Hire Date to the Current Date you have to insert the TODAY function instead of the End Date.

Let me show you the steps.

Steps:

  • Firstly, select the cell where you want to calculate the Years of Service. Here, I selected cell D5.
  • Secondly, in cell D5 write the following formula.
=DATEDIF(C5,TODAY(),"y") & " Years, " & DATEDIF(C5,TODAY(),"ym") & " Months, " & DATEDIF(C5,TODAY(),"md") & " Days"

Calculate Years of Service in Excel from Hire Date

Formula Breakdown

  • DATEDIF(C5,TODAY(),”y”) —-> Here, the DATEDIF function will return the number of years between the Hire Date and the Current Date.
    • Output: 8
  • DATEDIF(C5,TODAY(),”ym”) —-> The DATEDIF function will return the number of months between the Hire Date and the Current Date ignoring the days and years.
    • Output: 6
  • DATEDIF(C5,TODAY(),”md”) —-> Here, the DATEDIF function will return the number of days between the Hire Date and the Current Date ignoring the months and years.
    • Output: 22
  • DATEDIF(C5,TODAY(),”y”) & ” Years, ” & DATEDIF(C5,TODAY(),”ym”) & ” Months, ” & DATEDIF(C5,TODAY(),”md”) & ” Days” —-> turns into
    • 8 & ” Years, ” & 6 & ” Months, ” & 22 & ” Days” —-> Now, the Ampersand (&) operator will combine the texts and formulas. 
      • Output: “8 Years, 6 Months, 22 Days”
Note: The advantage of using the TODAY function is that it gets updated every time you open your worksheet. So, every day you open your worksheet the length of service also gets updated.
  • Thirdly, press ENTER to get the result.

  • After that, drag the Fill Handle to copy the formula.

Finally, you can see that I have copied the formula and calculated the Years of Service from Hire Date in Excel for every employee.

Use of TODAY function to Calculate Years of Srvice in Excel

Read More: Calculate Number of Days Between Today & Another Date


2. Calculating End Date from Hire Date after Certain Years of Service

In this example, I will show you how you can calculate the End Date of a service period from the Hire Date and the Years of Service. Suppose, you have some employees and you want to evaluate their performance after a certain Years of Service from the Hire Date. So, for this performance evaluation, you will need the End Date of that service period. Here, I will use the EDATE function to calculate the End Date.

Let’s see how you can do it.

Steps:

  • Firstly, select the cell where you want to calculate the End Date. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
=EDATE(C5,D5*12) 

Calculating End Date from Hire Date after Certain Years of Service

Here, in the EDATE function, I selected C5 as start_date and D5*12 as months. I multiplied the years by 12 to convert them into months. Afterward, the formula will return the date after these selected months.

  • Thirdly, press ENTER, and you will get the End Date.

End Date of Years of Service in Excel

  • After that, drag the Fill Handle to copy the formula.

Finally, you can see that I have copied the formula and got the End Date for every employee.


Practice Section

Here, I have provided a practice sheet for you so that you can practice how to calculate years of service in Excel.

Practice Sheet to Calculate Years of Service in Excel


Conclusion

To conclude, I tried to cover how to calculate years of service in Excel. Basically, I calculated the number of years between two dates in Excel. I explained 4 different ways of doing it. The DATEDIF function makes it easier to calculate the length between two dates. Hope you did not face any difficulties while reading this article. Be connected with ExcelDemy to get more articles like this. Lastly, if you have any questions, let me know in the comment section.


Further Readings

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

9 Comments
  1. Very useful formula for HR Professionals.

  2. This was very helpful! Great information! Thank you very much!

  3. Thanks so much! This made things a whole lot easier for me. As Chamila said, very useful formula for HR folks.

  4. This formula has a bug. If you input joining date of 01/01/20, and a present date of 31/12/20, it returns 0years, 11 Months, 30 Days. However this is actually 1 year, 0 months and 0 days.

  5. I want to subscribe

  6. THANK YOU VERY MUCH for sharing!

Leave a reply

ExcelDemy
Logo