Frequency of data occurence in Excel Column based on the certain condition

Question

I have a column (Table 1 below) with time data in 24 hour format saved as text.I want Table 2 to be populated by the hour and adding all the occurrences for the hour.

So one occurrence is at 0102. This falls within an hor range of 12:30am to 01:30pm. I want the count to be 1 for 01:00. If an occurrence falls between 01:30 to 02:30 I want the count to be 1 for 02:00. Also if time is 04:55 it should show count for 05:00 as 1. Basically rounding to the closest time and showing a count for that. It should round of to the nearest time and add the number of occurrences also. Thats why 06:00 is 2. to account for 0622 and 0614.

For example:

TABLE 1                   TABLE2 (Expected results)
Column A                  Column A     Column B
0102                      01:00        1 
0222                      02:00        2
0223                      03:00        0
0455                      04:00        0
0622                      05:00        1
0614                      06:00        2

NOTE In B1 we get 1 because value in A1 of TABLE1 is 0102 and falls between 12:30 and 01:30, thus represented by 01:00 in A1 in TABLE 2. We have two values in Table1 between 01:30 and 02:30: one is in A2 and the second is in A3, so we expect 2 in B2 in TABLE 2, the time range which is represented by 02:00 in A2. We attribute 0 to cells B3 and B4 in Table2 because there isn't any data in Table1 between 02:30-03:30 and 03:30-04:30, therefore they get 0 in their corresponding cells in Table2. However, we have two instances of data, 0622 and 0612 in Table1 which fall between 05:30 and 06:30, so value 2 is attributed to B6.

How do I do this using a formula?

Please help.

Answer 1

So after struggling to understand what was going on, here is the simple solution:

first let me clarify again:
column A: but pasted as text originally and in hhmm format, but you need to reformat them as simple numbers, so you will lose those 0 s at the beginning of certain data.
column B: we put down our time ranges, as you said, starting from 12:30 AM, so i put it down as 0030 of which the zeros are not visible anymore in number format. This range starts from 30 and increments by 100, until it stops at 2330 instead of going to 2430. this will be reflected in the formula for the last cell in column D.
column C: the hour ranges against which we round and count the data in A.
column D: we put down the following formula in D2 :

=COUNTIFS(A:A,">"&$B2,A:A,"<"&$B3)

and drag and fill down until the last but one cell. for the last cell D25 we change the greater/smaller than signs, and use the following formula (only for this cell):

=COUNTIFS(A:A,"<"&$B25,A:A,"<"&$B2)

Try these formulas, if it doesn't work, download the example sheet, here is the example sheet i created for this purpose, downloadable from here
and if you still have questions, don't hesitate to put them in the commentaries.