Excel - Date Conditional Formatting

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

Right now I have a sheet with dates in column "E". I am trying to make
it so that when a date is entered into that column it turns green if it
is 181+ days from todays date, yellow if it is from today until 180 days
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 conditional
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 and
turn red if there is not anything in the cell to begin with.

I know I am most likely missing something obvious, but I cannot figure
out what it is.

Thank you so much for your time!




--
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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus Busch;1602694 Wrote:

Thank you so much for your quick reply! Unfortunatly that is not working
for me. it is 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.

Any other ideas would be greatly appriciated!




--
Jade Summers

Hallo Jade,

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


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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Jade Summers;1602761 Wrote:

For some reason it was not working without the $ in front of the F. Why I
have no idea, but I am thrilled it is working. Thank you so much for your
help you saved my sanity!




--
Jade Summers