Remove "In Progress" from formula, but comes back unparseable
Good morning!
I would like to change the behaviour of the below formula that defines rag status...
=IF(Status@row = "Complete", "Gray", IF(AND(Status@row = "In Progress", [Due Date]@row > TODAY()), "Yellow", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row < TODAY(), "Red", "Yellow"))))
I would like to remove the behaviour that if the task is "In Progress" the Rag is set to yellow, and just want Red, Yellow, Green and Gray to trigger as per Due Date only. However when I remove the (AND(Status@row... part the box comes back as unparseable.
I seem to be missing something, please help!
Ollie
Best Answer
-
Let's try reordering it:
=IF(Status@row = "Complete", "Gray", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row > TODAY(), "Yellow", IF([Due Date]@row < TODAY(), "Red", "Yellow"))))
So this reads:
If the status is complete, Gray.
Otherwise, if the start date is more than 7 days in the future, Green.
Otherwise, if the due date is still in the future, Yellow.
Otherwise, if the due date is in the past, Red.
Otherwise, show yellow.
You may want to change the last "yellow" to "" as a check. That way, if anything doesn't meet any of the criteria in the formula, it will show blank. Then we'd be able to troubleshoot where there are gaps in the formula, if any.
=IF(Status@row = "Complete", "Gray", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row > TODAY(), "Yellow", IF([Due Date]@row < TODAY(), "Red", ""))))
Answers
-
Hi @Ollie Cater,
Try this:
=IF(Status@row = "Complete", "Gray", IF([Due Date]@row > TODAY(), "Yellow", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row < TODAY(), "Red", "Yellow"))))
I think you just had an extra end parenthesis in there from the AND(
Let me know if it fixes it!
Best,
Heather
-
Thanks Heather.
That doesn't throw up the error, but the rag status is still showing as yellow more that 7 days before the due date.
Sorry, I'm fairly new to Smart Sheet.
I just want
- Due date is more than 7 days away - Green
- Due date is 7 days away - Yellow
- Due date is passed - Red
- Status is "Complete" - Grey
Really appreciate the assistance.
Ollie
-
Let's try reordering it:
=IF(Status@row = "Complete", "Gray", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row > TODAY(), "Yellow", IF([Due Date]@row < TODAY(), "Red", "Yellow"))))
So this reads:
If the status is complete, Gray.
Otherwise, if the start date is more than 7 days in the future, Green.
Otherwise, if the due date is still in the future, Yellow.
Otherwise, if the due date is in the past, Red.
Otherwise, show yellow.
You may want to change the last "yellow" to "" as a check. That way, if anything doesn't meet any of the criteria in the formula, it will show blank. Then we'd be able to troubleshoot where there are gaps in the formula, if any.
=IF(Status@row = "Complete", "Gray", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row > TODAY(), "Yellow", IF([Due Date]@row < TODAY(), "Red", ""))))
-
That looks to have worked a charm! Thank you!
-
Fantastic! Glad it worked. 😀
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