Conditional formatting - job completion status by colour

[newE]

I've had problems formatting in Excel. Can anyone suggest a suitable video? Or better, even outline steps to do the following:

I'm trying to flag employee task completion status by automatically showing background fill:
LESS THAN 5 days is GREEN.
MORE THAN OR EQUAL TO 5 days, but LESS THAN 10, turns YELLOW.
MORE THAN OR EQUAL TO 10 days, turns RED.

I don't need to differentiate types of day (weekends,etc), but I do need to show dates, not just codes. And I need a historical record for several months before clearing and starting fresh.

Eg, assuming today is 27 Nov 2011:
EmpA is GREEN because she completed in LESS THAN 5 days.
EmpB is RED because she completed IN MORE THAN OR EQUAL TO 10 days.
EmpA (second task) is GREEN because she still has a few days left.

A B C D
1 Employee Task Assigned Completed
2 EmpA 11e02 22 May 2011 25 May 2011
3 EmpB 11e82 26 May 2011 15 Jun 2011
4 EmpA 11e03 25 Nov 2011 pending

Any help would be much appreciated.

[darkyam]

=OR(D2="pending",D2<C2+5) for green
=D2>=C2+10 for red
and then you can have the default format yellow.

[martindwilson]

try these in order using the "use a formula option"
=OR($B1="",D1="") NO COLOUR
=IF(AND(ISNUMBER($C1),ISNUMBER($D1)),$D1-$C1,TODAY()-$C1)>=10 RED
=IF(AND(ISNUMBER($C1),ISNUMBER($D1)),$D1-$C1,TODAY()-$C1)>=5 YELLOW
=IF(AND(ISNUMBER($C1),ISNUMBER($D1)),$D1-$C1,TODAY()-$C1)>=0 GREEN

[darkyam]

Out of curiosity, why would you need the top one? You can always have a default of no color if none of the others are met. A second issue is that your formulas would not return green if the date assigned and date completed are the same because D1-C1 would equal 0. Lastly, the OP would have to be sure to check the box of Stop if True for each of these formulas after the first. (Alternatively, the formulas could be used in reverse order without those boxes checked.)

[martindwilson]

today()-blank or 0 will be >10 so default would be red
condition is d1-c1>=0 so it would
and i forgot the stop if tru bit coz im doing this in '97 and it does them in order(and i could only do 3 at a time)