HELP! RYB Color Change Based on Dates

Lisi

Lisi ✭

03/22/19 edited 12/09/19 in Smartsheet Basics

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?

Tags:

0 ·

Comments

Paul Newcome ✭✭✭✭✭✭

03/22/19

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

CERTIFIED SMARTSHEET PLATINUM PARTNER
10xViz.com
0 · Share on Facebook Share on Twitter
Lisi ✭

03/22/19 edited 03/22/19

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.

0 · Share on Facebook Share on Twitter
L_123 ✭✭✭✭✭✭

03/22/19

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

0 · Share on Facebook Share on Twitter
Lisi ✭

03/22/19

Hi, thank you.

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

0 · Share on Facebook Share on Twitter
L_123 ✭✭✭✭✭✭

03/22/19

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.

0 · Share on Facebook Share on Twitter
L_123 ✭✭✭✭✭✭

03/22/19

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.

0 · Share on Facebook Share on Twitter
L_123 ✭✭✭✭✭✭

03/22/19

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.

0 · Share on Facebook Share on Twitter
Paul Newcome ✭✭✭✭✭✭

03/22/19

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.

CERTIFIED SMARTSHEET PLATINUM PARTNER
10xViz.com
0 · Share on Facebook Share on Twitter

Categories

Trending in Smartsheet Basics

Record a date when a status changes from closed to open
I have a tracker where I am tracking dates when tasks are closed, but I'm running into an issue where some items are being "reopened" for additional follow-up. What I want to do is know WHEN that happens (so I can track trends or culprits LOL). Is there a way to record a date when this happens? My record a date workflow is…
Is there a way to put a display name on a hyperlink in the Form Description?
Is this possible? I see it was asked quite a few years back and a couple people added it as an enhancement, just wanted to see if it was ever implemented?
Adding a editor
Hello, I have bough PRO licence. It shows that up to 10 editors are included. But when I try to add the editors, it shows me, that there are 0 licences available. What does that mean? Are there editors included or do I already need to buy more licences? Right now only 1 user can see the sheets.

Help shape the future of Smartsheet.
Share your ideas and feature requests.