Color formatting based on business date and project status

[Kailen64]

I have 5-10 business days to complete a task. I need to be able to quickly see what's open or pending, and I need to be able to easily maintain it. I tried looking up formulas on my own, but after editing the ones I found I kept getting errors or only part of the row was formatted. I tried asking my co-workers who are more proficient in Excel than I am but their solutions did not help.

In case anyone needs it, my document is using columns A-O. The columns that should determine what formatting is used are column D (Due Date), column E (Status), and column G (Date Completed). Here is what I want:

If the status is “Open” and the due date is 4 or more business days away from today then I want the row to be highlighted in green, 3 business days away in yellow, 1-2 business days away in red, and If the due date is either today or past due then I want the row highlighted in bright red.

If the status is “Pending – Client” then I want the row highlighted in gold, “Pending – Internal” in orange, and “Pending – External” in blue, all three regardless of the due date.

If the status is “Closed” and the “Date Completed” cell filled in then I want the row highlighted in grey, and any color formatting based on dates removed.

I no longer have the failed formulas I was working with. Any help anyone can provide is greatly appreciated!

 

[etaf]

so you need a few rules setup under conditional formatting

Select the range you want A2:??

and use the $ sign on the columns to highlight a row
assuming we start in row 2
Also the order of the rules will be important and a stop if true

column D (Due Date), column E (Status), and column G (Date Completed). Here is what I want:

If the status is “Open” and the due date is 4 or more business days away from today then I want the row to be highlighted in green,
=AND( $E2 ="OPEN", $D2 > = today()+4 )

3 business days away in yellow,

=AND( $E2 ="OPEN", $D2 = today()+3 )

1-2 business days away in red,

=AND( $E2 ="OPEN", $D2 > = today()+1 )

and If the due date is either today or past due then I want the row highlighted in bright red.
=AND( $E2 ="OPEN", $D2 < = today() )


If the status is “Pending – Client” then I want the row highlighted in gold, “Pending – Internal” in orange, and “Pending – External” in blue, all three regardless of the due date.

= $E2 = “Pending – Client”

If the status is “Closed” and the “Date Completed” cell filled in then I want the row highlighted in grey, and any color formatting based on dates removed.

=AND( $E2 = "closed" , $G2 <> "" )

and any colour formatting based on dates removed. - what does this mean ?

otherwise - can you put the spreadsheet - with dummy data - onto a share like dropbox or onedrive to link here ?

 

[Kailen64]

Thank you so much! This is finally set up how I need.

 

[etaf]

your welcome