Conditional Formatting Function for Anniversary Dates

[blucajun]

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!

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[blucajun]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Well I don't know where my reply giving an explanation went to because it is
in my Sent box but I don't see ity in the NG - damned OE.

Of course it would have been much better if I had written the formula the
other way around and *added* 7 years to the Hire date because it uses fewer
function calls:

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

Explantion, (of the above formula)
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, (year 1, 5, 7, 10, 20, 25, etc.), but you want it to match only the
7th anniversary year. What I have done is to add the number of years to the
Hire date - in this case 7 - and compare it to the today's date so that only
one formula would return TRUE.

In other words in my formula:
DATE(YEAR(C2)+7,MONTH(C2),DAY(C2))
returns the date of the anniversary of the *Hire year* seven years on then
today's date is subtracted from this date to give the number of days until
the hire date. If the Hire date was 26 June 2001 then any date (that my
formula calculates) from 12 June 2008 to 26 June 2008 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
unles it is the fact that in your formula you are referencing C3 noit C2
although you quote C2 as being the Hire date. 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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk