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
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!