Siam Hasan Khan
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.
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))
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
- INT(7.94166666666667) —-> Here, the INT function will return the integer number by rounding it down.
- 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 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) 
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(2022,1,24) —-> Here, the DATE function will return a serial number that represents a date from a given year, month, and day.
- 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
- DATE(2014,2,15) —-> Again, the DATE function will return a serial number that represents a date from a given year, month, and day.
- 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
- DAYS360(41685,44585) —-> Here, the DAYS360 function will return the number of days between the two given dates.
- 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
- INT(2859/360) —-> Here, Here, the INT function will return the integer number by rounding it down.
- 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"
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”
- 7& ” Years” —-> Now, the Ampersand (&) operator will combine the text and formula.
- 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"
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”
- 7&” Years, “&11&” Months” —-> Now, the Ampersand (&) operator will combine the texts and formulas.
- 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"
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”
- 7 & ” Years, ” & 11 & ” Months, ” & 9 & ” Days” —-> Now, the Ampersand (&) operator will combine the texts and formulas.
- 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, 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")
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”
- 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.
- 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 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")
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”
- 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.
- 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.
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"
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”
- 8 & ” Years, ” & 6 & ” Months, ” & 22 & ” Days” —-> Now, the Ampersand (&) operator will combine the texts and formulas.
- 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.
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) 
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.
- 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.
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.
Very useful formula for HR Professionals.
Thanks Chamila for your feedback!
This was very helpful! Great information! Thank you very much!
Thanks so much! This made things a whole lot easier for me. As Chamila said, very useful formula for HR folks.
Thanks for your feedback, LuluBelle! Glad to know that it helped.
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.
Hi Justin,
Thanks for notifying this. I will check and update the article.
Regards
I want to subscribe
THANK YOU VERY MUCH for sharing!