Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. . . |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting how many times something happens in a week | Excel Discussion (Misc queries) | |||
How to generate same event (has 2 possible outcomes) 200 times? | Excel Discussion (Misc queries) | |||
Counting number of times a value occurs | Excel Discussion (Misc queries) | |||
Counting the times a number appears | Excel Discussion (Misc queries) | |||
Counting cells with a specified frequency(how many times it appear | Excel Discussion (Misc queries) |