Counting event times

[wally]

In a workbook, dates and times for events are kept in columns, each column
representing the number times the events have occurred for the item in that
row. In this workbook the item in the row would be an address.

I need to break down the entered data by the hour such as 09:00, 10:00,
11:00, etc. The following is an example of the entries:

5/24 12:57 7/26 08:28
5/17 02:02 6/14 01:33 9/20 03:50
4/18 17:52 9/8 16:15 10/30 20:51 11/11 08:56
7/3 21:16
1/4 08:12 3/13 06:05 8/7 00:11 9/9 03:33

Thank you for your suggestions.

 

[Bernie Deitrick]

Wally,

For example, to find times between 9 aand 10 AM:

=SUMPRODUCT((A2:G8-(INT(A2:G8))>TIMEVALUE("9:00:00"))*(A2:G8-(INT(A2:G8))>=TIMEVALUE("10:00:00")))

 

[wally]

I'm sorry, my request was not clear. What I meant to ask for was to count
the number of events occurring in all the rows and columns for times between
00:00 and 00:59, between 01:00 and 01:59, and so on through 23:00 and 23:59.
(An event is indicated by a date and time) This would be used to create a
new table in the worksheet showing the number of events for each hour. I
hope this better clarifies the requirment.

I had previously attempted to use COUNTIF unsuccessfully.

 

[Bernie Deitrick]

That is what my formula does. Change the A2:G8 (in all four positions) to
include the range that holds the dates, and precede it by the sheet name,
like

Data!A2:Z1000

and it will return the count of events in the time frame as indicated.

Bernie
MS Excel MVP

 

[wally]

Ok, it did work after I revised the end of the formula from
.....>=TIMEVALUE("10:00:00"))) to read ....<=TIMEVALUE("10:00:00"))).

Thanks.

 

[Bernie Deitrick]

Good catch - sorry for the typo.

Bernie

Ok, it did work after I revised the end of the formula from
....>=TIMEVALUE("10:00:00"))) to read ....<=TIMEVALUE("10:00:00"))).

Thanks.