Help with multiple IF statements including both OR and AND

Liana Weitz

I'm trying to write a formula that will check the At Risk box if true and leave it unchecked if not true; this formula will be in the At Risk column.

I have 5 separate arguments which if any one of them is true, should check the At Risk box. The five arguments are:

1. If [start date] is blank

2. If [finish date] is blank

3. if [assigned to] is blank

4. if [finish date] is in the past AND status does not equal "complete"

5. if [start date] is in the past AND status = "not started"

Here's the formula I've tried:

=IF([Anticipated Start Date]@row = "", 1, IF([Anticipated Finish Date]@row = "", 1, IF([Assigned To]@row = "", 1, IF(AND([Anticipated Finish Date]@row < TODAY(), Status@row <> "Complete", 1), IF(AND([Anticipated Start Date]@row < TODAY(), Status@row = "Not Started", 1), 0)))))

Any help would be SO appreciated!

Nic Larsen

It just looks like you closed your AND's in the wrong spot.

Try:

=IF([Anticipated Start Date]@row = "", 1, IF([Anticipated Finish Date]@row = "", 1, IF([Assigned To]@row = "", 1, IF(AND([Anticipated Finish Date]@row < TODAY(), Status@row <> "Complete"), 1, IF(AND([Anticipated Start Date]@row < TODAY(), Status@row = "Not Started"), 1, 0)

Nic Larsen

It just looks like you closed your AND's in the wrong spot.

Try:

=IF([Anticipated Start Date]@row = "", 1, IF([Anticipated Finish Date]@row = "", 1, IF([Assigned To]@row = "", 1, IF(AND([Anticipated Finish Date]@row < TODAY(), Status@row <> "Complete"), 1, IF(AND([Anticipated Start Date]@row < TODAY(), Status@row = "Not Started"), 1, 0)

Liana Weitz

Thank you!!! That worked! You just saved my weekend… 🙌