How To Calculate The Length Of Service In Microsoft Excel

Calculating the length of service in Excel involves determining the length of time that an individual has been employed in a company or organization. Calculating the length of service in Excel can help organizations track employee performance, make informed decisions about promotions and salary increases, and identify areas where additional training or support may be needed.

In this tutorial, we will learn how to calculate the length of service in Microsoft Excel. In Microsoft Excel calculating the length of service is a simple process. We can utilize various built-in functions for this i.e. YEARFRAC, YEAR, and DATEDIF. We can also calculate the length of service in years using simple algebraic calculations.

Here we possess a dataset that indicates the starting and ending dates of employment of certain workers in a firm. Our objective is to determine their length of service

Method 1: Using the DATEDIF Function to Calculate the length of service

The DATEDIF function in Excel calculates the difference between two dates in terms of years, months, or days. The syntax for the DATEDIF function is as follows:

Syntax of DATEDIF

=DATEDIF(start_date, end_date, unit)

Where:

start_date: The start date of the period you want to calculate the difference from.

end_date: The end date of the period you want to calculate the difference to.

unit: A string that specifies the unit of time you want to use for the calculation. This can be “Y” for years, “M” for months, or “D” for days.

Note that the DATEDIF function is not documented in Excel’s official help files and may not be available in future versions of Excel.

Step 1 – Choose a Blank Cell and Place an Equals Sign

Choose a blank cell where you want to calculate the length of service.
Place an Equals sign.

Step 2 – Use the DATEDIF Function to Calculate the Years

The DATEDIF function is commonly used to extract years, months, or even days in Excel.
The syntax to calculate months of the service will be:

DATEDIF(B2,C2, “Y”)

Where C2 and B2 are the end date and start date, respectively.
The third argument “Y” specifies the information to be extracted i.e. years.

Step 3 – Enter an Ampersand Operator and Enter “Year”

Enter an Ampersand operator next to the DATEDIF function.
Then, enter “years” as we are extracting years from the first DATEDIF function.
The syntax will be:

DATEDIF(B2,C2, “Y”) & “year”

Step 4 – Use the DATEDIF Function to Calculate the Months

The syntax to calculate months of the service will be:
DATEDIF(B2,C2, “Y”) & “year” & DATEDIF(B2,C2, “YM”)

Where C2 and B2 are the end date and start date, respectively.
The third argument “YM” specifies the information to be extracted i.e. months.

Step 5 – Enter an Ampersand Operator and Enter “Month”

Enter an Ampersand operator next to the DATEDIF function.
Then, enter “months” as we are extracting months from the second DATEDIF function.

DATEDIF(B2,C2, “Y”) & “year” & DATEDIF(B2,C2, “YM”) & “months”

Step 6 – Press the Enter Key

Press the Enter key.

Step 7 – Use Autofill to Calculate the Length of Service for Each Employee

Use Autofill to calculate the service for each employee.

Method 2: Using the YEARFRAC Function to Calculate the Service

The YEARFRAC function in Excel calculates the fraction of the year between two dates. This function is useful when you want to calculate the length of time between two dates in terms of years, with decimal values indicating the fraction of a year.

Syntax of YEARFRAC

The syntax for the YEARFRAC function is as follows:

=YEARFRAC(start_date, end_date, [basis])

Where:

start_date: The start date of the period you want to calculate the fraction of the year from.

end_date: The end date of the period you want to calculate the fraction of the year to.

basis: An optional argument that specifies the day count basis to use in the calculation. This argument can be omitted, in which case it defaults to the US (NASD) 30/360 day count basis. Other options for the basis argument include “actual/actual”, “actual/360”, and “actual/365”.

The YEARFRAC function returns a decimal value that represents the fraction of a year between the two dates. For example, if the start date is January 1 and the end date is June 30, the YEARFRAC function would return 0.5 (indicating that half a year has elapsed between the two dates).

Step 1 – Select a Blank Cell and Place an Equals Sign

Select a blank cell.
Place an equals sign.

Step 2 – Enter the First YEARFRAC Function to Extract Years

The syntax would be:

ROUND(YEARFRAC(B2,C2,3),0)

The first argument of the YEARFRAC function i.e. B2 is the start date and the second argument i.e. C2 is the end date.

Step 3 – Enter an Ampersand and Enter “ years “

Enter an Ampersand operator next to the YEARFRAC function.
Enter “ years “ next to the ampersand operator.

ROUND(YEARFRAC(B2,C2,3),0)&” years ”

Step 4 – Enter the INT Function and the Second YEARFRAC Function to get the Months

Enter the INT function.
Nest the second YEARFRAC function inside the INT function.
Open three parentheses before the second YEARFRAC function.
The syntax would become:

ROUND(YEARFRAC(B2,C2,3),0)&” years ” & INT(((YEARFRAC(B2,C2,3))

Step 5 – Now Place a Subtraction Sign

Now place a subtraction sign.

Step 6 – Enter ROUNDDOWN Function and Enter the Third YEARFRAC Function as ROUNDDOWN’S First Argument

Enter ROUNDDOWN Function.
Enter the third YEARFRAC function.

ROUND(YEARFRAC(B2,C2,3),0)&”year”&INT(((YEARFRAC(B2,C2,3))-ROUNDDOWN(YEARFRAC(B2,C2,3),0))

Step 7 – Multiple by 12 and Close a Parenthesis

Multiple by 12 and close parentheses.

ROUND(YEARFRAC(B2,C2,3),0)&”years”&INT(((YEARFRAC(B2,C2,3))-ROUNDDOWN(YEARFRAC(B2,C2,3),0))*12 )

Step 8 – Enter an Ampersand and Enter “ months”

Enter an Ampersand operator and enter “Months”.

ROUND(YEARFRAC(B2,C2,3),0)&”years”&INT(((YEARFRAC(B2,C2,3))-ROUNDDOWN(YEARFRAC(B2,C2,3),0))*12 ) & ” months”

Step 9 – Press the Enter Key

Press the Enter key.

Step 10 – Use Autofill to Apply the Formulae on Each Cell

Use Autofill to apply the formulae on each cell.

Breakdown of the Formulae: