Excel Conditional Formatting based on dates in multiple cells

1
votes

This spreadsheet I am working on tracks trainings. I'm having issues with this because one training counts for itself and fills the requirement for the other. I am trying to apply conditional formatting on column G that turns the cell red if the dates in;

  • Column F and G are not within 3 years
  • Column G are blank
  • Column F = 0 and Column G are not within 3 years

Here is my best effort at the formula

=OR(AND(TODAY()-$F1>1095,TODAY()-$G1>1095),$G1=0,AND($F1=0,TODAY()-$G1>1095))
2
excelexcel-formulaconditional-formatting

2 Answers

0
votes

Use:

=OR($G1="",AND(OR($F1=0,$F1<EDATE(TODAY(),-36)),$G1<EDATE(TODAY(),-36)))
0
votes

Let A be the condition that column G is not within 3 years. Let B be that column F is not within 3 years. Let C be that G is blank. Let D be that F is zero. Let's explain using boolean algebra:

  • The first condition with AND, BA translates to AND(TODAY()-$F1>1095,TODAY()-$G1>1095). [Both column F and G not within 3 years]. You are correct.

  • The second condition C translates to $G1="", so you are correct.

  • The third condition with AND DA translates to AND($F1=0,TODAY()-$G1>1095). You are correct.

Here is the catch, both the first and third condition contain subcondition A, combine the first and third conditions to make

AND(Today()-$G1>1095, OR(Today()-$F1 > 1095, $F1 = 0)) = A(B+D)

We now have the slightly shorter

  • OR($G1="", AND(Today()-$G1>1095, OR(Today()-$F1 > 1095, $F1 = 0))) = C+A(B+D)

But both expressions are valid. Your expression is AB + C + AD.

Here are some sample results, letting TODAY() = 7/23/2019 The TRUE indicate that the values on G shold be red. The TRUE/FALSE on the left is your original one, and the TRUE/FALSE on the right is my revision.

sampletable