If you are using Excel to store data about employees in a company, you may at some point need to calculate their length of service. Let’s say we want to return how many years and months an employee has worked for our company.
The DATEDIF function in Excel is used to calculate the difference between two dates. The difference can be returned as the number of years, months or days.
As we wish to return the number of years and months that they have worked we will concatenate two DATEDIF functions together.
Calculate Length of Service Formula
Using the data in the spreadsheet below we can calculate the length of service for each employee in column E by using the following formula.
=DATEDIF(C2,D2,”y”)&” years, “ & DATEDIF(C2,D2,”ym”)&”months”
It is important that columns C and D are formatted as dates. If you receive an error for your formula this is a good place to check first for mistakes.
If you do not have an end date yet for the employee, the TODAY() function can be inserted into the formula in place of D2. This will calculate the length of service up to the current date.
The DATEDIF Function
The DATEDIF function is not documented in Excel (for some strange reason) so when you type it into a cell no information is displayed.
Its syntax however is;
=DATEDIF(Start Date, End Date, Interval)
In our formula we wrote one that returned the number of year served, another for the number of months remaining after years is calculated and joined them together.
The & is also used to concatenate, or join, the years and months text into our answer.
Watch the Video