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:
Click in the cell where you want the number of years of service to appear.Type: =INT(YEARFRAC(
Click the cell containing the start date, then type a comma. Click the cell containing the end date, followed by two closed parentheses. Press Enter and the years of service will calculate. 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:
Click the cell where you want the years of service to appear.Type: =DATEDIF(
Click the cell containing the start date, then type a comma. Click the cell containing the end date, type a comma, then type: “y”)
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:
Click the cell when you want the years and months of service to appear.Type: =DATEDIF(
Click the start date cell, then type a comma. Select the end date cell, type a comma, then type: “y”)&“ Years ”&DATEDIF(
Click the start date cell, then type another comma. Select the end date cell, type a comma, then type: “ym”)&“ Months”
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:
Select the cell where you want the time of service to appear.Type: =DATEDIF(
Select the start date cell, then type a comma. Click the end date cell, type a comma, then type: “y”)&“ Years ”&DATEDIF(
Select the start date cell, then type another comma. Click the end date cell, type a comma, then type: “ym”)&“ Months ”&DATEDIF(
Select the start date cell, then type a comma. Click the end date cell, type a comma, then type: “md”)&“ Days”
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:
Choose one of the methods above.Follow the instructions as written, but whenever you are asked to click the end date cell, instead type: TODAY()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. Press Enter and the formula will calculate years of service up to today’s 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. Comment
Δ
