Here is what I am trying to do. I have three data columns. ID, date (MM / DD / YY), time (00:00).
I need to create a chart or table that shows the number of occurrences, for example, 12/10/2010 between 00:00 and 00:59, 1:00 and 1:59, etc., for every hour of the day.
I can do a countif and get the results for the date, but I cannot figure out how to show a summary of the number of occurrences per hour over a 24-hour period. I have months of data and many times every day. An example dataset is shown below. Any help greatly
ID Date Time
221 12/10/2010 00:01
223 12/10/2010 00:45
227 12/10/2010 01:13
334 12/11/2010 14:45
I would like the results to be read:
Date Time Count
12/10/2010 00:00AM - 00:59AM 2
12/10/2010 01:00AM - 01:59AM 1
12/10/2010 02:00AM - 02:59AM 0
......(continues for every hour of the day)
12/11/2010 00:00AM - 00:59AM 0
.........
12/11/2010 14:00PM - 14:59PM 1
Etc. Sorry for the length, but I want to be clear.
. , , . csv.
PID,Date,Time
2888759,12/10/2010,0:10
2888760,12/10/2010,0:10
2888761,12/10/2010,0:10
2888762,12/10/2010,0:11
2889078,12/10/2010,15:45
2889079,12/10/2010,15:57
2889080,12/10/2010,15:57
2889081,12/10/2010,15:58
2889082,12/10/2010,16:10
2889083,12/10/2010,16:11
2889084,12/10/2010,16:11
2889085,12/10/2010,16:12
2889086,12/10/2010,16:12
2889087,12/10/2010,16:12
2889088,12/10/2010,16:13
2891529,12/14/2010,16:21