Counting Staff by Hour

Greetings,

I've used this forum for years, but just signed up.

I need help counting the number of staff within a time period. Unfortunately the schedule structure does not allow application of the simple solutions I've found regarding this which results in this being more complicated. My example is attached. My start and end time are in the same cell. I have used the Left() and Right() functions to manipulate the times. I am unable to count the number of occurrences of between the start/stop times of all my employees in the array. The formula also needs to take into account a manually entered time on the actual day.

Basically:
00:00 if F9:F42 = time is TRUE and I9:I42 = "" then count occurrences of 00:00 in scheduled times F9:F42 + if F9:F42 = time is FALSE, then if I9:I42 = time is TRUE then count occurrences of 00:00 in scheduled times I9:I42

01:00
02:00
"
"

Sorry this is confusing. Even when I think I've gotten close the midnight time crossover keeps throwing me off. Let me know how I can clarify. I'd be ecstatic if I could get this to work.

Thanks!

jpickralSchd_05_TEST.xlsx

So I got:

=IF(AND($H63>=TIMEVALUE(LEFT($J$62,5)),TIMEVALUE(RIGHT($J$62,5))>=$H63),1,0)

to work for 06:00-14:30, but it doesn't work for 20:00-04:30. I feel like there is some simple condition test that would enable this to work both ways. But I can't quite get there.

Again, thanks for any help!

Regards,

jpickral