Clearing the Risk Flag


Hi there,

I am trying to figure out a way to write a formula which will do a couple of things.

If the % Complete is less than 75% 30 days from end date = At Risk flag; If the % Complete is 100% 30 days from End Date = Clear Flag

If If Status "On Hold" is selected (regardless of the % Complete") = At Risk flag; If the Status is changed to In Progress or Complete = Clear the flag

This would not apply to the following statuses: Cancelled, N/A

Is there a way to do what I need it to do?

Best Answer

  • Ayelet Weiner
    Ayelet Weiner ✭✭✭✭✭
    Answer ✓

    @Olga Bishop

    I tested out my original formula and now see why it didn't work. Try this one.

    =IF(OR(Status@row = "On Hold", AND([% Complete]@row < 0.75, [End Date]@row <= TODAY(30), Status@row <> "Cancelled", Status@row <> "N/A", Status@row <> "Complete"), AND(Status@row = "Not Started", [End Date]@row <= TODAY(30))), 1, 0)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!