Conditional formatting based on dates in other columns



I am trying to set up conditional formatting rules for the following scenario:

I have 3 date-formatted columns: Date A, Date B and Submission Date in columns A,B and C.

I want to format cells in column C according to the following rules:

  • Date B is less than 8 days AFTER Submission date - FILL RED
  • Date B is between 8 and 14 days AFTER Submission date - FILL AMBER
  • Date B is more than 14 days after Submission date - FILL GREEN
  • If Date B is blank apply the above rules to Date A

I have been able to come up with rules that work for the first three steps but have got stuck with applying to Date A if Date B is blank. Any help wpuld be greatly appreciated.





For this purpose you might use the "Stop if True" checkbox.

  1. =IF(AND (B<>"", B<([SubDate]-8))
  2. Stop if True
  3. =IF(AND (B="", A<([SubDate]-8))

Looking in the Rules Manager, rules are implemented in the sequence in which they are listed. Accordingly, you should list all the B rules first, each marked "Stop if True", and followed by the A rules.

The alternative would be a single rule like this.

=IF(B = "", A<([SubDate]-8), B<([SubDate]-8))

The sequence in which the rules are applied remains important in either case.


Answer the Question

You must create an account to use the forum. Create an Account or Login