Nested IF statement for multiple date range

Justin1947

Hello! I am currently attempting to write an all encompassing formula to read through different work windows. We are only able to work in certain areas of the city depending on the "work window". Depending on the work window, these dates have differing start and end dates. I can get one window to work properly, but the logic seems to fall apart or just does not parse when adding in different work windows. The formula for one window is below:

=IF(AND(Date@row >= DATE(2024, 7, 16), Date@row <= DATE(2025, 3, 15), [Work Window]@row = "A : 07/16 - 03/15"), "", 1)

(If a date is outside of 7/16/2024 and 3/15/2025, this cell is flagged as it is outside the work window)

My question is, how do I nest these so the logic makes sense? It would need to read each individual work window, and then filter through the dates manually as we are just using a String for the actual "work window". Logically, I feel like this should work but between the ANDs and ORs I think it falls apart.

The cell that is labeled "works" contains this formula. The same with the rows below it, which is flagging as the dates are outside the aforementioned work window.

Image attached! Any guidance would be huge!!!

Scott Orsey

Hi Justin, You can certainly build a massive formula with nested IFs, but I suspect that will be tough to debug. And, I don't know how many Work Windows you would have so it would be unwieldy too.

A better way to solve this would be to create a reference sheet with the Work Window in the first column and columns for the "start date" and "end date". This sheet could have any number of Work Windows.

Then, to keep things as easy to understand as possible, I would add two helper date columns in your main sheet, one for the start date and one for the end date. You would then use cross sheet references and the INDEX(MATCH) functions to bring the start date and end date from the reference sheet into your main sheet based on the Work Window indicated in the main sheet. You could hide these columns if you don't want to see them.

Then you could construct your same IF statement using AND() to see if the date is after the start date and before the end date.

I hope this helps.

Be well