Well the date in your formula:
DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))
returns the date of the anniversary *this year* so it will match *every*
year but you want it to match only the 7th anniversary year. What I did
therefore was to subtract the number of years on the anniversary - in this
case 7 - and compare it to the Hire date so that only one formula would
return TRUE.

In other words in my formula:
DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY()))
returns the date of this day in the *Hire year* and this is subtracted form
the Hire date. If the Hire date was 26 June 2001 then any date (that my
formula calculates) from 12 June 2001 to 26 June 2001 will be within the 14
days that you want the highlighting to occur.

As to why it does not work for you I cannot say because it works for me. If
the above does not help then send me an email by changing the address in my
signature as it says to replace the part after the @ so that I can get your
address, (DON'T post it here or you will be targeted by the Spam bots) and I
will send you a demo spreadsheet.


--
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
...
Now I'm really confused. I'm no beginner for Excel, but I just don't
understand what the formula is doing and why I should be subtracting.
Anyway, I put the formula you gave into the conditional formatting field,
but
it didn't produce any of my formatting for those upcoming anniversaries.

I'm still lost on the subtracting 7 part, but I'm not a math wiz so...
Actually, this formula is making me feel really dumb. Ugh.



"Sandy Mann" wrote:

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!