How to Calculate Years of Service in Excel (Easily)
Ever wondered how the HR of your employer is always so precise about your employment record?
They even know how many days you have worked to date in total. Duh! How are they doing it for hundreds of employees daily 🤯
You’ll be amazed to know how easy it is to maintain the record of service for thousands of employees to the degree of perfection using Microsoft Excel.
Grab the practice workbook for this tutorial here and follow me to learn how to do it all by yourself.
Basic Method (No functions)
Who said you need functions to work out the period between two dates in Excel? The basic method to find years of service in Excel can be simply subtracting a date from another.
See here.
These are some payroll records from Company A’s employee database. So we have the name of the employee, his / her date of joining, and service of completion 📒
To find the years of service for each of them:
Step 1) Subtract the joining date from the service completion date.
Excel stores all dates as serial numbers. So, deducting a date from another date is just like subtracting a number from another for Excel.
The number 2113 is the count of number of days between 12 June 2018 and 25 March 2024.
Step 2) Divide this number by 365 (as each year has 365 days).
With this, you get the count of years between the joining and completion date which is 5.8 years ~ 6 years.
Step 3) Quickly drag and drop it down for the whole list.
Calculating years of service using the YEARFRAC function
To yield precisely accurate years of service between two given dates, we use the YEARFRAC function for example.
Let me show you how this function works by using the same example as above.
Step 1) Write the YEARFRAC function as below:
The arguments for this function are straightforward enough. Need the starting and the ending date of service. The basis argument is optional, and you can set it to 1.
Step 2) Give in the arguments for this function as below.
The years of service of John come out as 5.78 ~ 6. Same as we did through the basic formula above. But as you increase the decimal places, you might see a slight difference.
The results of the YEARFRAC function are more accurate. I recommend using it.
Step 3) Drag and drop the same formula to the whole list.
With the INT function
In the result 5.78, 5 represents the number of years, whereas 0.78 is the number of months on top of 5 years.
If you don’t want the resulting number to be something with decimal places but only a whole number 🎯
In other words, if you want the YEARFRAC function to only yield you the number of completed years of service without the number of months on top, here’s what to do:
Step 1) Wrap the YEARFRAC function into the INT function.
The INT Function rounds a number down to the closest integer.
Hence, it will round the number down to the completed years only and remove the decimal places (that represent the months).
Calculating years of service using the DATEDIF function
The DATEDIF is a mysterious function.
Excel doesn’t offer a lot of help or information on how it works but once you know how it does (which I will explain to you just now), it is the best one for calculating the period between two dates.
Years of Service
To find the years of service using the DATEDIF function,
Step 1) Write the following formula
The first argument of the DATEDIF function specifies the starting date, second tells the ending date.
And the third argument which is a Y in quotation marks tells the DATEIF function to give back the number of years of service only.
Step 2) Press Enter to have the following results.
Gives back 5 years (with no decimal places representing months). Works great, no?
Years and Months of Service
There are multiple ways how you can find the years of service + months of service in Excel.
But tweaking the DATEDIF function as I am going to do it just now will calculate the number of months in addition to the completed years only 🪁
See here.
Step 1) Change the third argument of the DATEDIF function as here.
Everything remains the same, the starting and the ending date.
We have only changed the third argument from “y” to “ym” which tells Excel to give back the number of months only.
Step 2) Press Enter to see the results.
9? Huh? Confused? Let me explain.
John joined on June 18. So, his 5 years were completed on June 23.
His service completion date falls in March 2024. How many months are there between June 2023 and March 2024? Give it a quick count on your fingers. Makes 9 months.
That’s what the DATEDIF function has done. It calculates the number of months between two dates but doesn’t include the months that have completed a year.
Now, to calculate the number of years + months completed by John:
Step 3) Combine two DATEDIF functions using a concatenate (&) as follows:
- The first DATEDIF function will return the number of years i.e., 5.
- Then there are concatenates (&) to join this result (5) with a space character and then the text string “Years”.
- Again, concatenate signs (&) to merge this a space character and the second DATEDIF function that will return the number of additional months i.e., 9.
- And finally, concatenate signs (&) before another space character and the text string “Months”
Step 4) Hit Enter. You’d love the results.
Step 5) Drag and drop it to all the employees.
Wow! That’s clean and ready to go 🤩
Years, Months, and Days of Service
Taking the above function, a step ahead, this time we will write it such to find the number of days too.
Follow me.
Step 1) Write the DATEDIF below to find the number of days that do not make a month or a year.
Gives back 13. These are the days on top of 5 years and 9 months for John.
Let us now combine this function with the DATEDIF function for years and months.
Step 2) Concatenate this DATEDIF function to the merged DATEDIF function from the above section as follows:
Just an additional concatenate (&) sign with the DATEDIF function to find the number of additional days.
And another concatenate sign (&) before the text string “Days”
Drag and drop the formula to have the results ready for all the employees in a blink 👀
I love how clean, well-defined, and accurate the results are, and what about you?
Years, Months, and Days of Service Until Today
What if there’s no completion date, and you just look forward to knowing the exact length of service of each employee to the present date?
Easy peasy.
Step 1) Just replace the service completion date in the concatenated DATEDIF function above with the TODAY function.
All instances of C2 were replaced with the TODAY() function.
Conclusion
Playing around with dates in MS Excel is always fun. Especially, if you know how to use the Date & Time functions of Excel to your best.
In this tutorial, we have discussed two top-notch functions from Excel’s Date & Time functions library. But there are so many more there for you to explore.
Read my other Excel tutorials on Excel Date and Time functions here.
- How to Extract Month From Date in Excel: Step-by-Step
- Today’s Date in Excel: Function and Formula Examples
- Excel NOW Function Guide: Get Current Date and Time
- Excel DATEDIF Function: Find Difference Between Dates
Hope you enjoy reading them as much as I did writing them. Happy calculating to you!