Nesting IF(AND with an OR, evaluate multiple conditions to check a box

SBell

Hi all

Currently using the following (resides in a checkbox column) that is working perfectly for a project to tag items that are then pulled into a report.

=IF(AND([End Date]@row <= TODAY($HuddleDays$1), [End Date]@row >= TODAY(-4)), 1, 0)

I've found that if an items happen to have a [Status] that is <> Complete and falls outside the 4 days ago condition it gets lost.

I would like to add an OR to the above so it will check for those conditions OR if the [End date] >TODAY(-4), [Status] <> "Complete"

Tried incorporating other examples, but can't get the syntax right. Thought this might do it, however it doesn't check the box unless the condition above is met. It doesn't error either

=IF(AND(OR([End Date]@row > TODAY(-4), Status@row <> "Complete"), [End Date]@row <= TODAY($HuddleDays$1), [End Date]@row >= TODAY(-4)), 1, 0)

Thanks in advance

Christian G.

Hi @SBell

It seems that you are repeating the [End Date]@row >= TODAY(-4) condition

=IF(AND(OR([End Date]@row > TODAY(-4), Status@row <> "Complete"), [End Date]@row <= TODAY($HuddleDays$1), [End Date]@row >= TODAY(-4)), 1, 0)

With that formula, it is going to be 1 ONLY if it is greater than Today(-4)

Remove the second one

=IF(AND(OR([End Date]@row > TODAY(-4), Status@row <> "Complete"), [End Date]@row <= TODAY($HuddleDays$1)), 1, 0)

We can break it down to simple expression so it is easier to see

A=[End Date]@row > TODAY(-4)
B=Status@row <> "Complete"
C=[End Date]@row <= TODAY($HuddleDays$1)

Your formula was:

=IF(AND(OR(A, B), C, A), 1, 0) 

we can rewrite this like that:

if A and C and (A or B) then 1 else 0

We see that A is mandadory

My suggestion changes it to

if (A or B) and C then 1 else 0

I hope it works

SBell

Thank you Christian

Appreciate the response the additional logic notes. I see how you were approaching and will check this out.

The duplicate on the TODAY(-4) was intentional as they are actually 2 different test. one is for TODAY(-4) AND Status <> Complete, the other is ">=TODAY(-4)" AND "<= TODAY($HuddleDays$1)"

The logic I believe is more

A=[End Date]@row > TODAY(-4)

B=Status@row <> "Complete"

C=[End Date]@row <= TODAY($HuddleDays$1)

D=[End Date]@row >= TODAY(-4)

IF D and C OR (A and B) then 1 else 0

If that makes sense. I only want to pull items into the report that are older than 4 days AND are not Complete, OR if the item end date falls within the HuddleDays variable and is within 4 days of today.

Shawn

Christian G.

If that makes sense. I only want to pull items into the report that are older than 4 days AND are not Complete, OR if the item end date falls within the HuddleDays variable and is within 4 days of today.

it should then be:

IF (D and C) OR (A and B) then 1 else 0

=IF(OR(AND([End Date]@row > TODAY(-4), Status@row <> "Complete"), AND([End Date]@row <= TODAY($HuddleDays$1), [End Date]@row >= TODAY(-4))), 1, 0)

SBell

Thanks Christian

We're close. I revised it to be as follows and this is now capturing all but one use case.

=IF(OR(AND([End Date]@row <= TODAY($HuddleDays$1), [End Date]@row >= TODAY(-4)), OR([End Date]@row > TODAY(-4), Status@row <> "Complete")), 1, 0)

Above is working (pic 1 and 2) for Child dates that fall within the condition # of days and status.

If the End Date is greater than TODAY($HuddleDays$1) it will check the box and it shouldn't regardless of status. In this pic, the Child 2 should not be checked as End Date is further out than TODAY($HuddleDays$1)

I have tried switching the order of the expressions, but this results in incorrect results.

Christian G.

What you wrote is this:

A=End Date]@row <= TODAY($HuddleDays$1)

B=[End Date]@row >= TODAY(-4)

C=[End Date]@row > TODAY(-4)

D= Status@row <> "Complete"

if( (A and B) OR (C OR D) ) then 1 else 0

Could be rewritten as if( (A and B) or C or D)

So, as soon as it is <>"complete", it is 1

as soon as [End Date]@row > TODAY(-4) , it is 1

as soos as End Date]@row <= TODAY($HuddleDays$1) AND [End Date]@row >= TODAY(-4), it is 1

If you want If(D and (C OR (A AND B)) then it shoul look like this:

=IF(AND(Status@row <> "Complete",OR([End Date]@row > TODAY(-4),AND(End Date]@row <= TODAY($HuddleDays$1),[End Date]@row >= TODAY(-4)))), 1, 0)

With this, if it's = "Complete" , it is 0, then it is Either In the last 4 days, OR, End Date]@row <= TODAY($HuddleDays$1) AND [End Date]@row >= TODAY(-4)

SBell

Thank you again Christian

This was helpful to help sort the nesting logic of the OR/ANDs.

After some finicking around, here is the revised formula that I have working for the use cases I'm running it through (so far);

=IF(AND([End Date]@row <= TODAY($HuddleDays$1), OR([End Date]@row > TODAY(-4), Status@row <> "Complete", AND([End Date]@row <= TODAY($HuddleDays$1), [End Date]@row >= TODAY(-4)))), 1, 0)

Shawn