Here's what I'm trying to do. I have three columns of data. ID, Date(MM/DD/YY), Time(00:00).
I need to create a chart or table that shows the number of occurrences on, say, 12/10/2010 between 00:00 and 00:59, 1:00 and 1:59, etc, for each hour of the day.
I can do countif and get results for the date, but I cannot figure out how to show a summary of the count of occurrences per hour for the 24 hour period. I have months of data and many times each day. Example of data set is below. Any help is 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 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
And so on. Sorry for the length but I wanted to be clear.
EDIT
Here is a sample spreadsheet. Very little data, but I couldn't figure out a better way without having a huge file. Tested in notepad for formatting and worked ok on import as 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