Conditional Formatting equation Help

[diana.britton]

Hi Excel experts, I could use your help in creating a conditional format formula for project management purposes. Below is a list of the rules in order that I have created. I am stuck getting #4 to do what I want it to do. Trying to get #4 to turn red and show "Past Due" text in cell G2 if today's date is greater than E2 and if G2 cell is blank. Goal here is to identify when task due dates have past to make sure we stay on track. Any thoughts?

Current Formulas:

1. =ISBLANK(G2)  - fill white

2. =AND(G2>E2) - fill red

3. =AND(G2<=E2) - fill green

4. =IF(AND($I$1>E2,G2=ISBLANK), "Past Due") - fill red

[#2]

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

[#3]

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?

[#4 · Accepted solution]

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.

[#5]

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.

[#6]

Hi Diana,

You are welcome. If any other questions don't hesitate to ask.