Conditional formatting based on dates in other columns

0

Hello

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.

Simon

Answer
Discuss

Answers

0

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.

Discuss

Answer the Question

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