Row Condition Based on Dates In Three Columns

[Ken0356]

Hello,
I've gone through some of the previously posted threads but didn't see one to match closely to what I'm looking for in a condition(s).
I have a table (A11:X160) that I want each row to react based on the follow condition types. (Note this table is a "Question Tracking Log" and each row represents a question report that is automatically feed from other worksheet. "reports")
Here are the headers to the three inspected columns. Column G is "Date Submitted To Customer" / Column H is "Response Deadline" / Column I is "Date Answered"
Condition #1
I want the specific table row to turn Blue when a date is in Column G(not blank) and todays date is 5 days or less than the date in Column H and Column I is empty.(In Ex. Today=11/10-11/17)

Condition #2
I want the specific table row to turn Orange when a date is in Column G(not blank) and todays date is within 2 days of Column H and Column I is empty. (In Ex. Today=11/18-11/19)

Condition #3
I want the specific table row to turn Red when a date is in Column G(not blank) and todays date on or past Column H and Column I is empty. (In Ex. Today=11/20and past)

In all of these conditions if there a client response in Column I then these conditions must be ignored. Basically if answered no reason to monitor the rows status any longer.

Thanks in advance for the help.

 

[ekrause]

You'll want to set up conditional formats and each will need their own rule.

The formula for Blue will be - =$D2-TODAY()<=5
Orange - =$D2-TODAY()<=2
Red - =$D2-TODAY()<=0
Blank - =$E2<>""

And you will need to prioritize them as shown in the picture below. Two points for you to consider:

1. the white/blank rule will change if ANY info is put into that column, that could be date past your deadline and accidental user errors (like adding a space by accident).
2. Anything longer than 5 days out will not get a highlight. It will look like its been completed from the blank/white formula. You can change that by just changing the color in the spreadsheet or adding another Conditional Format that colors anything above 5. The formula would be =$D2-TODAY()>5

Spreadsheet Reference for Conditional Formats

 

[dnorm]

Hi Ken
ekrause beat me to it (although I use IF formulas and stop if true).

To expand what they said, (and I am guessing row 11 is your table headers), apply this to H12 firstly (just to see if it is working for you) than after in the "Applies to" type =$A12:$X160 this way if you add more rows to your table the formatting should continue down (Unlike if it reads =$A$12:$X$160) the $ sign locks that direct to that target (i.e. $A12 is locked to column A but not row 12, A$12 is locked to row 12 but not column A and $A$12 is locked to that cell)

 

[Fluff]

@dnorm
You cannot use relative referencing in the applies to range. If you try it will automatically be converted to absolute.

 

[Ken0356]

This appears to work great except when my log is new yet and no row data exists for the 200 line long log(LLL wow). I need the table to remain as normal. Currently my table has a gray tint every other line for easy reading.

 

[Fluff]

Get rid of the rule for blank cells. Doing that is not a good idea IMO.
Then change the other rules along these lines

=AND(I2="",G2<>"",H2<>"",H2<=TODAY()+5)

 

[Ken0356]

Apologies had a PICNIC problem there for a second. I made a couple of minor tweaks to the condition formulas and now have it working properly. Thanks for the help

 

[Fluff]

Glad we could help & thanks for the feedback.