Trouble with a four color symbols formula
Hello everyone,
If am trying to indicate project color status based on <TODAY() and TODAY(+x) for green, yellow, red and blue symbols. This is my formula-=IF(OR([Requested Go Live Date/Discontinue Service Date]@row]<=TODAY(+5) "Red", (OR([Requested Go Live Date/Discontinue Service Date]@row<=TODAY(+9) "Yellow",(OR([Requested Go Live Date/Discontinue Service Date]@row<=TODAY(+14))))) "Green", "Blue")
I can get the Green and Red working, but not the Yellow or Blue, so obviously something is wrong with my formula. Any thoughts?
I also have 2 Status and 2 Due Date columns based on the need for a due date faster than the standard turnaround time; "Requested Go Live Date/Discontinue Service Date" and "Expected Completion Date". There is a function that automatically fills in the "Expected Completion Date" with Date of Request +14 or 28, as appropriate, and I am using conditional formatting to turn the text white for the dates in the "Expected Completion Date" column when there is a date in the "Requested Go Live Date/Discontinue Service Date" column. What I can't turn white are the colored status symbol dots in the "Status Requested Go Live Date/Discontinue Service" and "Status Expected Completion Date" columns when they are not needed. Any suggestions on how to handle that?
Thanks so much,
Ashley
Best Answers
-
I hope you're well and safe!
Try something like this. (You probably would need to change the order)
=IF([July 2022]@row = "", "", IF([July 2022]@row > 0.995, "Green", IF([July 2022]@row <= 0.995 - 0.98, "Yellow", IF([July 2022]@row < 0.98, "Red", "Gray"))))
Did that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Ashley McAdoo It looks like that second one should work if you get rid of the OR function and its set of opening and closing parenthesis.
-
Ok. Exactly which scenario isn't working?
Maybe this?
=IF([July 2022]@row <> "", IF([July 2022]@row < 0.98, "Red", IF([July 2022]@row <= 0.995, "Yellow", "Green")), "Gray")
Answers
-
Lets try fixing some syntax errors to see where we can get from there...
=IF([Requested Go Live Date/Discontinue Service Date]@row]<=TODAY(+5), "Red", IF([Requested Go Live Date/Discontinue Service Date]@row<=TODAY(+9), "Yellow", IF([Requested Go Live Date/Discontinue Service Date]@row<=TODAY(+14), "Green", "Blue")))
-
Thank you. That fixed one problem after I took out the extra bracket after the first @row. I still need to trigger the blue color based on whether or not a completion date has been populated, but this is a start. Any idea how to make the color dots not show when they are not needed? (i.e., the ones in the Expected Completion date column when the Requested Go Live date is what we are basing completion on.)
Thank so much,
Ashley
-
You would need to include another IF statement at the beginning to output blank (a set of quotes with nothing in between) when you don't want a color populated.
Blank = ""
-
Hey @Paul Newcome,
I am stuck on this again. I am trying to populate Red, Green or Gray (Gray is no data) on some rows where there are no parameters for Yellow, and R,GN, Y or GY on other rows where there are parameters for yellow. I am trying to follow the formula you gave me above, but I can I only get R or GN to populate, nothing else. Attached is the data I am working with and here are my 2 formulas. What am I doing wrong?
=IF(OR([July 2022]@row>= 0.95, "Green", [July 2022]@row ,= 0.95"Red", "Gray")) UNPARSEABLE
=IF(OR([July 2022]@row < 0.98, "Red", IF([July 2022]@row <= 0.995 - 0.98, "Yellow", IF([July 2022]@row > 0.995, "Green", "Gray")))) INCORRECT ARGUEMENT SET
Thank you,
Ashley
-
I hope you're well and safe!
Try something like this. (You probably would need to change the order)
=IF([July 2022]@row = "", "", IF([July 2022]@row > 0.995, "Green", IF([July 2022]@row <= 0.995 - 0.98, "Yellow", IF([July 2022]@row < 0.98, "Red", "Gray"))))
Did that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Ashley McAdoo It looks like that second one should work if you get rid of the OR function and its set of opening and closing parenthesis.
-
@Andrée Starå , thanks so much. That is working!
Actually I spoke too soon. I realized it is populating gray only, regardless of the number in the other column.
-
@Paul Newcome, that halfway worked. It is filling in colors, but it's filling in red for cells where there is no data.
-
Because blank is less than 0.98. Try starting it off like this:
=IF([July 2022]@row <> "", IF([July 2022]@row < 0.98, "Red", IF([July 2022]@row <= 0.995 - 0.98, "Yellow", IF([July 2022]@row > 0.995, "Green", "Gray"))))
-
Hey @Paul Newcome
Finally got to get back on this project. Gray is populating where it should be Yellow. I wanted to Gray to populate for blank spaces, but now I need it to populate for N/A. Green and Red seem to be okay, but there is no yellow at all. I tried to tweak the formula but did not have any luck. Suggestions?
Thanks,
Ashley
-
I think there may be some confusion with your criteria. Your existing formula says...
Less than .98 = Red
Less than or equal to negative .03 = Yellow
Greater than .995 = Green
Are you able to clarify?
=IF([July 2022]@row <> "", IF([July 2022]@row < 0.98, "Red", IF([July 2022]@row <= 0.995 - 0.98, "Yellow", IF([July 2022]@row > 0.995, "Green", "Gray"))))
-
Yellow is supposed to be a range between 0.995 and 0.98, not an equation.
-
@Ashley McAdoo Try this then:
=IF([July 2022]@row <> "", IF([July 2022]@row < 0.98, "Red", IF([July 2022]@row <= 0.995, "Yellow", IF([July 2022]@row > 0.995, "Green", "Gray"))))
-
Hey @Paul Newcome,
The formula still doesn't work. If there is no data in the July 2022 cell than the symbol should be gray. If July 2022 is less than 98% it should be red. If July 2022 is greater than 99.5% it should be green. Anything that falls between 98 and 99.5 percent should be yellow. I hope this makes it more clear.
Thanks,
Ashley
-
Ok. Exactly which scenario isn't working?
Maybe this?
=IF([July 2022]@row <> "", IF([July 2022]@row < 0.98, "Red", IF([July 2022]@row <= 0.995, "Yellow", "Green")), "Gray")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!