COUNTIFS (Date and Time)

[deadlypawn]

How do I count the number of OT per 30 min interval per date?

Date Start End # of hours
11-May 6:00 PM 8:00 PM 2
11-May 6:00 PM 8:00 PM 2
11-May 6:00 PM 8:00 PM 2
12-May 6:00 PM 8:00 PM 2
12-May 6:00 PM 8:00 PM 2
11-May 6:30 PM 8:00 PM 1.5
11-May 6:30 PM 8:00 PM 1.5
11-May 6:30 PM 8:00 PM 1.5
11-May 6:30 PM 8:00 PM 1.5
12-May 6:30 PM 8:00 PM 1.5
11-May 6:30 PM 8:00 PM 1.5
11-May 7:00 PM 8:00 PM 1
12-May 7:00 PM 8:00 PM 1
12-May 7:00 PM 8:00 PM 1
11-May 7:00 PM 8:00 PM 1
11-May 7:00 PM 8:00 PM 1
11-May 7:00 PM 8:00 PM 1
11-May 7:00 PM 8:00 PM 1
12-May 7:00 PM 8:00 PM 1
11-May 8:00 PM 9:00 PM 1
11-May 9:00 PM 10:00 PM 1

11-May
6:00 PM ?
6:30 PM ?
7:00 PM ?
7:30 PM ?
8:00 PM ?
8:30 PM ?
9:00 PM ?

Here's the formula that I am using:
=SUMPRODUCT(--($I$5:$I$65>=TIMEVALUE("3:00 AM")),--($I$5:$I$65<TIMEVALUE("3:30 AM")))

Any advise?

[Bob Phillips]

This works for me

=SUMPRODUCT(--($H$5:$H$65=--"2012-05-11"),--($I$5:$I$65>=--"18:00:00"),--($I$5:$I$65<--"18:30:00"))

[deadlypawn]

This works for the 6 PM but not for the succeeding intervals. We still need to consider the duration of hours.

[Bob Phillips]

It does, what do you mean?