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

Best Answer


  • Andrew Stewart
    Andrew Stewart ✭✭✭
    Answer ✓

    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)

  • 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
    rbuckner ✭✭✭

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

  • rbuckner
    rbuckner ✭✭✭

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


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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!