RYGB Based on two date columns
Hi All,
I have a formula that I can't get right and looking for help with the following:
Green Status = If End Date is in the future and Status is "In Progress"
Yellow Status = If Start Date has begun and the Status is "To Do", Backlog", or "Planning"
Red Status = If End Date has passed and the status is "In Progress" or "On Hold"; If project has no Start Date OR End Date and status is "In Progress" or "On Hold"
Blue Status = If there is no start date and the status is in "To Do", or "Backlog"
Any help would be appreciated, thank you!
Best Answers
-
Hi @MMorgan
Try this formula:
=IF(AND([End Date]@row > TODAY(), Status@row = "In Progress"), "Green", IF(AND(AND([Start Date]@row <> "", [Start Date]@row <= TODAY()), OR(Status@row = "To Do", Status@row = "Backlog", Status@row = "Planning")), "Yellow", IF(AND([End Date]@row < TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND(OR([End Date]@row = "", [Start Date]@row = ""), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND([Start Date]@row = "", OR(Status@row = "To Do", Status@row = "Backlog")), "Blue")))))
Hope it works for you.
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech -
This is great! It works! Thank you so much
Answers
-
Hi @MMorgan
Try this formula:
=IF(AND([End Date]@row > TODAY(), Status@row = "In Progress"), "Green", IF(AND(AND([Start Date]@row <> "", [Start Date]@row <= TODAY()), OR(Status@row = "To Do", Status@row = "Backlog", Status@row = "Planning")), "Yellow", IF(AND([End Date]@row < TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND(OR([End Date]@row = "", [Start Date]@row = ""), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND([Start Date]@row = "", OR(Status@row = "To Do", Status@row = "Backlog")), "Blue")))))
Hope it works for you.
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech -
This is great! Thank you @Gia Thinh. I just ran a test and all items work, except for On Hold when the end date is in the future. Can you possibly add if it is on hold but with a future end date, the status is green. @https://giathinh.tech/wegrid/
Again, i really appreciate this!
-
Try this updated formula:
=IF(AND([End Date]@row > TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Green", IF(AND(AND([Start Date]@row <> "", [Start Date]@row <= TODAY()), OR(Status@row = "To Do", Status@row = "Backlog", Status@row = "Planning")), "Yellow", IF(AND([End Date]@row < TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND(OR([End Date]@row = "", [Start Date]@row = ""), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND([Start Date]@row = "", OR(Status@row = "To Do", Status@row = "Backlog")), "Blue")))))
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech -
This is great! It works! Thank you so much
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 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!