counting entries per hour

[rjchudek]

My spreadsheet contains two text columns that represent dates and times; 11/21/2007 and 0935 for example. The rows are populated with the date and time for every event during a 48 hour period. A single worksheet may contain up to 3,000 events (rows).

How do I create a subtotal for the number of events in each of the forty-eight 1-hour periods?

Robert Chudek

 

[barry houdini]

This is how I envisaged it would work....

Book1
	C	D	E	F	G	H	I
1		Date	Time
2		30-Nov-07	23:43		30/11/2007 22:00	0
3		30-Nov-07	23:44		30/11/2007 23:00	19
4		30-Nov-07	23:44		01/12/2007 00:00	12
5		30-Nov-07	23:45		01/12/2007 01:00	0
6		30-Nov-07	23:47
7		30-Nov-07	23:47
8		30-Nov-07	23:47
9		30-Nov-07	23:48
10		30-Nov-07	23:48
Sheet3

Formula in H2 copied down

=SUMPRODUCT((D$2:D$600+E$2:E$600>=G2)*(D$2:D$600+E$2:E$600< G2+"1:00"))