ExcelInsider Team
In Excel, calculating years of service is a common thing we perform. It is needed when we want to track years of services, employee tenure etc. In simple words, it is the calculation of getting the difference between two dates, but on a large scale. In HR departments or administrative works, it’s important to measure how long an employee has worked from their hire date to current date (or a specific end date)
To calculate years of service in Excel, follow these steps:
➤ Select a cell where you want to show the years of service.
➤ Use the formula =DATEDIF(start_date, end_date, “Y”)
➤ Click Enter.
➤ Drag the formula down using fill handle to calculate the whole column.
In this article, we’ll show 12 methods to calculate years of service in Excel using Excel functions like DATEDIF, YEARFRAC, and INT, VBA with examples for handling different scenarios like partial years and retirement calculations.
Using the DATEDIF Function to Calculate Only the Full Years of Service
The DATEDIF function is a hidden Excel function. It is used to calculate the difference between two dates in specific units (years, months, days).
In this method, we’ll focus on calculating only the complete number of years of service between the Start Date and the End Date. This is useful for high-level reporting where partial years are not considered.
Steps:
➤ Open the worksheet in the Excel file.
➤ In column F, use the formula in cell F2:
=DATEDIF(D2, E2, “Y”)
This calculates the full number of years between Start Date (D2) and End Date (E2).
➤ Drag the formula down from cell F2 to fill the remaining rows for all employees.
Applying the DATEDIF Function to Calculate the Total Number of Months
This method uses the DATEDIF function to calculate the total number of full months between the Start Date and End Date. This includes all months across the years.
Steps:
➤ In column F, enter the following formula in cell F2:
=DATEDIF(D2, E2, “m”)
This returns the total number of full months between the two dates.
➤ Drag the fill handle down from cell F2 to copy the formula for next rows.
Use the DATEDIF Function to Calculate the Total Number of Days
We use the DATEDIF function to calculate the total number of full days between a Start Date and End Date. It’s useful for detailed reporting, project based roles, or time sensitive HR decisions where counting every day is important.
Steps:
➤ In cell F2, enter the formula:
=DATEDIF(D2, E2, “d”)
➤ Drag the formula down from cell F2 to apply it to all rows you want.
Let the DATEDIF Function to Calculate Years and Months
This function can calculate the service duration in complete years and remaining months. It uses two DATEDIF functions combined. It’s suitable where partial years need to be shown more precisely without days.
Steps:
➤ Use this formula in cell F2:
=DATEDIF(D2, E2, “Y”) & ” years, ” & DATEDIF(D2, E2, “YM”) & ” months”
These two DATEDIF functions will return the number of full years and remaining months between the dates.
➤ Copy the formula down from F2 to the rest of the column.
Application of the DATEDIF Function to Calculate Years, Months, and Days
This method provides a complete breakdown of service duration in years, months, and days using three DATEDIF components. It’s used for detailed records, retirement eligibility checks, or formal certificates where exact service duration is needed. Let’s now navigate to the worksheet named “DATEDIF Y-M-D” in the sample Excel workbook you have downloaded.
Steps:
➤ In cell F2, enter this formula:
=DATEDIF(D2, E2, “Y”) & ” years, ” & DATEDIF(D2, E2, “YM”) & ” months, ” & DATEDIF(D2, E2, “MD”) & ” days”
This formula will calculate full years, remaining months, and leftover days.
➤ Fill down the formula to all employee rows in column F.
Using the DATEDIF Function to Calculate Service Duration Until Today’s Date
We can use the DATEDIF function to calculate the service duration from the employee’s Start Date to the current date instead of a fixed End Date. We will use the TODAY function to determine today’s date.
Steps:
➤ In cell F2, enter the following formula:
=DATEDIF(D2, TODAY(), “Y”) & ” years, ” & DATEDIF(D2, TODAY(), “YM”) & ” months”
This will calculate the number of complete years and months from Start Date to today.
➤ Now, copy the formula down using the fill handle from F2 for all rows in the worksheet.
Next Use the YEARFRAC Function to Get the Number of Whole Years
The YEARFRAC function calculates the exact fractional number of years between two dates. When wrapped with INT, it returns the number of whole years. It is similar to DATEDIF with “Y”, but it is mostly used in financial or actuarial contexts for high precision.
Steps:
➤ Now, click in cell F2, enter the formula, and click Enter.
=INT(YEARFRAC(D2, E2))
This will calculate the number of full years, discarding any partial years in cell F2.
➤ Now autofill the formula down the entire column F using the fill handle from cell F2.
Dealing With the YEARFRAC Function to Calculate Service Duration Until Today’s Date
This method uses the YEARFRAC function to calculate the exact number of years from the Start Date up to the current date. It’s good for real-time analytics or calculations that depend on partial service.
Steps:
➤ In cell F2, input the following formula:
=INT(YEARFRAC(D2, TODAY()))
This will return the number of full years of service as of today.
➤ Drag the formula down from F2 to fill the column for all rows.
Applying NETWORKDAYS for Workday-Based Service Calculation
The NETWORKDAYS function calculates the number of working days (Monday–Friday) between two dates. It automatically excludes weekends.
Steps:
➤ In cell F2, enter this formula:
=NETWORKDAYS(D2, E2)
This formula will calculate the number of workdays between the Start Date and End Date.
➤ Drag the formula down the column to fill the rest of the employee rows.
Handling With Simple Date Subtraction and Division
This formula will calculate the approximate number of years of service. It subtract the Start Date from the End Date and divide the result by 365. It’s a quick and simple way to get an estimate. However it doesn’t account for leap years or exact month lengths.
Steps:
➤ In cell F2, type this formula:
=(E2 – D2) / 365
This calculates the total number of days and converts them roughly into years.
➤ Fill the formula down the column for all employees.
➤ To change the decimal counts select the column > Right-click your mouse > Format Cells. It will open up a new window.
➤ To format the decimal values select the entire column or the cell range of F2:F10.
➤ Open the Context Menu and choose the option Format Cells….
➤ Go to Number > Define the length of decimal places > Press OK.
➤ This is the final output-
Using DAYS or DAYS360 and Division
This method calculates the service duration using the DAYS or DAYS360 function to determine the number of days between two dates. It then divides the result by 365 to estimate the number of years.
The DAYS360 function assumes 360 days in a year.
Steps:
➤ In cell F2, use this formula:
=DAYS(E2, D2) / 365
This will calculate the total days between the Start Date (D2) and End Date (E2), then convert it into years.
➤ Alternatively, you can use DAYS360 for financial use:
=DAYS360(D2, E2) / 360
This assumes 360 days in a year and is used for financial modeling.
➤ Fill the formula down from cell F2 to apply it to all employee rows using fill handle
Frequently Asked Questions
How do I sort years of service in Excel?
To sort years of service in Excel:
- Select the Data: Click on any cell within the column containing the years of service.
- Access the Sort Feature: Navigate to the Data tab on the Ribbon.
- Sort by ascending order (shortest to longest tenure), click on Sort Smallest to Largest.
- Sort by descending order (longest to shortest tenure), click on Sort Largest to Smallest.
What is the years of service method?
The “years of service method” is the process of calculating the total number of complete years some have been employed. It is typically from their hire date to the current date or a specified end date.
Is DATEDIF still in Excel?
Yes, the DATEDIF function is still available in Excel. However, it’s now a “hidden” function. That means it doesn’t appear in the function list or provide formula suggestions as you type. Despite this, you can use it by manually entering the formula:
=DATEDIF(start_date, end_date, “Y”)
How do I handle employees with multiple service periods?
For employees with multiple non continuous service periods you can calculate the duration of each period separately and then sum them:
- Calculate Each Period: Use DATEDIF or YEARFRAC for each employment period.
- Sum the Periods: Add the results to get the total years of service.
For example:
=DATEDIF(start1, end1, “Y”) + DATEDIF(start2, end2, “Y”)
Concluding Words
We have described 11 different methods to calculate the years of service. Each method has its own unique approach and use cases. You can use the simple DATEDIF function to the precise YEARFRAC and also custom VBA functions. All these methods allow flexibility for additional detail and real time data requirements. Let me know if you need further support with any of the methods or require additional resources.
