Anniversary Date Recognition Formula Help?

**onflight1978** · 04-08-2010, 01:07 PM

I have a worksheet with several start dates in date format...mm/dd/yy. I want to find the upcoming key anniversary dates (5 years, 10 years, 15 years, 20 years, 25 years, 30 years, 35 years, 40 years, 45 years) of all employees so recognition can be given. I would like the column/field to be highlighted to show me the employees who have reached the 5, 10, 15, 20, 25 year mark etc.. I am using Excel 2007..any experts out there who could help me out with a formula?

**NBVC** · 04-08-2010, 01:15 PM

Select the range of names and or dates...

Then go to Home|Conditional Formatting and select New Rule

Select use formula to determine which cells to format

then enter formula something like:

=MOD(YEAR(TODAY())-YEAR($B2),5)=0

where B2 is top cell in the date range corresponding to your top cell in your selection.

Click Format and choose from the Pattern tab.

This will highlight any name/date that has been around for a number of years divisible by 5.

**estige** · 04-08-2010, 02:00 PM

Hi, here's another suggestion, also using conditional formatting.
Dates in the past are in blue, upcumming dates (choose how far into the future) are in yellow, and dates further into the future are grey.