How can I create a formula that will tell me how many years my employee has worked for our company from their Anniversary Date (hire date) to the current date?
How can I create a formula that will tell me how many years my employee has worked for our company from their Anniversary Date (hire date) to the current date?
If the anniversary date was in cell "A1":
=YEAR(TODAY())-YEAR(A1)
Mike, thanks. Did not work for me. I put this inOriginally Posted by MikeO
=YEAR(TODAY())-YEAR(D19) and it gave me back a date rather than a number representing years.
=DATEDIF(D19,TODAY(),"Y")
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
PUT THIS IN
=DATEIF(D19,TODAY(),"Y")
ITgave me an error sign
They both work for me......
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
The equation I gave you works for me. I think you just need to format the cell with the equation to be a number.
Wonderful, Mike. You were right. My cells were formated for DATE. As soon as I formated them for NUMBER it worked.
THANKS MUCH.
raziel, If you got the solution for your question please mark your thread solved using the thread tools at the top of the page.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Hmmm if I use your example Steve, and place the date 12/5/1987 in A2 as indicated..... I get two different results. MikeO gives me 21 whilst Bob's give me 20.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
That is because Mike's simply takes the lower year from the higher, so 31/12/2007 and 01/01/2008 are years 2007 and 2008 which is 1. If you use DateDif, it is calculating the number of years, and (correctly IMO) concludes here that 1 day is not a year, even if those days are in different years. That is why I offered DateDif
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks, much, XLD.
Good catch Ted.
This was informative......thanks to all.....especially Bob.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Posting Permissions
Reply With Quote
