At Risk Flag triggered by upcoming due date and/or past due, and % complete less than 100%

Hi, I'm looking for formula help with the at risk flag.

Goal: If target finish date is within 7 days OR past due, and % complete is less than 100%, then activate flag. when % complete equals 100%, flag can deactivate.

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

---Issue with this formula - when today's date is past the target finish date, the flag goes blank again, even if the % complete column is not 100

=IF(OR([Target Finish Date]@row <= TODAY(7), [Target Finish Date]@row >= TODAY(), Status@row <> "Complete", [% Complete]@row <> 1), 1, 0)

---Issue with this formula - flag does not disappear when % complete equals 100

Also, I don't need both status and % complete in the formula. the status column has conditional formatting that will auto-populate based on the % complete column.

Tried this version too, same problem:

=IF(AND([Target Finish Date]@row <= TODAY(7), [Target Finish Date]@row >= TODAY(), [% Complete]@row < 100), 1, 0)

Many Thanks!

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!