Use Excel to calculate how many years and months an employee has worked in your company (the length of service or tenure). All you need are simple formulas. But if you want a faster solution, download for free our ready to use the length of service calculator Excel template.
It's always useful to know how long an employee has worked in your company. Based on the length of service, the company may grant more leave days or adjust the wage. Furthermore, loyal employees may be congratulated and rewarded. You don't want to miss a celebration just because you didn't calculate employees' lengths of service in time.
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:
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:
All you have to do to calculate a person's length of service is to use DATEDIF() function with the following parameters:
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.
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
Enter the date of joining
Select month | Select date | Select year
Enter the end date
Select month | Select date | Select year
Result
Your lenght in service is 40 years, X months, Y days.
Your next work year in service takes place in X months, Y days.
De sters asta. Vezi un exemplu concret http://biswa.net/service-length-calculator.html . Result il afisezi dupa ce apesi pe calculate, iar at this date sa fie precompletat cu data de astazi.
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.
Automate your employee management with the easy to use online HR software.
Easy set-up ● No contracts required ● No credit cards