Nesting IF(AND with an OR, evaluate multiple conditions to check a box
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
Answers
-
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
-
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
-
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)
-
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.
-
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)
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!