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 the year of service in Excel. And for this, you are going to use the DATEDIF function. If you never heard about this function, you 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/start date.
In the following example, we have a start date and an end date.
And now let’s use the following steps:
- Type “=DATEDIF(” in cell C2.
- Refer to the start date, which is A2, and enter a comma.
- Refer to the end date, which is B2, and enter a comma.
- Enter “y”, enter close parentheses, and hit enter to get the result.
Let me explain: When you use “y” in the DATEDIF function it returns the complete year 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 year, month, and dates. You can use the following formula.
=DATEDIF(A2,B2,"y")&" year(s), "&DATEDIF(A2,B2,"ym")&" months(s), "&DATEDIF(A2,B2,"md")&" days(s)."
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)"
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 the A1 cell, we have 01-Jan-2007 and today is 07-May-2023. In the result formula has returned 17.35 which means 17.35 years is the years of service.
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(A2, 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, we have an option to decide is the employee is working or not, if the status is ‘Working” then the formula will use the TODAY to get the current date. But if that person is not working anymore and the status is other than “Working” then it will take the date from the cell B4 which is the second leaving date.
In the end, it will return a sum total years of services in the result. This formula help 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 customer 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 will calculate the total years of service based on the dates you specify, using today’s date when no second leaving date is specified. It will ensure that periods, where the employee wasn’t employed, are not included in the years of service calculation.
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 needs to be entered in the correct format in Excel to ensure that Excel correctly uses them as dates rather than text.