At Risk Formula Update

Danielle Maroon

Currently using the Onboarding Template Set and in the Sheet Onboarding timeline, I have the Risk flag set, the current formula is to flag if the end date is in the past and the status is not compete (for the row)formula is =IF(AND([End Date]9 < TODAY(), NOT(Status9 = "Complete")), 1, 0) but I need to update it because I want the risk flag to be false if the status is On Hold or Cancelled regardless of what the end date reads? How would I write that in the formula? Any ideas?

Andrée Starå

Hi Danielle,

Try something like this.

I've updated it with @row so you don't have to worry about row numbers and I also added so it doesn't get flagged if End Date is blank.

=IF(OR(Status@row = "On Hold"; Status@row = "Cancelled"); 0; IF(AND([End Date]@row < TODAY(); NOT(ISBLANK([End Date]@row)); NOT(Status@row = "Complete")); 1; 0))

The same version but with the below changes for your and others convenience.

=IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), 0, IF(AND([End Date]@row < TODAY(), NOT(ISBLANK([End Date]@row)), NOT(Status@row = "Complete")), 1, 0))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

Did that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post 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!

Danielle Maroon

@Paul Newcome @Mike Wallace

Andrée Starå

Hi Danielle,

Try something like this.

I've updated it with @row so you don't have to worry about row numbers and I also added so it doesn't get flagged if End Date is blank.

=IF(OR(Status@row = "On Hold"; Status@row = "Cancelled"); 0; IF(AND([End Date]@row < TODAY(); NOT(ISBLANK([End Date]@row)); NOT(Status@row = "Complete")); 1; 0))

The same version but with the below changes for your and others convenience.

=IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), 0, IF(AND([End Date]@row < TODAY(), NOT(ISBLANK([End Date]@row)), NOT(Status@row = "Complete")), 1, 0))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

Did that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post 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!