Row Condition Based on Dates In Three Columns

Ken0356

New Member
Joined
Sep 30, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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)
1605732846724.png

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)
1605732912945.png

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)
1605733000912.png


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.
1605733449530.png

Thanks in advance for the help.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

ekrause

New Member
Joined
Aug 7, 2019
Messages
49
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

1605742573651.png


Spreadsheet Reference for Conditional Formats

1605742904794.png
 
Solution

dnorm

Board Regular
Joined
Dec 28, 2017
Messages
96
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
@dnorm
You cannot use relative referencing in the applies to range. If you try it will automatically be converted to absolute.
 

Ken0356

New Member
Joined
Sep 30, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
Get rid of the rule for blank cells. Doing that is not a good idea IMO.
Then change the other rules along these lines
Excel Formula:
=AND(I2="",G2<>"",H2<>"",H2<=TODAY()+5)
 

Ken0356

New Member
Joined
Sep 30, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,584
Messages
5,625,644
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top