Subtract dates in excel to calculate years of service

Using Excel, years of service can be calculated with the DATEDIF function. Its purpose is to compute the differences between two sets of date, simply put, it subtracts dates in Excel. Thus, the DATEDIF function is what you’ll need if you want to calculate years of service (or excel date difference) using the start date and the end date (or current date).

Depending on your needs, this function can help you calculate the time between two dates such us: years between two dates, months between two dates, or even days between two dates.

Note that there are several ways and functions to get this work done. But here, I only present what works well, easy to understand, and of course save you some time.

Here’s the situation:

You’re an accountant in this organization where management wants to recognize some employees for their longevity with the organization. Now you’ve been asked to calculate the number of years served by each and every employee in the organization.years of service|time between dates in excel

Here’s what to do:

Step #1: Insert the start date in one cell and the end date in an adjacent cell

In this example, the start date is in cell C3 and the end date is in cell D3.

years of service|time between dates in excel

Note: With the end date, you can type a specific date or use the current date. To insert today’s date into a cell, type =TODAY() and press enter. Excel will pick the date your computer is using at the moment.

Calculating years between the two dates

Step #2: Use this formula to calculate the years only

=DATEDIF(C3,D3,”Y”)

years of service|time between dates in excel

Step #3: Copy the formula to the rest of the cells using the fill handle.

Just double-click on the fill handle to complete this step.

years of service|time between dates in excel

Calculating years plus months

With what we’ve just done, we only get the year differences. What about if you want to calculate both the years and months each employee served in the organization?

Here’s what to do:

Step #4: Use this formula to calculate both the years and months

=DATEDIF(C3,D3,”Y”) & ” years ” & DATEDIF(C3,D3,”YM”) & ” months”

years of service|time between dates in excel

Calculating years plus months, and days:

Yet, another person may be interested in calculating the number of years, months and even days between two dates all in one cell.

Here’s what to do:

Step #5: Use this formula to calculate the years, months, and days

=DATEDIF(C4,D4,”Y”) & ” years ” & DATEDIF(C4,D4,”YM”) & ” months ” & DATEDIF(C4,D4,”YM”) & ” days”

years of service|time between dates in excel

Calculating years and months in decimals:

For benefiting employees, the HR doesn’t need the decimals since most benefits are only based on complete year service. But for payroll purposes, accountants would have to calculate employee’s years of service in decimals.

Here’s what to do:

Step #5: Use this formula to calculate years’ of service in decimals:

=DATEDIF(C4,D4,”Y”)+DATEDIF(C4,D4,”YM”)/12

years of service|time between dates in excel

If you have any further questions, just let me know in the comment session below.

2018-05-19T04:28:53+00:00May 19th, 2018|Microsoft Excel|0 Comments

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.