Forum Discussion
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.
Hi Diana,
Few comments
- In your 2. and 3. AND does nothing, simple
=G2>E2
is enough;
- not necessary to have two rules for the same colour. One colour - one rule (combine 2 and 4);
- the rule shall return TRUE or FALSE. Depends on that formatting is applied or not;
- moreover, you can't take the value from the cell (G2), even if it's blank, and return another value into the same cell. Use another cell for Past Due;
- G2 = ISBLANK is incorrect. =ISBLANK(G2);
- combining 2. and 4. the rule for red could be
=ISBLANK(G2)*($I$1>E2)+(G2>E2)
- not clear to which range you apply your conditional formatting, thus not clear if absolute and relative references are used correctly
Hi Sergei,
Thank you for your suggestions. I have made the corrections and combined 2/4. I am still struggling with the Past Due conditional Formula in the adjacent cell H2.
Formula tried: =IF((=ISBLANK(G2))+($I$1>E2),"Past Due")
Error
This is a snap shot of where the data is being entered.
data
I have tried =IF(($G$2=ISBLANK($G$2))+($I$1>$E$2),"Past Due") as well but it shows me past due if pasted directly in the cell. It does nothing if applied as a conditional format. Do you know what I am doing wrong?
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.
Hi Sergei,
Thank you so much! It worked and I now have a way to stay on top of tasks and make sure we don't miss anything. Truly appreciate your through response! I can create some basic conditional formatting formulas but anything beyond that is a bit harder.
