Anniversary date recognition

QuestionEdit

I have a worksheet and in the column C2 I have employee hire dates in the format of 1/1/2010 (for example). I am looking for a formula that would recognize that row based on the hire dates the upcoming 5, 10, 15, 20, 25 year anniversary and highlight the date. Im using2007 and pretty new at all this...any help would be greatly appreciated.

AnswerEdit

Because you're new, I think I would set it up this way. (It could be all in one formula, but it gets more difficult to follow).

Set up a couple cells that have the beginning and end of this calendar year.

in cell X1, enter 1/1/2010 in cell Y1, enter 12/31/2010

In cell D2, create an IF-THEN formula that recognizes if the hire date plus 5 years happens this calendar year or not.

=IF(c2+(5*365)>X1,IF(c2+(5*365)<Y1,"5 YR","NO major anniversary this year")

Set up a similar formula in E2 for 10 years, etc.

-Craig