PDA

View Full Version : Solved: ANNIVERSARY YEARS



razielinski
03-28-2008, 10:59 AM
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?

MikeO
03-28-2008, 11:08 AM
If the anniversary date was in cell "A1":

=YEAR(TODAY())-YEAR(A1)

razielinski
03-28-2008, 11:22 AM
If the anniversary date was in cell "A1":

=YEAR(TODAY())-YEAR(A1)

Mike, thanks. Did not work for me. I put this in
=YEAR(TODAY())-YEAR(D19) and it gave me back a date rather than a number representing years.

Bob Phillips
03-28-2008, 11:30 AM
=DATEDIF(D19,TODAY(),"Y")

razielinski
03-28-2008, 11:43 AM
=DATEDIF(D19,TODAY(),"Y")

PUT THIS IN
=DATEIF(D19,TODAY(),"Y")

ITgave me an error sign

lucas
03-28-2008, 12:07 PM
They both work for me......

MikeO
03-28-2008, 12:08 PM
The equation I gave you works for me. I think you just need to format the cell with the equation to be a number.

razielinski
03-28-2008, 12:53 PM
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.

lucas
03-28-2008, 01:27 PM
raziel, If you got the solution for your question please mark your thread solved using the thread tools at the top of the page.

Aussiebear
03-28-2008, 04:02 PM
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.

Bob Phillips
03-28-2008, 04:15 PM
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

razielinski
03-28-2008, 04:26 PM
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

Thanks, much, XLD.

lucas
03-28-2008, 04:56 PM
Good catch Ted.
This was informative......thanks to all.....especially Bob.