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

SUmmary in Report Widget
How can i create a Report widget that only shows a status with "red status" from my main data source? Do i need to create a separate data source on just my "red status"
Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…