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

Watch the

sie calulate length of service in excel download kese hoge

ReplyDeletesie calulate length of service in excel download kese hoge

ReplyDeleteMy formula is :

ReplyDelete=DATEDIF(O2,AL$1,"y")&" years, "&DATEDIF(O2,AL$1,"ym")&" months, "&O2-DATE(YEAR(O2),MONTH(O2),1)&" days"

Where O2 is the hire date and AL$1 is today()

The formula works, kinda. It isn't doing the math correctly. I have a person who started exactly a year ago and it is 2 weeks short...