Date Conditional Formatting

[Jade Summers]

I seem to be having quite the conundrum and after hours of searchin
cannot find the answer. Hoping someone with some more experience ca
help me.

Right now I have a sheet with dates in column "E". I am trying to mak
it so that when a date is entered into that column it turns green if i
is 181+ days from todays date, yellow if it is from today until 180 day
and this is where the issue exists, red when the date is expired.

Problem is I am using Excel 2003 and can only use three conditiona
formatting rules so they are as follows:

Green --> =$F166>TODAY()+181
Red --> =$F166<TODAY()
Yellow --> =TODAY()-$F166<180

I am trying to make it so the date does not change to 0/Jan/1900 an
turn red if there isn't anything in the cell to begin with.

I know I am most likely missing something obvious, but I can't figur
out what it is.

Thank you so much for your time

 

[Claus Busch]

Hi Jade,

Am Wed, 13 Jun 2012 15:11:39 +0000 schrieb Jade Summers:

Green --> =$F166>TODAY()+181
Red --> =$F166<TODAY()
Yellow --> =TODAY()-$F166<180

for the red color use:
=AND(F166>0,F166<TODAY())


Regards
Claus Busch

 

[Jade Summers]

Hi Jade,

Am Wed, 13 Jun 2012 15:11:39 +0000 schrieb Jade Summers:
-

for the red color use:
=AND(F166>0,F166<TODAY())


Regards
Claus Busch

Thank you so much for your quick reply! Unfortunatly that isn't workin
for me. It's just turns all cells that were red (both dates and blank
no colour. However this does seem to fix the issue of puttin
0/Jan/1900.

Any other ideas would be greatly appriciated

 

[Claus Busch]

Hallo Jade,

Am Fri, 15 Jun 2012 12:12:46 +0000 schrieb Jade Summers:

Thank you so much for your quick reply! Unfortunatly that isn't working
for me. It's just turns all cells that were red (both dates and blank)
no colour. However this does seem to fix the issue of putting
0/Jan/1900.

for me it works fine. Take a look at the range F166:F1100:
https://skydrive.live.com/#!/view.aspx?cid=9378AAB6121822A3&resid=9378AAB6121822A3!206


Regards
Claus Busch

 

[Jade Summers]

Thank you so much for your quick reply! Unfortunatly that isn't workin
for me. It's just turns all cells that were red (both dates and blank
no colour. However this does seem to fix the issue of puttin
0/Jan/1900.

Any other ideas would be greatly appriciated!

For some reason it wasn't working without the $ in front of the F. Why
have no idea, but I am thrilled it's working. Thank you so much for you
help you saved my sanity