I have a list of time intervals along with their convenience ratings. I wish to highlight intervals rated anything other than impossible which conflict with those rated impossible. I use start1 and end1 named ranges to refer to the interval endpoints and convenience is self-explanatory.
I couldn't do it with ISBETWEEN, but the following formula appears to correctly return true for all such intervals and false for others:
=OR(ARRAYFORMULA( IF($C1<>"impossible", IF(GTE($A1,FILTER(start1,convenience="impossible")), IF(LTE($A1,FILTER(finish1,convenience="impossible")),TRUE) ) )))
However, when I enter this in Conditional Formatting it is rejected as invalid formula.
How can I add it to the conditional formatting rule?
Here's the spreadsheet. The data in question is the sheet named "TEST" (the last one). The formula has been entered in column P.