Multiple IF statements (or similar) to drive RYGB
I am trying to write a formula that returns a Red/Yellow/Green based on 4 part. I've searched the community but nothing has helped so far. I've tried IF AND and IF INDEX formulas but I'm clearly getting something wrong in the logic or expression usage. UNPARSEABLE is my constant friend...
The example is for a project and I want to return an RYGB based on the Status, the Start Date and/or the End Date.
In descriptive form I want the formula to,
Return Blue if the Status = Not Started and Start Date > TODAY(), return Yellow if the Status = Not Started and Start Date is < TODAY(), Return Green if Status = In Progress and End Date < TODAY or Status = Completed, Red when Status <> Complete and End Date <= TODAY(-1)
My sheet is more or less like this:
I've been messing around with a variety of formula so if here is a better way I am completely open to suggestions...
Cheers
Best Answers
-
=IF(AND(Status@row = "NOT STARTED", [Start Date]@row > TODAY()), "BLUE", IF(AND(Status@row = "NOT STARTED", [Start Date]@row < TODAY()), "YELLOW", IF(AND(Status@row = "IN PROGRESS", [Start Date]@row < TODAY()), "GREEN", IF(Status@row = "COMPLETED", "GREEN", IF(AND(Status@row <> "IN PROGRESS", [End Date]@row < TODAY(-1)), "RED")))))
...
-
Hi @Wade in NZ
This is exactly what your "Green" rule is looking for... if the task is "in progress" and the Start date is in the past:
IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",
What I would suggest doing is move your end "Red" statement up earlier, so it first checks the end date of the In Progress tasks before checking the start date.
This should be higher in the order of operations:
IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red"))))))
Try:
=IF(AND(Status@row = "Not Started", [Target Start Date]@row > TODAY()), "Gray",
IF(AND(Status@row = "Not Started", [Target End Date]@row < TODAY()), "Red",
IF(AND(Status@row = "Not Started", [Target Start Date]@row < TODAY()), "Yellow",
IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red",
IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",
IF(Status@row = "Completed", "Green"))))))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Try something like this:
IF(OR(Status@row = "Completed", "Green", IF([End Date]@row < TODAY(), "Red", IF(Status@row = "Not Started", IF([Start Date]@row>= TODAY(), "Blue", "Yellow")), "Green"))
-
=IF(AND(Status@row = "NOT STARTED", [Start Date]@row > TODAY()), "BLUE", IF(AND(Status@row = "NOT STARTED", [Start Date]@row < TODAY()), "YELLOW", IF(AND(Status@row = "IN PROGRESS", [Start Date]@row < TODAY()), "GREEN", IF(Status@row = "COMPLETED", "GREEN", IF(AND(Status@row <> "IN PROGRESS", [End Date]@row < TODAY(-1)), "RED")))))
...
-
Awesome! Many thanks! Worked a treat!
-
Run into a slight snag when I implemented this formula in a project we knew to be going a bit sideways. My final formula is below and unfortunately it returns a Green when the task in In Progress but the Target Start Date is < TODAY and therefore overdue. The Overdue report picked up the task but, with the Schedule Health still showing Green, it is causing confusion. Somewhere my logic hierarchy is falling over...
=IF(AND(Status@row = "Not Started", [Target Start Date]@row > TODAY()), "Gray",
IF(AND(Status@row = "Not Started", [Target End Date]@row < TODAY()), "Red",
IF(AND(Status@row = "Not Started", [Target Start Date]@row < TODAY()), "Yellow",
IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",
IF(Status@row = "Completed", "Green",
IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red"))))))
-
Hi @Wade in NZ
This is exactly what your "Green" rule is looking for... if the task is "in progress" and the Start date is in the past:
IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",
What I would suggest doing is move your end "Red" statement up earlier, so it first checks the end date of the In Progress tasks before checking the start date.
This should be higher in the order of operations:
IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red"))))))
Try:
=IF(AND(Status@row = "Not Started", [Target Start Date]@row > TODAY()), "Gray",
IF(AND(Status@row = "Not Started", [Target End Date]@row < TODAY()), "Red",
IF(AND(Status@row = "Not Started", [Target Start Date]@row < TODAY()), "Yellow",
IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red",
IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",
IF(Status@row = "Completed", "Green"))))))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!