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!

Find more posts tagged with

Sheets

Formulas

Email Notifications

Accepted answers

Andrée Starå

Hi @Cindy Doucet

Try something like this.

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

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

All comments

SoS | Dan Palenchar

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.

Cindy Doucet

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

Andrée Starå

Hi @Cindy Doucet

Try something like this.

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

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Cindy Doucet

Hi Andrée Starå

Thank you very much - that did the trick!

I hope you have a great weekend and stay safe!

Cindy

Andrée Starå

@Cindy Doucet

Excellent!

You're more than welcome!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.