To calculate the length of service in Excel, you need to determine the number of days, months, or years between two dates. Here’s how to do it:
Recording employee data
First, record the employee’s start and end dates. Click on column (1) and type in the date (2). Next, repeat these steps for all employees.
Tip: Add employee names to column A. This helps track how long each person has worked for your company.
How to calculate years of service?
To find years of service, use the DATEDIF function. This powerful tool calculates the difference between two dates. It can show results in days, months, or years.
Note: DATEDIF is not a built-in Excel function. You need to download and install the Analysis ToolPak add-in from Microsoft.
Click on the column (1) and type in =DATEDIF(A2,B2,”y”)&” years “&DATEDIF(A2,B2,”ym”)&” months” (2).
Tip: You can use any cell references instead of A2 and B2. For example, use D2:E2.
Alternatively, if you only want years, modify the formula.
=DATEDIF(A2, B2, “y”)& ” years” formula will provide you a number of years without the months.
Important: DATEDIF doesn’t account for leap years. This means results may not be completely accurate over multiple years. To fix this, create a custom formula. It should calculate the difference between dates based on days in each year.
Or, try this alternative formula: =INT(YEARFRAC(A2,B2)).
Finally, for even more precision, use this formula:
=DATEDIF(A2,B2,”y”) & ” years ” & DATEDIF(A2,B2,”ym”) & ” months ” & DATEDIF(A2,B2,”md”) & ” days”
This formula displays the complete date difference. You’ll see years, months, and days all together.
