RYGB Status not turning red
I'm using the formula below and based on the date (03/08/21), row 2 Health should be red. Any thoughts?
=IF(FinalDeckReceived@row = 1, "Blue", IF([Deck Due Date]@row <= TODAY(+7), "Yellow", IF([Deck Due Date]@row > TODAY(-7), "Green", IF([Deck Due Date]@row > TODAY(), "Red", "Blue"))))
Best Answer
-
Hi @Bob Kernan
Logic formulas read instructions from the left-to-right and stop as soon as there is a statement that matches the criteria. Since in the screen capture above the date (03/08/21) is less than Today + 7 days, then it's Yellow. The formula doesn't check the rest of the criteria since the date is indeed in the past.
You'll want to add in starting dates as well as ending dates... meaning to look between two dates as a range instead of just providing one criteria. For example, Yellow might be less than Today + 7 , but also, greater than Today.
Try this:
=IF(FinalDeckReceived@row = 1, "Blue", IF(AND([Deck Due Date]@row <= TODAY(+7), [Deck Due Date]@row > TODAY()), "Yellow", IF(AND([Deck Due Date]@row > TODAY(-7), [Deck Due Date]@row < TODAY()), "Green", IF([Deck Due Date]@row < TODAY(), "Red", "Blue"))))
Cheers!
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Answers
-
Hi @Bob Kernan
I hope you're well and safe!
Not sure I follow.
Your example looks correct, but I'm guessing that you want the rows before today that aren't checked to be red.
Correct?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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.
-
I want any date that is past the Deck Due Date to turn Red
-
Hi @Bob Kernan
Logic formulas read instructions from the left-to-right and stop as soon as there is a statement that matches the criteria. Since in the screen capture above the date (03/08/21) is less than Today + 7 days, then it's Yellow. The formula doesn't check the rest of the criteria since the date is indeed in the past.
You'll want to add in starting dates as well as ending dates... meaning to look between two dates as a range instead of just providing one criteria. For example, Yellow might be less than Today + 7 , but also, greater than Today.
Try this:
=IF(FinalDeckReceived@row = 1, "Blue", IF(AND([Deck Due Date]@row <= TODAY(+7), [Deck Due Date]@row > TODAY()), "Yellow", IF(AND([Deck Due Date]@row > TODAY(-7), [Deck Due Date]@row < TODAY()), "Green", IF([Deck Due Date]@row < TODAY(), "Red", "Blue"))))
Cheers!
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Thank you Genevieve
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!