Clearing the Risk Flag

Olga Bishop

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?

Ayelet Weiner

@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)

Olga Bishop

Let me clarify my ask:

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%

Ayelet Weiner

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.

Olga Bishop

@Ayelet Weiner that didn't work.

Ayelet Weiner

https://community.smartsheet.com/discussion/comment/273280#Comment_273280

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

Mark Cronk

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

Ayelet Weiner

@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)

Olga Bishop

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

Ayelet Weiner

Glad I could help!