How-To

How to Calculate Years of Service in Excel

Sheets featured

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 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 calculate years of service using YEARFRAC:

  1. Click in the cell where you want the number of years of service to appear.
  2. Type: =INT(YEARFRAC(
    excel yearfrac
  3. Click the cell containing the start date, then type a comma.
    excel yearfrac formula start date
  4. Click the cell containing the end date, followed by two closed parentheses.
    excel yearfrac complete formula
  5. Press Enter and the years of service will calculate.
    excel yearfrac years of service
  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 years of service using DATEDIF:

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

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:

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

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:

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

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:

  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.
    excel yearfrac today
  4. Press Enter and the formula will calculate years of service up to today’s date.
    excel years of service to date

Excel at Excel

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.

Once you’ve created your formulas to calculate years of service, you can learn how to hide a formula in Excel so that no one else can edit or delete them. Knowing how to apply a formula to an entire column could also be useful. If your start date and end date information is in a different sheet, you might want to know how to pull data from another sheet in Excel.

If things go wrong, knowing how to find a circular reference in Excel can help you track down the cause of the problem.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top