Conditional Formatting - Due Dates

[pcexgokou]

Hello everyone, my boss told me to make a control board which determines the due dates.
If the it is 5 days before due date the text color will be green.
If 3 days before the text color will be yellow.
If it is in due date or overdue, the text color will be red.

Thanks in advance

[etaf]

=E3>=TODAY()+5 = red

=E3>=TODAY()+3 = Yellow

=E3<=TODAY() = red

If the it is 5 days before due date the text color will be green.
If 3 days before the text color will be yellow.
If it is in due date or overdue, the text color will be red.

What to do if between today and 3 days
I may have misunderstood your requirement


make up three rules
and put them in order
RED, YELLOW , GREEN
and stop if true

see attached

for 2007 or 2010 excel version
Conditional Formatting

Highlight applicable range >>

E3 to E???


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


apply one of the formulas above


Format… [Number, Font, Border, Fill]
OK >> OK

[ianxwood]

Hi

I have interpreted your requirement slightly differently than etaf

1 question, what happens if due date is 4 days in the future? I have assumed this to be green

if due date 4 to 5 days and only 4 to 5 days in the future is green, see Font tab attached

Rule : = and(today() - due date >= -4, today() -due date >= -5) Font/Fill green

OR if due date 4 or more days in the future is green, see Fill tab attached

Rule : = today() - due date <= -4 Font/Fill green

if due date is 1 to 3 days in the future then yellow

Rule : = and(today()-due date < 0, today() - due date >= -3) Font/Fill yellow

if due date is today or in the past then red

Rule : = due date <= today() Font/Fill red

Apply rules in the order they appear above

I usually find it helpful to work out conditional format rules as a worksheet IF (logical, value true, value false) function first.



Regards
Ian