Of course, it would be easier if you had all your start/end times in separate cells... but you can try this in B3:
=SUMPRODUCT((MID(Sheet1!B$4:B$24,1,5)+0<=$A3)*(MID (Sheet1!B$4:B$24,FIND("-",Sheet1!B$4:B$24)+1,5)+0>=$A3))+SUMPRODUCT(( IFERROR(MID(Sheet1!B$4:B$24,13,5)+0,0)<=$A3)*( IFERROR(MID(Sheet1!B$4:B$24,19,5)+0,0)>=$A3))
you must confirm this formula with CTRL+SHIFT+ENTER not just ENTER, then copy to the right and down
|