Conditional Formatting equation Help

Hi Diana,

Step by step. Formula for Past Due. In any formula it shall be only one "=" at very beginning if only you have no logical expression inside which compares two values, like

=A1 + (ISBLANK(B1)=TRUE)

is correct, and

=A1 + =ISBLANK(B1)

is not correct

In your case formula for the cells in column H will be

=IF(($I$1>E2)*ISBLANK(G2), "Past Due","")

or exactly the same

=IF(AND($I$1>E2),ISBLANK(G2)), "Past Due","")

taking into account what TRUE is equivalent of 1 and false is equivalent of 0 in arithmetic operations.

In conditional formatting the rule formula shall return TRUE of FALSE. For the red color that could be

=(ISBLANK($G2)*($I$1>$E2)+($G2>$E2))*($E2<>"")

Here

=ISBLANK($G2)*($I$1>$E2)

returns TRUE (1) if both conditions are met and FALSE (0) otherwise. Adding

($G2>$E2)

we have 1 or 2 (which is TRUE) if at least one condition is met in

=ISBLANK($G2)*($I$1>$E2)+($G2>$E2)

otherwise zero (FALSE). That is equivalent of OR condition.

Finally we multiply on (AND condition) ($E2<>""), which is equivalent of ISBLANK ($E2), not to apply color to the cells in rows where dates in E are empty.

For the green color the rule could be

=($G2<=$E2)*($G2<>"")

i.e. for the cells where cells in G are not blank and dates are less than ones in column E (blank cell is always less than any date in E).

Finally we apply the rules to the range

=$H$2:$H$80

to have some gap for future records.

Alternatively you may apply rules for the range like

=$E$2:$H$80

to color not only cells in column H, but your records from column E to H.

Please find the sample attached.