IF AND OR formula

Jay Klomp

I have been trying for way too long to get this formula working correctly - time to ask the pros. I would like the "At Risk" column to flag if "Due Date" has passed and if "Status" is anything other than "Completed" or "% Compete" is anything other than "100%"

I've tried many different ways to get this to work without success. Help is appreciated.

Nick Korna

Hi @Jay Klomp,

Something like this?

=IF(AND(Status@row <> "Completed", [% Complete]@row < 1, [Due Date]@row < TODAY()), 1, 0)

Sample output:

I'm not sure about that last line, but since it is complete (percentage wise), I am not sure it would need flagging. Please let us know if you have any problems/questions though!

Jay Klomp

Thanks for helping @Nick Korna. This works but not quite the way that I need it to. With this formula, if "% Completed" is at 100% the row remains unflagged regardless of what the "Status" says. I need it to flag if the row is past due and the status is anything other than Completed or if it is past due and "%Status" is not 100% or both. I'm trying to weed out users that might forget to change one of those columns. This formula also flags the row if there is no info filled out at all.

Nick Korna

You could add in automation workflows to change the values rather than relying on users to do it. For example, if the % complete is 100%, change the Status to "Complete" (and another workflow for vice versa).

Jay Klomp

I could but the problem with an automation is that, in the instance of if "Status" changes to Complete then change cell value in "% Complete", you are given a text field. If I enter 100% in that field, it no longer calculates as a total percentage of its group.

I think a good formula for the "At Risk" column would still be the better option. If "Due Date" is past and "Status" is anything other than Completed and/or "% Complete" is anything other than 100%. I'm just not sure how to properly string the ANDs and ORs.

Jay Klomp

Nevermind about the automation part. I was overlooking something and it now works. I might be able to make this work now.