Count the Time in excel columns

Closed
Report
Posts
1
Registration date
Wednesday November 3, 2010
Status
Member
Last seen
November 3, 2010
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I have a requirement, in a column the time is displayed as below, now i need for the date 10/9/2010 i need the count of B2 for every one hour eg: 4 records during time interval 10:00:00 to 10:59:59
A2 B2
10/9/2010 12:00:00 AM
10/9/2010 10:00:22 PM
10/9/2010 10:02:28 PM
10/9/2010 10:26:32 PM
10/9/2010 10:32:28 PM
10/9/2010 11:22:28 PM
10/9/2010 11:32:28 PM
10/9/2010 11:42:28 PM
10/9/2010 11:52:28 PM
10/10/2010 12:00:23 AM
10/10/2010 12:01:10 AM
10/10/2010 12:01:24 AM
10/10/2010 12:01:25 AM
10/10/2010 12:01:35 AM
10/10/2010 12:42:28 AM
10/10/2010 12:52:28 AM

please help me.

Regards,
sindhuG.

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
one way of doing this
suppose the data is like this from the second row.(that is from A2 B2)
in F1 enter 10/9/10
in G1 enter 10:00:00 PM
inH1 enter 10:59:59 PM
(this is only for writing the formula easily)

In an empty cell copy this formula

=SUMPRODUCT(((A2:A17+B2:B17)>=F1+G1)*((A2:A17+B2:B17)<=F1+H1))

you will get 4