At Risk Flag Formula issues/questions

Pat Sullivan

I searched through the Community and found a couple of formulae that either directly addressed what I want to accomplish or I thought could be modifed to do what I want.  I get the #UNPARSEABLE  error for each of them.  I'm sure I'm missing something basic (new user) since all of the responses to the solutions are positive.

I want to display the At Risk flag if the item has an End Date of less than three days from today and is not marked Complete in some way.  I started with the first formula using the column "% Complete" formatted as Text/Number.

=IF(AND([End Date]@row&lt;=Today(3), [End Date]@row&gt;=Today(),Status@row<>"Complete",[%complete]@row&lt;>1),1,0)

I did modify the above code but only to change the Column names to match my own. It's certainly the more complex of the two but others seemed to have had success.

The second one I tried was this one

=IF(AND(Complete@row = 0, TODAY(3) >= [End Date]@row), 1)

For this one I added a column "Complete" of type "Checkbox"

What other info can I provide or what am I missing?   I don't have any other formulae in the sheet.  Also, I created a new column for the At Risk flag of type "Symbols" as I can't see a way to apply a formula to the existing column since it is used in conditional formatting.  I'm not sure what it is triggered by either.

Apologies up front for the newbie questions.  One more thing looking forward...If I can get the first formula to work, does the %Complete cell have to have a value of 0 if not started for the formula to work or can it be blank until there is progress?

 

Paul Newcome

Try something like this...

 

=IF(AND([% Complete]@row <> 1, [End Date]@row >= TODAY(-3)), 1)

 

This will also work if the % Complete is blank and the End Date is approaching.

Pat Sullivan

Thanks Paul.  Some progress. I no longer get #UNPARSEABLE but with the test item %COMPLETE blank and the End Date in the past there is no flag.  Not a deal breaker since when I set an end date in the future but less than 3 days away, and the %COMPLETE is blank, I do get a red flag.  Just not if the End Date is in the past.

Is there any clear explanation of the syntax of the IF expression anywhere? I've looked at a couple of links from the Community but I must be too dull to understand the use of AND or OR in this expression.

Paul Newcome

Let's try this one instead...

 

=IF(AND([% Complete]@row <> 1, TODAY() >= [End Date]@row -3), 1)

.

And in an effort to not complicate things any further for you...

 

What is your current understanding of IF, AND, and OR statements? Tell me what you know/understand, and I will be more than happy to try to expand on things or explain differently for you.

Danielle Maroon

@Paul Newcome im looking for help on this risk formula as well.

Currently my formula reads:

=IF(AND([End Date]9 < TODAY(), NOT(Status9 = "Complete")), 1, 0).

but I want to update it in addition to that, I want it to not flag (so false?) if status = On Hold or Cancelled regardless of the end date field?

Andrée Starå

@Danielle Maroon

I answered your other post, but thought I'd answer here as well.

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

@Andrée Starå This is perfect, exactly what I needed!

Thanks so much!

Andrée Starå

@Danielle Maroon

Excellent!

Happy to help!

✅Remember! 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!