# Clearing the Risk Flag

Options

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?

• ✭✭✭✭✭
Options

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)

• Options

Here is my Formula:

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

Here is what I am trying to accomplish:

Flag Activated when:

30 days from Due date and % Complete is less than 75%

Status is "On Hold"

Status is "Not Started" and it’s within 30 days of Due Date (regardless of % Complete)

Flag Removed when:

Status is Cancelled, N/A, or Complete

Complete is 100%

• ✭✭✭✭✭
Options

Try this.

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

The flag will only be activated when it meets the above criteria, so you don't really have to worry about when to remove it.

• Options

@Ayelet Weiner that didn't work.

• ✭✭✭✭✭
edited 12/30/20
Options

Are you getting an error? What exactly is not working?

• ✭✭✭✭✭✭
Options

Hi @Olga Bishop ,

Try:

=IF(OR(Status@row = "On Hold", AND([% Complete]@row < 0.75, [end date]@row<= today(30)), AND(Status@row = "Not Started", [End Date]@row <= TODAY(30))), 1,0)

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭
Options

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)

• Options

Thank you! @Ayelet Weiner this worked perfectly! TY!

• ✭✭✭✭✭
Options