Raising Flag for at Risk Tasks

I would like the flag to be red if the date is within 7 days of "today", AND, if "Status" is not "Complete", and I want the flag to be white if the "Due Date" is blank, greater than seven days away, or if the task status is "Complete"

I have: IF(ISBLANK([Due Date]9), 0, IF([Due Date]9 <= TODAY(7), 1, 0))

I just don't know how to add the condition with the completion status

Find more posts tagged with

Formulas

functionality

Accepted answers

Andrew Stewart

You can use an AND condition to combine the two, eg

=IF(AND([Due Date]@row <= TODAY(7),[Status]@row<>"Complete"),1,0)

(assuming 1 is red and 0 is white)

All comments

Andrew Stewart

You can use an AND condition to combine the two, eg

=IF(AND([Due Date]@row <= TODAY(7),[Status]@row<>"Complete"),1,0)

(assuming 1 is red and 0 is white)

iriscoetzee

Andrew's solution worked perfectly, but I also need to add that if the due date is blank, flag should be white. How do I edit the formula to allow this?

Thank you!

rbuckner

It worked correctly on one of my sheets, but then I added it to another and it didn't work the same.

rbuckner

=IF(AND(ISBLANK([Due Date]@row), 0, IF([Due Date]@row <= TODAY(7), 1), IF(Status@row <> "Complete", 1, 0)))

I get INCORRECT ARGUMENT

I'm trying to add the condition that iriscoetzee is asking for, which I would also like.

Andrew Stewart

Hi @rbuckner ,

To combine conditions, think about it this way:

IF(ISBLANK([Due Date]@row), 0, "whatever happens when due date is not blank")

You already have IF(AND([Due Date]@row <= TODAY(7),[Status]@row<>"Complete"),1,0) for when the date is not blank, so combining the two gives:

IF(ISBLANK([Due Date]@row), 0, IF(AND([Due Date]@row <= TODAY(7),[Status]@row<>"Complete"),1,0))

I find it helpful to start with simple conditions, working in notepad, and cut and paste sections into a Smartsheet cell to work up to the end result slowly, it is very easy to get a syntax error or miss a bracket, and Smartsheet is not very specific with its error messages.

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.