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.

Table of Contents

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.

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.

Pro tip

We have divided the number of days by 365 whereas some years have 366 days (leap years).

This method will work fine if the period between the joining and closing date doesn’t include any leap years.

For example, from 15 May 2021 to 10 Aug 2023 or any period in between where all years have 365 days 📆

However, if used otherwise (for periods including leap years), it produces a slightly different answer.

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.

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.

Pro tip

The TODAY() function returns the date of today. It is a volatile function.

Every time you refresh your workbook or reopen it again, the results of this function will update.

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!