Hello
I have a list of start dates for employees and I would like to put a formula in that calculates how many years they have worked for the company from this date. I would have thought this was simple to do but from an online search I can't seem to find a clear answer.
Thanks in advance.
use DATEDIF(startdate,enddate,"Y")
datedif isnt like other normal formulas, excel wont help you try fill in the arguments
here is some reading on it
https://exceljet.net/excel-functions...tedif-function
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thanks for replying
I don't seem to have that formula when I press =? I only have DATE and DATEVALUE?
I have attached an example of what I have done so far. What I am trying to do is summarise the data before putting it into a table but as you can see I'm having a bit of trouble.
When an employee has worked less than a year its not sure what range to put it in. How can I get round this? I think I may need to put some dates in months?
Try this:
in B3: =DATEDIF(A3,TODAY(),"Y")
in C3: =LOOKUP(B3,{0,1,2,3,5},{"< 1 Year","1-2 Years","2-3 Years","3-5 Years","> 5 Years"})
How do I get the 'DATEDIF'?
When I press = I only have two options, DATE and DATEVALUE?
The DATEDIF formula is a bit of a special story.. it is officially not supported in excel and therefor not in the list of formula's (I believe there were lawsuits against MS for copyright infringements)
it is however available in the background but you will have to know (or find on internet) how to use it as the normal help to setup the formula is not available
It is the right formula to use however..
I also put a small lookup table for the year ranges that is easier then nested if formula's
Edit: I see humdingaling in post #2 also posted a link, that link has a clear list of available options, make sure to check that is you also want months or days sincs start
Last edited by Roel Jongman; 04-26-2018 at 04:16 AM.
How do I get it from the 'background'? Seems silly that it doesn't show up!
just type the formula manualy, all that is missing is the normal formula wizard guiding you thru what to put where. but you were given 2 links that explain in detail how the formula should be setup and the working formula is also in your example workbook
understanding how it works is now up to you..
Like i said it is a bit of a special story behind this formula.. (legal stuff) so it is just a little more difficult to use if you are used to using the formula wizard.
just read thru one of the links provided (I think humdingaling in post #2 is easier to understand) and see all possibilities of the DATEDIF formula and to see which date goes where in the formula
Date-Dif, This function doesn't exist as other function but mystically it works. You will have to put =DATEDIF(STARTDATE, ENDDATE,Option)Originally Posted by Rumpus24
An option can be as below.
"Y" Difference in complete years
"M" Difference in complete months
"D" Difference in days
"MD" Difference in days, ignoring months and years
"YM" Difference in months, ignoring days and years
"YD" Difference in days, ignoring years
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks