Counting events between times

Hi guys,
i have list of events that are time stamped. I need a formula within excel to automatically count the no. of events between, for example, 10:00 and 11:00 etc. Being a newbie, i used the countif formula,example,
=COUNTIF(D:D,"<01/01/2005 01:00")
and the same for the next hour and deducted the result. However, this is very time consuming when i have to do it for each hour, so i need something much faster and easier. Hope i get an answer soon.
Thanx

Try the following...

=SUMPRODUCT(--(D1:D10>="10:00 AM"+0),--(D1:D10<="11:00 AM"+0))

OR

=SUMPRODUCT(--(D1:D10>=TIME(10,0,0)),--(D1:D10<=TIME(11,0,0)))

OR

=SUMPRODUCT(--(D1:D10>=A1),--(D1:D10<=B1))

...where A1 contains your start time and B1 contains your end time.

Hope helps!

Originally Posted by monty_mm

Hi guys,
i have list of events that are time stamped. I need a formula within excel to automatically count the no. of events between, for example, 10:00 and 11:00 etc. Being a newbie, i used the countif formula,example,
=COUNTIF(D:D,"<01/01/2005 01:00")
and the same for the next hour and deducted the result. However, this is very time consuming when i have to do it for each hour, so i need something much faster and easier. Hope i get an answer soon.
Thanx

Hi again,
thanks for ur reply Dominic. I tried those formulas but they didnt work. Also, i need to specify the date as well, not only the time. The formula u supplied also wont help me much because i'll have to type it for each date and time. For example, if i have a list of events occurring by the hour between 01/01/2005 and 07/01/2005, and i need to count the no. of events occurring in each hour in each of these days, typing a formula like that is going to be hectic. Auto fill does not work properly when i tried. By the way, all the dates/times are in one column, for example A1:A6000 and not in other columns. Hope i get an easy solution soon. Thanks for ur earlier reply anyway.

With Column A containing your date/time, enter the hourly intervals in two columns, let's say Column C and D, as follows...

Please Login or Register  to view this content.

Then, enter the following formula in E1 and copy down:

=SUMPRODUCT(--($A$1:$A$6000>=C1),--($A$1:$A$6000<D1))

Hope this helps!

Originally Posted by monty_mm

Hi again,
thanks for ur reply Dominic. I tried those formulas but they didnt work. Also, i need to specify the date as well, not only the time. The formula u supplied also wont help me much because i'll have to type it for each date and time. For example, if i have a list of events occurring by the hour between 01/01/2005 and 07/01/2005, and i need to count the no. of events occurring in each hour in each of these days, typing a formula like that is going to be hectic. Auto fill does not work properly when i tried. By the way, all the dates/times are in one column, for example A1:A6000 and not in other columns. Hope i get an easy solution soon. Thanks for ur earlier reply anyway.

Domenic,
thanx a zillion...ur formula worked perfect...cheers