HELP! RYB Color Change Based on Dates

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?

Find more posts tagged with

Sheets

Healthcare

IT & Operations

Conditional Formatting

Comments

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

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.

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

Hi, thank you.

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

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.

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.

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.

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.

Sign in to join the conversation!

Quick Links

Trending discussions

I am an Admin, but I cannot edit the workspace
I am already an Admin of a workspace, but when I enter the sheet to edit, it prompts me that I am a viewer. Has anyone encountered this situation? In the share tab, it shows that I am both an Admin and a viewer. How could a product from such a large company have such a BUG?😫
How to recover data after I ran a new workflow and lost my data
The black arrow indicates when (around 4:30pm) the mistake was made. I would like to recover a previous version say around 2pm.
Why is % Complete not the same between Smartsheet and Microsoft Projects
I have two identical projects with the exact same data, but the % Complete in Smartsheet is 14% and the % Complete in Microsoft Projects is 4%. Which one is correct