Nesting IF, AND, and OR

Justin1947 ✭✭
edited 03/22/24 in Formulas and Functions

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

  • Justin1947
    Justin1947 ✭✭
    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)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!