IF/AND date formula to flag a delayed task

Hi!

I want my formula to flag in red when a task is late, that is, when it is still "in progress" when the "end date" is today. The smartsheet accepted the formula below, but it didn't work. Can you help me?

=IF(State@row = "complete", "Green", IF(State@row = "in progress", "Yellow", IF(State@row = "recurrent", "Yellow", IF(State@row = "impediment", "Red", IF(AND(State@row <> "complete", TODAY() > [End Date]@row), "Red", IF(State@row = "not started", "Blue"))))))

Thanks!

Find more posts tagged with

Accepted answers

Darren Mullen

@Mariana Pontual Hi! You'll have to change up the order of your IF statements. What is happening is that Smartsheet evaluates the first IF statement in the series of nested IF's that is TRUE, so when the STATE is "In Progress" it is automatically changing the color to "Yellow" and it never gets to the IF(AND(State@row <> "complete", TODAY() > [End Date]@row), "Red". statement

So, you'll need to have to order as follows:

=IF(State@row = "complete", "Green", IF(AND(State@row <> "complete", TODAY() > [End Date]@row), "Red", IF(State@row = "impediment", " , "Red", IF(State@row = "in progress", "Yellow", IF(State@row = "recurrent", "Yellow", IF(State@row = "not started", "Blue"))))))

I think that should work, haven't thought through all the combinations, but it should solve your issue.

All comments

Darren Mullen

So, you'll need to have to order as follows:

I think that should work, haven't thought through all the combinations, but it should solve your issue.

Mariana Pontual

Hi @Darren Mullen, thanks for your quick answer. 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.