Aggregate the time data (mm/dd/yyyy:hh:mm:ss) into the 3 hours range

Hi, I need to aggregate the time data (mm/dd/yyyy:hh:mm:ss) into an hourly range.

So, basically if I have ~16 observation for 2 days, I want to aggregate them into a 3 hours range window and count the number of observations in each range (of 3 hours). Example is given below. Thank you very much in advance.


Raw Data 3 hour range Count
09/08/2013:23:46:41 09/08/2013:03:00:00 0
09/08/2013:23:50:19 09/08/2013:06:00:00 0
09/08/2013:23:50:46 09/08/2013:09:00:00 0
09/09/2013:00:27:04 09/08/2013:12:00:00 0
09/09/2013:00:45:20 09/08/2013:15:00:00 0
09/09/2013:00:45:21 09/08/2013:18:00:00 0
09/09/2013:00:45:21 09/08/2013:21:00:00 0
09/09/2013:00:45:21 09/08/2013:24:00:00 3
09/09/2013:00:45:21 09/09/2013:03:00:00 13
09/09/2013:00:45:21 09/09/2013:06:00:00 0
09/09/2013:00:45:21
09/09/2013:00:45:21
09/09/2013:00:45:21
09/09/2013:00:45:21
09/09/2013:00:45:21
09/09/2013:00:45:21

The function you need is "frequency" and the bin values need to be 3 hourly time segments..

Date & Time separator is ":" in your data. It must be 'space'. I used SUBSTITUTE to replace first ":" to 'space'

In C2, with CTRL+SHIFT+ENTER

=FREQUENCY(SUBSTITUTE(A$2:A$17,":"," ",1)+0,SUBSTITUTE(B2,":"," ",1)+0)-SUM(C$1:C1)

I think that this is what you are looking for:

Thanks for the feedback