Countif to Count Activities in a time interval on a Specific Date

Guys,

I am a Novice, need you help on the below table. Data Represented from Row A to E and Column 1 to 14.



TABLE.

(A) (B) (C) (D) (E)
(1) Date Start Time End Time Duration Refrence Number
(2) 21-Dec-11 16:32:05 16:32:07 00:00:02 123456
(3) 21-Dec-11 16:32:05 16:32:07 00:00:02 123456
(4) 21-Dec-11 16:32:08 16:32:09 00:00:01 123456
(5) 22-Dec-11 16:32:05 16:32:07 00:00:02 123456
(6) 22-Dec-11 16:32:08 16:32:09 00:00:01 123456
(7) 22-Dec-11 16:32:05 16:32:07 00:00:02 123456
(8) 22-Dec-11 17:34:37 17:34:38 00:00:01 123456
(9) 22-Dec-11 17:34:39 17:34:40 00:00:01 123456
(10) 22-Dec-11 17:34:41 17:34:41 00:00:00 123456
(11) 23-Dec-11 18:21:43 18:21:44 00:00:01 123456
(12) 23-Dec-11 18:21:45 18:21:45 00:00:00 123456
(13) 23-Dec-11 19:02:02 19:02:03 00:00:01 123456
(14) 23-Dec-11 19:02:03 19:02:04 00:00:01 123456



What I am looking for is a formula that sorts the data in the below intervals
So when I type the date in cell "B1" , I want the formula such that "C3" scands the table above for only the selected date and returns me the count of refrence numbers in the corresponding interval.
EG. "C3" for 21st Dec shoudl give "0" and "C4" should give "4"



(A) (B) (C)
(1) ENTER DATE 21-Dec-11

(2) INTERVAL INTERVAL Count
(3) 16:00:01 16:30:00 ___
(4) 16:30:01 17:00:00 ___
(5) 17:00:01 17:30:00 ___
(6) 17:30:01 18:00:00 ___
(7) 18:00:01 18:30:00 ___
(8) 18:30:01 19:00:00 ___

Guys,

I am extremely thankful to the immediate response i got.
Excuse me for not being able to understand the excel language.
I have attached the excel sample herein.
Sheet - Rawdata is where my data will be entered.
Sheet named Working in C Column (highlighted in yellow) I need the formula that gives me the count for the already pre defined intervals.
On this sheet I would only change Cell B1 and the below should populate the refrence data.

Thanks in Advance.

Hi Marvin, I dont think it works on 2003. Did you get a chance to see the sample excel I attached, probably a formula might best suite my need.

Originally Posted by MarvinP

Hi,

I was just able to attach my sample file. See if it helps.

RON, Thanks a TON !!
Yup that does it. That exactly how I wanted.
You guys are wonderful.

Originally Posted by Ron Coderre

Using your sample workbook, I entered the formula I posted (adjusted for different ranges and sheet names)
The new formula, copied down, returns the counts you are looking for.

Please Login or Register  to view this content.

Thanks RON,

Thats exactly how and what i wanted.
You guys are superb.
Appreciate the value of such forums for amaetures like us.
You would be seeing more of me now on.

Originally Posted by Ron Coderre

Using your sample workbook, I entered the formula I posted (adjusted for different ranges and sheet names)
The new formula, copied down, returns the counts you are looking for.

Please Login or Register  to view this content.

Thanks Marvin. Still The formula way that Ron suggested works better for me.

Thanks marvin, however the formula way that Ron shared works best for me.
Cheers !!

Originally Posted by MarvinP

I'd create a new column that rounds the minutes to a half hour and use it in my Pivot Table.
Look at http://www.excelforum.com/excel-gene...half-hour.html or
http://www.fanhow.com/knowhow:Round_...Excel_34292849

RON,

IF you around, using the same formula as below, how could I sum up the value instead of counting it?

Originally Posted by Ron Coderre

Using your sample workbook, I entered the formula I posted (adjusted for different ranges and sheet names)
The new formula, copied down, returns the counts you are looking for.

Please Login or Register  to view this content.

Thanks Ron, Yup it worked.
Cheers
QUOTE=Ron Coderre;2671295]Try this regular formula:

Please Login or Register  to view this content.

copied down through C33

Is that what you need?[/QUOTE]