Check if any cells in one column fall between cells in different columns in Google Sheets

Question

So I'm trying to figure out if any call for service happens during the time frame of another call.

The information is set up below like this:

| |[A]|[B]|[C]|
|:---|:---|:---|:---|
|1|"Call Time"|"En route"|"in service"| 
|2|08:30|08:31|09:55 
|3|10:04|10:04|11:25
|4|10:28|10:30|11:15 
|5|12:22|12:23|13:05 
|6|12:45|12:49|13:40
 

So obviously A4 falls between B3,C3, and A6 falls between B5,C5. I've used the formula

=arrayformula(isbetween(a2:a6, b2:b6, c2:c6,)) 

and it will only check if the A value of the specific row is between the B and C values of the same row.

Any help would be greatly appreciated.

Answer 1

Try this formula:

=isbetween(A3:A6,B2:B5,C2:C5)

you could conditional format column A (A3:A1000) with custom formula:

=and(A3<>"",isbetween(A3,B2,C2))