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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives