Start End Formula Logic

[mahmed1]

Hey,

I have a column that has a bunch of start times (Column B)
A bunch of End Times (Column C)
Eg B2 = 08:00 C2= 16:00
09:30 15:30
08:00 14:30
10:00 18:00

etc

in column E2 copied down i have a list of times from 08:00 to 23:30
08:00
08:30
09:00
......

what i want to do is a count the number of shifts that are between 08:00 - 08:30
08:30 - 09:00 etc copied down

the result for 08:00 should show 2

i tried = countifs(B:B,”>=“&E2,C:C,”>=“&E3)

this failed miserable

 

[Phuoc]

Try this:

=COUNTIFS($C$2:$C$5,">"&E2,$B$2:$B$5,"<"&E2+1/48)