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

Find more posts tagged with

Formulas

Accepted answers

Heather Duff

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

All comments

Heather Duff

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

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

Heather Duff

@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.

=IF(Status@row = "Complete", "Gray", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row > TODAY(), "Yellow", IF([Due Date]@row < TODAY(), "Red", ""))))

Ollie Cater

That looks to have worked a charm! Thank you!

Heather Duff

Fantastic! Glad it worked. 😀

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.