If/And/Or

Carla S

These are the bane of my existance.....

In plain English:

If status is either complete or remove and the due date is in the past, do not check the overdue box, otherwise if status is not complete/remove and due date is in the past check the box.

I have tried a number of combinations and I am not getting the expected results.

=IF(AND([Due Date]@row <= TODAY(), OR(Status@row <> "Complete", Status@row <> "Remove")), 0, 1) - checks all boxes that are NOT overdue

=IF(AND([Due Date]@row <= TODAY(), OR(Status@row = "Complete", Status@row = "Remove")), 1, 0) - checks the overdue boxes for complete/remove but not in progress and hold

=IF(AND([Due Date]@row <= TODAY(), OR(Status@row = "Complete", Status@row = "Remove")), 0, 1) checks complete/remove that are not overdue as well as hold/in progress overdue and not overdue

=IF(AND([Due Date]@row <= TODAY(), OR(Status@row <> "Complete", Status@row <> "Remove")), 1, 0) check all overdue including complete/remove

These are just samples of our status options, we have several, but do not want complete/remove checked when overdue, but would like the others.

I am sure it is a simple change.....thank you

Nick Korna

Hi @Carla S,

I think you're trying to maybe put too much on a single AND/OR, so how about this instead:

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

If Past due, but complete/remove, then unticked. If due date is after today, untick. Otherwise, ticked.

Sample output:

Hope this helps, but if I've misunderstood something or you've any problems/questions then just post! 🙂

Carla S

Perfect, thank you. I appreciate the help!

Nick Korna

No problem, happy to help.