Conditional Formating planned VS actual date
If I understand what you're looking for correctly, you'll need 2
conditions on the conditional formatting on the planned date column.
For example, if the top of the planned date column is A1 and the top of
the associated actual date column is B1, you might use something like
this on cell A1:
Conditional Formatting
Condition 1:
Formula is: =AND(B1="",A1=INT(NOW()))
(and format your red highlighting)
Condition 2:
Formula is: =AND(B1="",INT(NOW())-A1<=2)
(and format your yellow highlighting)
Condition 1 will be true only if the actual date is blank and the
planned date is greater than or equal to today's date (using INT(NOW())
for today's date to filter out the time and look only at the date
today).
Condition 2 will be checked only if Condition 1 is false, and will be
true only if the actual date is blank and the planned date is 1 or 2
days before today's date (because condition 1 would be met if it's on
or after today's date).
Hope this helps!
Matt
|