traffic light status - Conditional formatting using dates.

Hi could someone please help.

I need to be able to colour a cell called status red amber or green depending on the time taken from date due to date completed.

To simplify the idea i have 4 cells in a row, A1 being the due date, B1 being the revised date, C1 being the completed date and D1 being the status cell (Green, Amber, Red, Green depending on status)

So a Due date is input into cell A1 and D1 will be green to start.(Colour cell D4 green and text GREEN)

If 7 days past the due date A1 will turn amber (Colour cell D1 yellow and text AMBER)

If past 7 days of the due date A1 then D1 turns to status red (colour cell D1 red and text RED)

If a revised date is input into cell B1 then D1 status should be reset to correspond to the conditions above again within 7 days or beyond.

When a date or the word completed is input into cell C1 then D1 should change to colour green and text green.

Can this be done with simple code using IF's and conditional formatting, any help appreciated.

Thank you in advance.

Stephen

You can use the following in cell D1's conditional formatting:

Condition 1:
Formula Is: =C1<>""
Pattern: Green

Condition 2:
Formula Is: =IF(B1<>"",TODAY()-B1>7,TODAY()-A1>7)
Pattern: Red

Condition 3:
Formula Is: =IF(B1<>"",TODAY()-B1=7,TODAY()-A1=7)
Pattern Amber

HTH

Jason

Thanks for your help,

Is their a way to get the text to appear?

Also it needs to be status green when no dates are input into Cells A1,B1,C1, and when its got todays date. Amber when its upto 7 days old and red when past 7 days old.

Then green with the word closed, when C1 has something inputted into its cell.

ANy ideas on that?

To which text are you referring?

Originally Posted by hebster

Also it needs to be status green when no dates are input into Cells A1,B1,C1, and when its got todays date. Amber when its upto 7 days old and red when past 7 days old.

Then green with the word closed, when C1 has something inputted into its cell.

ANy ideas on that?

Try these revised formulas:

Condition 1:
Formula Is: =OR(AND(A1="",B1="",C1=""),C1<>"")
Pattern: Green

Condition 2:
Formula Is: =IF(B1<>"",TODAY()-B1>7,TODAY()-A1>7)
Pattern: Red

Condition 3:
Formula Is: =IF(B1<>"",TODAY()-B1<=7,TODAY()-A1<=7)
Pattern Amber

HTH

Jason