Hello and Good Day to you -

I have a spreadsheet containing a column of times that increase every day. These time ranges are formatted in military time and viewed by the hour 6:00 - 7:00AM, 7:00 - 8:00AM, etc through 16:00. The root data is entered as (example) 6:53:07 AM

Each day these rows increase with new times, so for example the data that was in column B15 might be in B30 tomorrow. I am trying to find a way to track the Count of the number of entries for each hour spread, in other words how many events are found between 6 and 7, then 7 and 8, so forth and so on.

I have tried using COUNTIF but just cannot seem to get it right. Attached is an XLS that shows the data - column B is what I'm looking at. The counts in the row at the top D2:M2 are manual and I am trying to find a way to autopopulate these counts in the row as the data continues to be entered into the columns each day.

I did see a thread pointing to a format similar to this - =COUNTIF(B2:B500,>"6*")-COUNTIF(B2:B500,<"7*") but this failed. Have also tried doing a like COUNTIF(B2:B500,"6*") to get all of the counts for anything 6AM-related but that failed as well.

Any ideas on how I might achieve this result? Thanks so much for your time.


I am using OFFICE2013

Attached Files

For the forum.xlsx (18.6 KB, 14 views)