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 -- =$F166TODAY()+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 isn't anything in the cell to begin with.

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

Thank you so much for your time!

Hi Jade,

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

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

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


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

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.

Any other ideas would be greatly appriciated!

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.as...182 2A3%21206


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

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