Subtract dates in excel to calculate years of service

By |2018-05-19T04:28:53+00:00May 19th, 2018|

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.

Leave A Comment

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

I Know You Better!
Subscribe To My Newsletter
Be the first to get latest Excel updates and
exclusive content straight to your email inbox.
Yes, I want to receive updates
No Thanks! I Don't Want to Learn Excel.
close-link

Not Sure What to Learn in Excel?

Sign Up For My Actionable Excel Techniques You Can Use Right Away!
Send me Updates
You'll Get One Email Every Week!
close-link