How to Calculate Years of Service in Excel (Easy Formulas)

Excel can be quite useful when you’re working dates. It has some powerful built-in formulas that allow you to do calculations based on dates.

One common scenario where you have to perform calculations with dates would be to find out how many years or months or dates have elapsed between two given dates.

This could especially be useful if you want to calculate years of service of employees in your company (where you have their joining date and the end date).

In this tutorial, I will show you some formula techniques that you can use to calculate the years of service in years (or years and months and days).

Calculating Years of Service Using the YEARFRAC Formula

If you only want to get the total number of years of service between two dates, you can use the YEARFRAC function.

Below I have a data set where I have the start date and end date for a set of employees, and I want to calculate their years of service in column D.

Dataset to calculate years of service

You can do that using the below formula:

=INT(YEARFRAC(B2,C2,1))
INT and YEARFRAC Formula to get Years of service

The YEARFRAC function gives you the total number of years between two dates (where full years are shown as integers and incomplete years are shown as decimals).

The INT function then extracts the integer part from the result of YERAFRAC, which is the total number of years of service between the two given dates.

Also read: How to Get the Number of Days in a Month in Excel?

Calculating Years of Service Using the DATEDIF Formula

If you need to calculate the use of service where you may want to get the number of years as well as the number of months and/or days, then you are better off using the DATEDIF formula (pronounced as DATE DIF formula)

It’s one of the undocumented functions in Excel, which means that you would not find any help on this function from Microsoft or see this as a part of the IntelliSense when you type it in a cell in Excel.

But when it comes to calculating the total years of service between two dates, this is the best function to use.

Calculating Only the Years of Service

Below I have a data set where I have the start date and end date for a set of employees, and I want to calculate the total number of years of service for each employee.

Dataset to calculate years of service

Below is the formula that will do this:

 =DATEDIF(B2,C2,"y")
DATEDIF formula to get years of service

The DATEDIF formula takes the start and the end date as the first two arguments and gives the total years between the dates (as I have specified Y as the third argument).

Calculating Service Duration in Years and Months

Below I again have the same data set and I want to calculate the duration of service of each employee in years and months.

Dataset for year and month

Below is the formula that will do this:

=DATEDIF(B2,C2,"y")&" Years "&DATEDIF(B2,C2,"ym")&" Months"
DATEDIF formula to get years and months of service

The above formula contains two DATEDIF functions

  • The first one is used to calculate the total number of completed years between the two dates
  • The second one is used to calculate the total number of months between the two dates, but does not include those months that have completed an year. For example, if the difference between two dates is 14 months, this would only return 2, as the first 12 months completed to become a year.

These DATEDIF functions are combined with the text “Years” and “Months”, so that the result is more meaningful and the user would know how many years and months of service has been completed.

Calculating Service Duration in Years, Months, and Days

And if you want to get the total service duration in Years, Months as Days, you can use the below formula:

=DATEDIF(B2,C2,"y")&" Years "&DATEDIF(B2,C2,"ym")&" Months "&DATEDIF(B2,C2,"md")&" Days"
DATEDIF formula to get service in years months and days

The above formula uses three DATEDIF functions to find out the numbers of years months and days in the total service and then combine these using the & operator.

I have also added the words Years, Months, and Days to make the result easy to comprehend.

Calculating Service Duration Till Today

If you want to calculate the total time elapsed between a given date and the current date, you can use the same DATEDIF formula, and replace the end date with the TODAY formula.

TODAY() formula gives you the current date based on your computer settings. It’s a volatile formula, which means that the next time you open the workbook or make a change in the workbook, this formula would be refreshed, and if the date has changed, then the new date would be used.

Below I have a data set where I want to calculate the total years of service for each employee from their joining date till today.

Dataset to calculate years of service till today

Here’s the formula that will do that:

=DATEDIF(B2,TODAY(),"y")&" Years "&DATEDIF(B2,TODAY(),"ym")&" Months "&DATEDIF(B2,TODAY(),"md")&" Days"
DATEDIF formula to get service duration till today

Note: You can also use this formula to calculate the age of a person when you have their date of birth

Also read: Calculate Fiscal Year from Date in Excel

Calculate the Date After a Specific Duration of Service

Another scenario involving total tenure of service could be when you want the date after a specific number of years in service.

For example, let’s say you’re the HR in a company that offers loyalty bonuses to employees who complete 10 years in service.

You have a list of employees and their joining data shown below, and you need to know on what date their loyalty bonus would be due.

Date of Bonus dataset

Below is the formula that will give you this date:

=EDATE(B2,10*12)
EDATE formula to calculate date after specific years

The EDATE formula is meant to return the date after or before the specified number of months, but we have hacked it to give us the date after 10 years.

To do this, instead of specifying the number of months as the second argument, we have specified 10*12, which is the total number of months in 10 years.

So these are some of the ways you can use to calculate the years of service in Excel and get the result in years, years, and months, or years months and days.

I also covered how to get the date after a specific number of years using the EDATE formula.

I hope you found this tutorial useful.

Other Excel tutorials you may also like:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster