Nesting IF, AND, and OR

Justin1947

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, "")

Justin1947

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)

Paul Newcome

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)

brianschmidt

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!:)

Justin1947

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)

Paul Newcome

Happy to help. 👍️