Raising Flag for at Risk Tasks

rbuckner

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

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)

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.