Counting event times
 

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.

Counting event times
 

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")))

--
HTH,
Bernie
MS Excel MVP


"wally" wrote in message
...
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.

Counting event times
 

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" wrote:

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")))

--
HTH,
Bernie
MS Excel MVP


"wally" wrote in message
...
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.


.

Counting event times
 

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" wrote in message
...
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" wrote:

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")))

--
HTH,
Bernie
MS Excel MVP


"wally" wrote in message
...
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.


.

Counting event times
 

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" wrote:

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" wrote in message
...
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" wrote:

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")))

--
HTH,
Bernie
MS Excel MVP


"wally" wrote in message
...
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.


.


.

Counting event times
 

Good catch - sorry for the typo.

Bernie


"wally" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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")))

--
HTH,
Bernie
MS Excel MVP


"wally" wrote in message
...
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.


.


.