This Excel tutorial explains how to calculate year of service in Excel using different calculation methods.
Excel calculate year of service
Having worked for human resources field for some years in different companies, I have seen different systems / companies use different calculation methods to calculate year of service for annual leave entitlement. In this post, I want to summarize how to use Excel to calculate year of service using different methods to help HR professionals.
Method 1 – calculate year of service in decimal places
Different legislation have different calculation method for year of service in decimal places, so it is necessary to tailor make a formula for specific needs, there is really no single solution.
But generally speaking, Excel has a Function called 360, which can serve the purpose with the assumption that each month has 30 days.
For example, an employee joined in Jan 1st of 2015, up till Jun 2nd 2017, year of service is
=DAYS360(DATE(2015,1,1),DATE(2017,6,2))/360 return 2.4194
Method 2 – Complete year of service as of January 1st
For payroll purpose, company would calculate employee’s year in decimal places. But for other benefits purpose, HR does not need to know the decimal places because most benefits are associated with complete year of service, but there are different definitions of completing year of service. One way is to calculate complete year of service as of January 1st of each year.
For example, an employee joined on Mar 1st, 2016.
As of Jan 1st 2017, employee has 0 completed year of service.
As of Jan 1st 2018, employee has 1 completed year of service.
To calculate this in Excel formula, use DATEDIF Function.
For example, as of Jan 1st 2018
=DATEDIF(DATE(2016,3,1),DATE(2018,1,1),"Y") return 1
This calculation method underestimates employees’ years of service compared with the actual year of service.
Method 3 – Complete year of service as of Dec 31st
For most companies, annual leave entitlement is given beforehand on Jan 1st of each year. For example, when an employee is joined on Mar 1st 2016, annual leave is given on the first date of hire on pro-rata baisis, and then given on Jan 1st 2017, instead of giving on Mar 1st 2017.
For this type of year of service calculation, we can use DATEDIF function but unlike method 2, we use Dec 31st as the end date parameter.
For example, year of service as of 2018 is
=DATEDIF(DATE(2016,3,1),DATE(2018,12,31),"Y") return 2
This calculation method overestimates employees’ years of service compared with the actual year of service.
Method 4 – Year of service in decimal places as of month end
A more accurate method to calculate year of service is to recalculate every month end. This calculation method is also being used in annual leave entitlement calculation to avoid too much underestimation and overestimation.
For example, when an employee is joined on Mar 2nd, 2016
as of Apr 30st, 2018, completed year of service is 2 year + 1 month / 12
To express this in Excel formula, this time we make use of “YM” parameter in DATEDIF Function
=DATEDIF(DATE(2016,3,2),DATE(2018,4,30),"Y")+DATEDIF(DATE(2016,3,2),DATE(2018,4,30),"YM")/12 return 2.8333
This formula works perfectly the start month is larger than the end month
=DATEDIF(DATE(2016,4,2),DATE(2018,3,30),"Y")+DATEDIF(DATE(2016,4,2),DATE(2018,3,30),"YM")/12 return 1.917
