Remove "In Progress" from formula, but comes back unparseable

Ollie Cater · 04/19/21

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

Heather Duff · 04/19/21

@Ollie Cater

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", ""))))

Heather Duff · 04/19/21

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

Ollie Cater · 04/19/21

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.