How-To

# How to Calculate Years of Service in Excel

If you have employees, you can use Excel to work out how long they’ve been working for you. Learn how to calculate years of service in Excel.

Spreadsheets are a great way to keep track of employee information and statistics. You may have information such as position, salary, and the dates an employee started or exited the business. Excel makes it easy to use this data to calculate other helpful information, such as years of service, as well.

For example, this information can be used to calculate the number of years of service an employee has. This could be useful for calculating benefits or if you need to provide a reference. If the employee is still working for you, you can also calculate their years of service to date.

Here’s how to calculate years of service in Excel.

## How to Calculate Years of Service in Excel Using YEARFRAC

If you’re only looking to find the total number of completed years served, then the YEARFRAC function is a simple way to do so. This function returns the difference between two dates as a fraction of a year; it is commonly used to work out things such as the portion of a full year’s benefits that would need to be paid.

If the length of time is longer than a year, the value in front of the decimal point will be the total number of complete years between those two dates. We can use the INT function to return just this number.

To use the YEARFRAC formula to calculate years of service in Excel, follow these steps.

1. Click in the cell where you want the number of years of service to appear.
2. Type: =INT(YEARFRAC(
3. Click the cell containing the start date, then type a comma.
4. Click the cell containing the end date, followed by two closed parentheses.
5. Press Enter and the years of service will calculate.
6. You can use autofill in Excel to copy your formula to other cells.

## How to Calculate Years of Service in Excel Using DATEDIF

The YEARFRAC function is useful if you only want to calculate the number of years of service. If you want to include months or even days of service, you’ll need to use the DATEDIF function. You can also use this to calculate just the total number of years of service.

To calculate length of service in Excel using DATEDIF, follow these steps.

1. Click the cell where you want the years of service to appear.
2. Type: =DATEDIF(
3. Click the cell containing the start date, then type a comma.
4. Click the cell containing the end date, type a comma, then type: "y")
5. Press Enter and the years of service will calculate.

## How to Calculate Years and Months of Service

If you want to include the total number of months worked, you can also do this using the DATEDIF function. You’ll need to include some text to differentiate between the years and months.

To calculate years and months of service using DATEDIF in Excel, follow these steps.

1. Click the cell when you want the years and months of service to appear.
2. Type: =DATEDIF(
3. Click the start date cell, then type a comma.
4. Select the end date cell, type a comma, then type: “y”)&“ Years ”&DATEDIF(
5. Click the start date cell, then type another comma.
6. Select the end date cell, type a comma, then type: “ym”)&“ Months”
7. Press Enter and the years and months of service will calculate.

## How to Calculate Years, Months, and Days of Service

If you want even more accuracy, you can include the total days of service in your calculation. You’ll need to add additional text to differentiate between the values.

To calculate years, months, and days of service using DATEDIF in Excel, follow these steps.

1. Select the cell where you want the time of service to appear.
2. Type: =DATEDIF(
3. Select the start date cell, then type a comma.
4. Click the end date cell, type a comma, then type: “y”)&“ Years ”&DATEDIF(
5. Select the start date cell, then type another comma.
6. Click the end date cell, type a comma, then type: “ym”)&“ Months ”&DATEDIF(
7. Select the start date cell, then type a comma.
8. Click the end date cell, type a comma, then type<: “md”)&“ Days”
9. Press Enter and the years, months, and days of service will calculate.

## How to Calculate Years of Service up to the Current Date

All of the above calculations assume that you are calculating the length of service for someone with a start date and end date. If the employee is still employed, then you’ll want to calculate the years of service from their start date to the present day. The beauty of this method is that the result will update every day so will always remain accurate.

To calculate years of service in Excel up to the current date, follow these steps.

1. Choose one of the methods above.
2. Follow the instructions as written, but whenever you are asked to click the end date cell, instead type: TODAY()
3. For example, the completed formula for calculating the years of service using YEARFRAC would look something like the following. The reference for the cell containing the start date may be different for your formula.
4. Press Enter and the formula will calculate years of service up to today’s date.

Knowing how to calculate years of service in Excel is just one way to make the most of the spreadsheet’s powerful features. The more you learn about Excel, the more useful it becomes.

1. Travis

October 12, 2022 at 5:46 am

How can I calculate the subtotal of years, months, and days. I am doing a spreadsheet of employees that have returned back to the worksite in the past. I cannot seem to get the subtotal of a total amount of years, months, and days. Thanks!

• sharon wang

February 17, 2023 at 4:41 am

I started work on 4th Oct 2022 till today 17 Feb 2023?

How long I have been working in my company?

To Top