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?
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives