HELP! RYB Color Change Based on Dates

Lisi

Hello everyone,

 

I am currently using this:

=IF(NOT(ISBLANK(Completion4)), IF([Estimated End]4 >= Completion4, "Green", IF(NOT(ISBLANK(Completion4)), IF([Estimated End]4 < Completion4, "Red"))))

Which is working but I try to add this:

IF(ISBLANK(Completion3), IF(NOT(ISBLANK(Supervisor4)), "Yellow"

And it doesn't work, I'm not sure what I am doing wrong. Any ideas?

 

 

Paul Newcome

How are you trying to add it, what is the issue/error once it is added, and what is the desired outcome?

Lisi

Hi Paul,

The first two parts for the green and red work fine but nothing happens when it should turn yellow. I don't get any errors. When I try the whole statement together when it should be turning yellow it doesn't. I'm not sure why because if I use that statement alone it works. 

L_123

you are referencing row 3 instead of 4 with the yellow statement which might be contributing to your issue.

=IF(NOT(ISBLANK(Completion4)), IF([Estimated End]4 >= Completion4, "Green", IF(NOT(ISBLANK(Completion4)), IF([Estimated End]4 < Completion4, "Red",IF(ISBLANK(Completion4), IF(NOT(ISBLANK(Supervisor4)), "Yellow"

the above is what I believe you are trying to use. you need to look at the heirarchy of your statements. You never give a false criteria, that is to say you never tell the formula what to do when the opposite of your criteria

 

Lisi

Hi, thank you.

But no that's not it, I changed that already and it's still not working. It's pretty strange.

L_123

1. =IF(NOT(ISBLANK(Completion4)),

Return if above statement is true

IF([Estimated End]4 >= Completion4, "Green", IF(NOT(ISBLANK(Completion4)), IF([Estimated End]4 < Completion4, "Red",IF(ISBLANK(Completion4), IF(NOT(ISBLANK(Supervisor4)), "Yellow"

Return if above statement is false

""

 

2. IF([Estimated End]4 >= Completion4

Return if above statement is true

"Green"

Return if above statement is false

IF(NOT(ISBLANK(Completion4)), IF([Estimated End]4 < Completion4, "Red",IF(ISBLANK(Completion4), IF(NOT(ISBLANK(Supervisor4)), "Yellow"

 

3. IF(NOT(ISBLANK(Completion4))

Return if above statement is true

IF([Estimated End]4 < Completion4, "Red",IF(ISBLANK(Completion4), IF(NOT(ISBLANK(Supervisor4)), "Yellow"

Return if above statement is false

""

 

4. IF([Estimated End]4 < Completion4

Return if above statement is true

"Red"

Return if above statement is false

IF(ISBLANK(Completion4), IF(NOT(ISBLANK(Supervisor4)), "Yellow"

 

5. IF(ISBLANK(Completion4)

Return if above statement is true

IF(NOT(ISBLANK(Supervisor4)), "Yellow"

Return if above statement is false

""

 

6. IF(NOT(ISBLANK(Supervisor4)), "Yellow"

Return if above statement is true

"Yellow"

Return if above statement is false

""

 

 

You have 4 if statements that don't return anything if false. Your formula is linear and does not effectively utilize both criteria of the statements. If statements are composed of 3 parts (Criteria, true, false) and all need to be considered when stacking them. I'll clean up the formula and post it in a response to this so you can see what I am talking about.

L_123

Original

=IF(NOT(ISBLANK(Completion4)), IF([Estimated End]4 >= Completion4, "Green", IF(NOT(ISBLANK(Completion4)), IF([Estimated End]4 < Completion4, "Red",IF(ISBLANK(Completion4), IF(NOT(ISBLANK(Supervisor4)), "Yellow"

1. if C is not blank

2. if EE is greater than or equal to C then "green"

else

3. if C is not blank then if EE is less than C "Red"

else

4. if C is blank then if S is not blank then "Yellow"

Every single statement is reliant on the criteria of the previous, so there are several redundant statements in this. An example would be line 3, we already checked if C is blank in line 1, why check again? Also we already checked if EE is greater than or equal to in line 2, the only other option is less than, so we don't need to check it again in line 3.

For line 4 we introduce a new column (supervisor) but we never tell the program what to do if S is blank, only what to do when the is a value in the cell.

 

L_123

What you can do instead is something like:

 

=if(not(isblank(Completion4)),if([Estimated End]4 >= Completion4,"Green","Red"),if(isblank(Supervisor4),"","Yellow"

 

I recommend pasting your solution into notepad and breaking it down into parts to be able to focus on a single part at a time. Trying to understand something like this in a single piece is very difficult, when you break it into individual parts it becomes much easier.

 

Paul Newcome

I also find it helps to list out each scenario (requirement/outcome).

.

If this equals that, then I want it to do this.

If this equals something else, then I want it to do something else.

.

List EVERYTHING out. Don't worry about listing things multiple times. Don't worry about having 5 different things equaling the same thing. Just list it all out.

 

Then organize your list.

 

Then write your statements.

 

Then prioritize your outcomes.

 

THEN you can combine your statements for a finalized formula.