Formulating Date cells to auto color code based on Date

I deal with a spreadsheet on a daily basis that is used as a suspense tracker. I need to formulate the cells in one of the columns to either color fill or icon change based on the suspense date. Example: Today is 23-Apr-13, I have 5 taskings due 23-Apr, 24-Apr, 25-Apr, 28-Apr and 30-Apr. I would need the 23-Apr cell Red (since due today), 24-Apr cell Yellow ("caution" due tomorrow) and the 25-30 Apr cells Green (no stress, plenty of time).

I have played around with it now for 2 weeks, and got close but no cigar. My Chiefs actually want me to use the Red, Yellow, Green stop light ICONs but I am still crawling with the regular cell fills..lol. Anyone that can help, greatly appreciated. Have uploaded an example. This has had me pulling my hair out for days.....

Darren

etaf,

I looked at the sheet you posted. It seems to be mostly what I was looking for. I came up with close to the same, using =(A2-TODAY()), but couldn't get them all to work. Like past dates, they are clear, but need to be red as well. To elaborate on the ICONs. on the drop down for conditional formatting, there is an option for ICON sets. Hovering over ICON sets, a drop down menu comes up. At the bottom, an option for "more rules". If you select more rules, a window opens where you can select an ICON style, then the "rules" options at the bottom. This ICON set is how they want me to format it, but I am hit a brick wall.

Attempting to formulate the ICONs based on a "formula" Type, I can't figure out what formula to put in the "value" box with the <>= functions. I am still playing with it and will I suppose til I figure it out, but no luck so far.

Darren

so to get the red for previous dates - change
CELL = today() - then red- what if date older than today
to
CELL <= today()

i'll look in to the icons - never used before

Uploaded file for preview of ICON set option.

etaf, thanks a bunch for the help with the formulas for filling. The change worked so far as I can see. Greatly Appreciated. Now I will keep trucking away at the ICON part.

i have added an extra column with the traffic lights in

in that column I have used an IF statement to select a value

=IF(B2<=TODAY(),1,IF(B2=TODAY()+1,0.75,0.5))

so we get
0.1 for today or before today
0.75 for tomorrow
and 0.5 for day after tomorrow and beyond

now we can apply the icon traffic lights to those numbers and also state - show icon only (so the results from the formula goes away )

and you have the traffic lights - albeit in a different column

see attached