# Nesting IF, AND, and OR

edited 03/22/24

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

Tags:

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)

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭
edited 03/22/24

=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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!