"At Risk" Formula

Jessica Takata

Hi,

Searching for help to create a formula for the "At Risk" column that turns the flag red when the following criteria is met:

- Anticipated Due Date is in the past

- % Complete is not 100%

- Status is not "On Hold" or "Canceled"

I found this article that helped with the first two criterion, but I still can't seem to get the last one right. We *could* just delete the date for those statuses, but there are times when we still want to capture that information even if the project/task is on hold or canceled.

Help?

Thank you!  

Mike Wilday

Try this one and let me know if it works for you. 

=IF(AND([Anticipated Due Date]@row <= Today(), [% Complete]@row&lt;>1,OR(Status@row <>"On hold", Status@row <>"Canceled")),1, 0)

Jessica Takata

Thank you for the quick reply! I'm getting a "BOOLEAN EXPECTED" message?

Mike Wilday

Jessica,

Right-click on the column header and tell me what type of symbol column you are using? The formula I gave you was for the flag column. 

Jessica Takata

Hi,

Yes, it's the flag symbol/column I'm using, but maybe I'm doing something wrong? I attached a screenshot of what I see when I right-click...

Thank you.

Mike Wilday

Can you share a screenshot of the formula that you input that is giving you that response? 

Jessica Takata

Okay, so strange, but I just reinserted the formula and I'm no longer getting an error message (yeah!), but it also appears to still be turning red when On Hold and Canceled are chosen. The only thing I changed was  "Anticipated Due Date" to "Anticipated End Date" to match the column title.

I attached a screenshot.

Thank you!

Mike Wilday

Let's split that out from the rest of the formula because in ANY case, a cancelled or on hold project should be blank... Try this one: it looks for on hold or canceled and then puts a zero, if not, then it checks for the other items. 

=IF(OR(Status@row <>"On hold", Status@row <>"Canceled"), 0, IF(AND([Anticipated End Date]@row <= Today(), [% Complete]@row&lt;>1), 1, 0))

Jessica Takata

It seems to turn them all "off" (white) now... But I see your point. I can request that the team maintain the details for On Hold and Canceled projects elsewhere and remove the dates from the smartsheet. This should fix the issue.

Thank you for your help!

Mike Wilday

Whoops, I had an error there. 

=IF(OR(Status@row ="On hold", Status@row ="Canceled"), 0, IF(AND([Anticipated End Date]@row <= Today(), [% Complete]@row&lt;>1), 1, 0))

Mike Wilday

That edit should fix your issue. You should be fine having them track status in the same sheet. Let me know if that solved the problem! 

Jessica Takata

Yes! This appears to be working now.

Thank you very much!

dhall

Stumbled upon this thread and I know it's a couple years old but I'm curious if this formula still works. When I paste it into my sheet, it doesn't acknowledge the columns. Only thing I can identify is that the "&lt;" at the end of [% Complete]@row" is causing it.

If I remove the "&lt;", the column names in the formula properly highlight, but then the formula doesn't work. Are these 4 characters in this formula important?

I feel like this one makes more sense for what I'm trying to accomplish:

=IF(OR(Status@row ="On hold", Status@row ="Canceled"), 0, IF(AND([End Date]@row <= Today(), [% Complete]@row<1), 1, 0))

Only edits I made were to remove "&lt;", changed the % Complete to be LESS THAN 100% (1), and End Date instead of Anticipated End Date.

dhall

Okay so I did a search for the "&lt;" and "&It;" (lowercase L vs uppercase I, respectively) in the Smartsheet community and it looks like every post that contains this in a formula is from 2018-2019, so it suggests it is no longer needed as part of the formula.

Can we get someone to confirm that's the case and if so, is just removing it the solution to using this type of formula?

Genevieve P.

Hi @dhall

In 2019 we migrated our Community from one platform to another. I believe the values you're seeing ("&lt";) are a remnant of how the data was copied. They're not a part of Smartsheet formulas and will give you an error.

In regards to the formula, the structure should work! And yes, you would need to update the column names to be your own column names.

=IF(OR(Status@row ="On hold", Status@row ="Canceled"), 0, IF(AND([End Date]@row <= Today(), [% Complete]@row <1), 1, 0))

When you say this "doesn't work", are you receiving an error or an incorrect result? Can you confirm that your Status column also has the same dropdown values ("On hold", "Canceled")?

dhall

Hi @Genevieve P. thanks for the quick reply!

That settles it, I just wanted to make sure there wasn't some part of a formula that needed &lt; and I was going crazy thinking how I could not have seen this before.

I'm using this formula as a base to test potentially using the colored balls (red, yellow, green, blue) to for dates and % complete, but good to know I'm not crazy!