# 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!

• ✭✭✭✭✭✭
edited 07/03/20

Check for % complete being 100% first. If so, make the flag equal to 0 (off). This will prevent flag from ever turning on if % complete is 100%. This is a general best practice for logic based formula as the first criteria met will end the logic flow.

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

You do not status in the formula to make your conditional formatting work. Formulas and conditional formatting are independent of each other, though sometimes they rely on each other.

In the example below, I added a Status column that references % complete to classify a task as Not Started, In Progress, or Complete.

There is conditional formatting built off of this to change % complete based on status.

School of Sheets (Smartsheet Partner)

If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

• Thank you Dan - this is so close to working...

The formula that is closest to what I'm looking for is:

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

---- Remaining Issue: on rows that have no value at all, the flag is activated - thoughts?

Thank you, Cindy

• Hi Andrée Starå

Thank you very much - that did the trick!

I hope you have a great weekend and stay safe!

Cindy

• ✭✭✭✭✭✭

Excellent!

You're more than welcome!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!