Count frequency of set hour ranges from large list of times

I have a large list of date/time stamps (over 8000). I am just concerned with the time portions. I need to summarize how many records appeared within a certain hour block.
Example:

Hour / Frequency
04:00-04:59 / 200
05:00-05:59 / 150
06:00-06:59 ...
19:00-19:59
08:00-08:59
09:00-09:59
10:00-10:59
11:00-11:59
12:00-12:59

Attached is a sample of the data (just 100 records) I am working with. This is part of a much larger spreadsheet so I mainly need the correct formula(s). My range of date/times I need to work from is listed in tab "times" column B. The hour ranges I need to count can be found in tab "frequency".

You forgot to actually attach your workbook but in a nutshell, Excel codes date/time as a real number of days. Therefore the part after the decimal point is fraction of a day.

If you have a date/time in A1 and need just the time part you can use

=A1-INT(A1)

Does that get you started?

See attached for a solution. Note a couple of things:

When putting a range of numbers or times in Excel, it is best to put the start time in one cell and the end time in another. When you put them both together in a string, it is much harder to work with.

Your range of times looks like there may be an error, which I preserved in my solution. The time 19:00 appears between 06:00 and 08:00. Should it really be 07:00?

Your data doesn't provide comprehensive test coverage, and I didn't take the time to test with a larger set of data. In fact, only one time on your sheet falls into any of the ranges, and it happens to be 7 PM which falls into the range that I just identified as a likely error.

So you will probably want to run more extensive tests.

Use a Pivot Table.

It's not clear if you intend to use Col A or Col B as source ?

Using whichever is appropriate Insert a Pivot Table and set the DateTime field as both Row Label and Data Field (set to COUNT)

Then to get the frequency by hour (rather than by record):

Right click on any date time stamp in the Row Label and select Group -> choose Hours only.

in your dataset of course there's only one hour - 00 so you will get 00 -> 99 result

Originally Posted by samchargers09

I see there may be some confusion created by my spreadsheet. I do not have a start and end time.

I am talking about sheet "frequency" where you are tabulating the counts. Did you see the change I made?

Just to check - are you able to create a Pivot from your earlier sample file ?

Originally Posted by DonkeyOte

Just to check - are you able to create a Pivot from your earlier sample file ?

Yes. I can create a Pivot from the earlier sample file.

Attached is an updated file with two pivot tables - 1) using column B 2) using column C. Either way my ultimate goal is to simply count the occurrence of records within the defined hour ranges on the "frequency" tab.

time_tbl_pivot.xls

OK, so are you saying you can not group the Pivots in your uploaded file ?

(FWIW - I am able to group both by Month)