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...
i need to do from the hire date hrough this year, not todays date. what is that formula?
ReplyDeleteI have the same question! All I'm finding is "today" and "months" formulas. Just need formula to calculate YOS from hire date only... 01/11/1988 to the year 2019. No "start date" and "end date".
DeleteCell b7 holds the start of service date
ReplyDeleteCell b5 holds the end of service date
If you want to include both the start date and the end date (to show the actual number of days of service, including first and last day of service):
=DATEDIF(B7-1,B5,"y")&" years, "&DATEDIF(B7-1,B5,"ym")&" months, "&DATEDIF(B7-1,B5,"md")&" days"
If you do not want to include the start date (or the end date):
=DATEDIF(B7,B5,"y")&" years, "&DATEDIF(B7,B5,"ym")&" months, "&DATEDIF(B7,B5,"md")&" days"
I was just browsing for related blog posts for my project research and I happened to discover yours. Check out my blog if you want to hire virtual assistant
ReplyDelete