How to Calculate Years and Months of Service in Excel (With Free Length of Service Calculator Template)

Having an attentive and responsive HR department is a critical factor in employees' work satisfaction. People feel the need to be noticed, even if all they get is a congratulation email. These small touches make them feel accomplished and increase their loyalty towards the company. Besides, if you keep an Excel file with all your employees, it's effortless to know how many months and years each employee has worked and when is the next work anniversary.

Check out the following straightforward tutorial to calculate the length of service and find the next work anniversary of an employee.

Define important columns for calculating the length of service

To calculate the length of service, you need to know the employee's employment date and if they still work for the company. Therefore, define the following columns:

• Employee's name
• Employee's employment date, formatted as Date
• "Still Working" column, formatted as Text, which tells you in "yes" and "no" if a person still works for the company
• Employee's end of employment date, formatted as Date, tells you the last working day for an employee who doesn't work anymore.

Tip: To make it easier for you to spot the employees who don't work for the company anymore, use Excel's conditional formatting to highlight employees' data in different colors based on the "Still Working" value cell.

Learn to use the DATEDIF() function

The DATEDIF() function calculates the number of years, months, and days between two dates. It needs three parameters:

• The start date of the period you want to calculate.
• The end date.
• The unit measure of the result ("y" – years, "m" – months, "d" – days, or mixed measures such as "YM" – years and months and "MD" – months and days).

All you have to do to calculate a person's length of service is to use DATEDIF() function with the following parameters:

• The employee's employment date
• If the employee still works for the company ("Still Working" = "yes"), today's date; if the employee doesn't work for the company anymore ("Still Working" = "no"), the end of employment date.
• A unit measure at your choice.

Tip: Excel has TODAY() function, which automatically delivers today's date.

If you want to be more specific than that and have the length of service with the precision of months, use the formula DATEDIF(DATE_OF_EMPLOYMENT, TODAY(), "Y") & "Years," & DATEDIF(DATE_OF_ EMPLOYMENT, TODAY(), "YM") & "Months instead.

Calculate how many days are until the next service year

Knowing the length of service for an employee is useful. Still, most of the time, you want to know how many days are until the next work anniversary so you can plan the celebration or take the measures stated in the company's policy. Calculating the next work anniversary helps you plan a celebration or send a congratulation email, send a memo to the Accounting Department to update the payroll or offer a bonus, or update the leave management system.

Remember to consider only the employees who still work in your company. For former employees, fill all the columns referring to the next work anniversary with a clear message such as "NOT WORKING ANYMORE."

To find out how many days remain until the next work anniversary of an employee, we first need to calculate when the next work anniversary will be. We compare the day and month of the employment date with today's date and month to determine if the following work anniversary will be this year or next year.

After we find out when the next work anniversary will be, we compute the number of remaining days by using the formula DATEDIF(TODAY(), DATE_OF_NEXT_SERVICE_YEAR," d"). You can choose to display the remaining time until the next work anniversary in months too.

Download our simple Length of Service Calculator Excel Template

If you don't want to waste time with Excel formulas, download our free Length of Service Calculator Excel template. It calculates the length of service, the date of the next work anniversary, and how many days remain until your employees' subsequent work anniversaries. It also includes a list of employees you can easily personalize and filters for quick sorting. Having the right tools saves you time and energy to plan the next work celebration!

Download the Length of Service Calculator Excel Template

Smart managers choose dynamic HR Software - with automatic feeds and notifications

If you want more than just a static sheet with some formulas, we recommend considering LeaveBoard. You will have access to employees' work anniversaries and lengths of service, be able to generate reports with key data for your organization and receive automatically generated calendar feeds to add to Outlook and Google Calendar. You will also receive notifications via email for future work anniversaries. Sounds good? That's more! LeaveBoard is entirely free for businesses with less than nine employees.

What are you waiting for? Please register for an account and boost your team's efficiency and performance with our web-based HR app.