Count the Time in excel columns

Closed
sindhuG Posts 1 Registration date Wednesday November 3, 2010 Status Member Last seen November 3, 2010 - Nov 3, 2010 at 06:37 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 4, 2010 at 08:21 AM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Nov 4, 2010 at 08:21 AM
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
0