Help with formula with multiple conditions to auto-update RYG ball
I am trying to create a formula with multiple conditions in order to automatically change the color of the RYG ball.
IF the Status = Not Started/In Progress/Blank AND the End Date is less than TODAY () then the ball should be Red
OR IF the Status = Not Started/In Progress/Blank AND the End Date is in the next 7 days then the ball should be Yellow
otherwise the ball should be Green
I currently have this formula =IFERROR(IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(+7), "Yellow", "Green")), " ") but I just need to add other conditions for Red & Yellow status.
Thanks in advance for any help!
Best Answer
-
Try something like this...
=IFERROR(IF(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = ""), IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(7), "Yellow", "Green")), ""), "")
Answers
-
Try something like this...
=IFERROR(IF(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = ""), IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(7), "Yellow", "Green")), ""), "")
-
That works - thank you Paul!
-
Happy to help. 👍️
-
@Paul Newcome this formula works great but when my Status field is blank (no End Date entered) I want the default of my RYG to be blank. Is there a way to add that into the existing formula you helped me with?
-
Yes. Your initial post had a "blank status" included as one of the possible options for generating a color. If you want it to be left blank, then we should be able to just remove that particular bit from the original formula like so:
=IFERROR(IF(OR(Status@row = "Not Started", Status@row = "In Progress"), IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(7), "Yellow", "Green")), ""), "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!