I am using Excel 2007...and I have an excel spreadsheet with the hire dates in the format of dd/mm/yy under column C2.. when an employee hits the following anniversaries: 1 year, 5 year, 10 year, 15 year, 20 year, etc. I would like the field to be highlighted so it brings it to my attention and I can recognize them. Is there a way to do this?  I would appreciate any help you may provide.  Thank you for your time.

---

Answer from Richard Rost:

Here's a SIMPLE way that won't require any programming.

Lets say you have the date in column A. Make column B for your 1-year anniversary:

=IF(AND(MONTH(A1)=MONTH(TODAY()),DAY(A1)=DAY(TODAY()),YEAR(A1)=YEAR(TODAY())-1),TRUE,FALSE)

This compares the month, day, and year and if TODAY is the same month and day, but one year less (the -1) then put a TRUE here, otherwise a FALSE.

Now, you can substitute any values you want (even leave false BLANK with a "") and then just use some outrageous colors via CONDITIONAL FORMATING to make the true values stand out.

You can easily copy this formula for years 5, 10, 20, etc.

I hope this helps. It's the easiest non-programming solution I could think of. Anything else would involve a custom VBA function.