Obviously subtract different numbers of years for the different
anniversaries.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Should you not be subtracting the 7 years? Try:

=AND(C2-DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY()))<=15,C2-DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY()))=0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"blucajun" wrote in message
...
I have a spreadsheet that tracks our employee's significant anniversary
dates
(year 1, 5, 7, 10, 20, 25, etc.). I need a formula that will highlight
those
employees names and the significant anniversary for the the next 45 days
(e.g., any day that I open the spreadsheet, any employee with an upcoming
significant anniversary will have their name highlighted and the date of
the
corresponding anniversary hightlighted.) Here's how my spreadsheet is
set up:

Data begins on row 2 & Columns begin with A

A2 = Month/Day
B2 = hidden column containing unrelated data
C2 = Date of Hire
D2 = Employee Name
E2 -J2 = Hidden columns containing unrelated data
K2 = 1st year anniversary date (calculates from DOH +1)
L2 = 5th year anniversary date (calculates from DOH + 5)
M2 = 7th year anniversary date (etc.)
N2 = 10th year anniversary date (etc.)
O2 = 15th "
P2 = 20th "
Q2 = 25th "
R2 = 30th "
S2 = 35th "
T2 = 40th "

My conditional formatting formula looks like this:

=AND(TODAY()-DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))<15,TODAY()-DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))0))

and is yielding the wrong formatting. Instead of showing me that this
coming Tuesday is John Doe's 7th year anniversary by highlighting only
the
date for his 7th year (which would be next Tuesday's date), all the
anniversaries for John Doe are highlighted -including those in the past.

I found the formula on this site and modified the cells to correspond but
it's still off and I don't know why. I'm having a hard time dissecting
the
formula to see what each portion is asking for so I can't figure out how
to
fix it. Help!