At risk flagged, if/and formulas

Andy Grunhovd

I struggling with creating a formula that flags at risk if End Date is within 3 days or past, if Corrective Action is checked, and Review Status is not Complete. Basically if Corrective Action is not checked or Complete is selected, no flag, otherwise if End Date is within 3 days or in the past At Risk is flagged. I've tried so many different combinations and just can't get what I need.

Thanks for your help,

Andy

Purnima Gore

Hi @Andy Grunhovd

I have created the following for a similar purpose.

I usually use notepad, add one part of the formula, make sure it works and then add a bit more :-) It can take a good night's sleep to make it work sometimes :-)

At risk based on status, the finish date and % complete

=IF(Status@row = "Complete", "Gray", IF(AND(Finish@row < TODAY(), [% Complete]@row < "1"), "Red", IF(AND(Finish@row > TODAY(), Finish@row < (TODAY() + 14), [% Complete]@row < "0.50"), "Red", IF(AND(Finish@row > TODAY(), Finish@row < (TODAY() + 14), [% Complete]@row < "0.75"), "Yellow", "Green"))))

• So if the Task is complete then Gray
• If the date is less than today and the % complete is less than 100 then it goes read.
• If the date is greater than today and finish less than 14 days in the future and the %complete is less than 50% then it goes red.
• If the date is greater than today and finish less than 14 days in the future and the % complete is less than 75% then it goes yellow
• Everything else is green.

Hope that helps.

Kind regards

Purnima

Purnima Gore

Hi @Andy Grunhovd

I have created the following for a similar purpose.

I usually use notepad, add one part of the formula, make sure it works and then add a bit more :-) It can take a good night's sleep to make it work sometimes :-)

At risk based on status, the finish date and % complete

=IF(Status@row = "Complete", "Gray", IF(AND(Finish@row < TODAY(), [% Complete]@row < "1"), "Red", IF(AND(Finish@row > TODAY(), Finish@row < (TODAY() + 14), [% Complete]@row < "0.50"), "Red", IF(AND(Finish@row > TODAY(), Finish@row < (TODAY() + 14), [% Complete]@row < "0.75"), "Yellow", "Green"))))

• So if the Task is complete then Gray
• If the date is less than today and the % complete is less than 100 then it goes read.
• If the date is greater than today and finish less than 14 days in the future and the %complete is less than 50% then it goes red.
• If the date is greater than today and finish less than 14 days in the future and the % complete is less than 75% then it goes yellow
• Everything else is green.

Hope that helps.

Kind regards

Purnima

Andy Grunhovd

@Purnima (Cierr)

Wow, that's awesome and unfortunately way more than I really need or could use. I was able to make it work based on what you sent though. This is what I came up with. I forgot to mention that I am also counting the flagged for metrics and I only need those flagged, or at risk, counted.

=IF(AND([End Date]@row < TODAY(-3), NOT([Review Status]@row = "Complete")), 1, 0)

Thank you!