Nesting IF, AND, and OR
Hello there! I am working on a project that will allow us to do a very quick visual check when a date is inside a work window. The problem occurs when a work window has two date ranges, this example being 07/16 to 07/30, and then another work window between 11/15 and 02/01. Not all work windows have two work windows, but it does complicate the logic for what we are trying to achieve. The formula below is not parsable, and I believe the reason is I have the logic incorrect. I am also trying to create a formula that will be all encompassing, so it will only read one work window, or two, depending on if two work windows exist.
There are 4 helper columns, which use a separate sheet to determine what work window a job is in.
The #NO MATCH jobs are jobs that only have one work window. The way I am trying to get it to work is to check if the date is indeed the work window, and to flag a job if it is outside. Any idea on how to fix this?
=IF(AND([Date of Job Start]@row > [WW Start]@row, [Date of Job Start]@row < [WW End]@row) OR([Date of Job Start]@row > [WW F SL Start]@row, [Date of Job Start]@row < [WW F SL End]@row), 1, "")
Best Answer

Thank you both! It took a little tweaking and it would not work without the IFERROR() formula. The final formula used below!
=IF(OR(AND([Finish Date]@row >= [WW Start]@row, [Date of Job Start]@row <= [WW End]@row), AND([Finish Date]@row >= [WW F SL Start]@row, [Date of Job Start]@row <= [WW F SL End]@row)), 0, 1)
Answers

I would suggest using an IFERROR wrapped around the formulas pulling in the dates so that the NO MATCH error doesn't push through.
Then you can try this in the flag column:
=IF(OR(AND([Finish Date]@row>= [WW Start]@row, [Date of Job Start]@row<= [WW End]@row), AND([Finish Date]@row>= [WW F SL Start]@row, [Date of Job Start]@row<= [WW F SL End]@row)), 1)

I would try this instead:
=IF(OR(AND([Date of Job Start]@row > [WW Start]@row, [Date of Job Start]@row < [WW End]@row), AND([Date of Job Start]@row > [WW F SL Start]@row, [Date of Job Start]@row < [WW F SL End]@row)), 1, 0)
This is just based on correcting the structure of the formula. Apologies if I'm misinterpreting the logic you want your statement to follow.
Hope this helps!:)

Thank you both! It took a little tweaking and it would not work without the IFERROR() formula. The final formula used below!
=IF(OR(AND([Finish Date]@row >= [WW Start]@row, [Date of Job Start]@row <= [WW End]@row), AND([Finish Date]@row >= [WW F SL Start]@row, [Date of Job Start]@row <= [WW F SL End]@row)), 0, 1)

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!