Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question: How many occurences in date range?
Good afternoon
Col A has a list of dates and times. The data looks like this: 08/09/09 08:12 08/09/09 09:15 08/10/09 07:59.. etc. Each date/time represents one transaction. We need to count the number of transactions occurring on a each day between two times. For example, on 8/09/09 between 8:00 and 9:59 the answer would be 2. Can you give me some ideas about how to approach this problem? We don't want to script this, but will use a separate cell on a sheet for each date and time range. Thanks in advance for your help. -DL |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question: How many occurences in date range?
Hi
First you need to split and times using 'Text To Columns' with 'space' as delimitter. Then use use this formula to calculate occurences in range: =SUMPRODUCT(--(A1:A3=DATEVALUE("08-09-2009")),--(B1:B3=TIMEVALUE ("08:00")),--(B1:B3<TIMEVALUE("10:00"))) Regards, Per On 8 Sep., 22:58, dlowrey wrote: Good afternoon Col A has a list of dates and times. *The data looks like this: 08/09/09 08:12 08/09/09 09:15 08/10/09 07:59.. etc. Each date/time represents one transaction. We need to count the number of transactions occurring on a each day between two times. *For example, on 8/09/09 between 8:00 and 9:59 the answer would be 2. Can you give me some ideas about how to approach this problem? *We don't want to script this, but will use a separate cell on a sheet for each date and time range. Thanks in advance for your help. -DL |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question: How many occurences in date range?
"dlowrey" wrote:
We need to count the number of transactions occurring on a each day between two times. Suppose your data is in column A of Sheet1, in column A and column B on Sheet2, you have the list of dates and time ranges to be counted. 8/9/09 8:00 8/9/09 10:00 8/9/09 9:00 8/9/09 12:00 ....etc... Then in column C of Sheet2, you would enter the following formula and copy down: =sumproduct( (A1 <= Sheet1!$A$1:$A$100) * (Sheet1!$A$1:$A$100 < B1) ) Note: Generally, it is better test for "less than" some upper time limit than to test for "less or equal to" some time minus one minute. This has to do with the internal form in which Excel stores date/time information. However, if you enter date/time information manually, it is not a problem, and "less than or equal" would work just as well. ----- original message ----- "dlowrey" wrote in message ... Good afternoon Col A has a list of dates and times. The data looks like this: 08/09/09 08:12 08/09/09 09:15 08/10/09 07:59.. etc. Each date/time represents one transaction. We need to count the number of transactions occurring on a each day between two times. For example, on 8/09/09 between 8:00 and 9:59 the answer would be 2. Can you give me some ideas about how to approach this problem? We don't want to script this, but will use a separate cell on a sheet for each date and time range. Thanks in advance for your help. -DL |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question: How many occurences in date range?
Thanks guys for the answers.
I think you all are saying that I need to first transform a date-time value stored in a cell into into 2 text values in two additional columns, one for date, one for time. This seems a little strange. If the date/time in a cell is a unique number (I understand that dates are actually stored as integers incremented by seconds), then I would think that there must be some way to compare them directly. Thanks for adding some clarification. -DL "JoeU2004" wrote: "dlowrey" wrote: We need to count the number of transactions occurring on a each day between two times. Suppose your data is in column A of Sheet1, in column A and column B on Sheet2, you have the list of dates and time ranges to be counted. 8/9/09 8:00 8/9/09 10:00 8/9/09 9:00 8/9/09 12:00 ....etc... Then in column C of Sheet2, you would enter the following formula and copy down: =sumproduct( (A1 <= Sheet1!$A$1:$A$100) * (Sheet1!$A$1:$A$100 < B1) ) Note: Generally, it is better test for "less than" some upper time limit than to test for "less or equal to" some time minus one minute. This has to do with the internal form in which Excel stores date/time information. However, if you enter date/time information manually, it is not a problem, and "less than or equal" would work just as well. ----- original message ----- "dlowrey" wrote in message ... Good afternoon Col A has a list of dates and times. The data looks like this: 08/09/09 08:12 08/09/09 09:15 08/10/09 07:59.. etc. Each date/time represents one transaction. We need to count the number of transactions occurring on a each day between two times. For example, on 8/09/09 between 8:00 and 9:59 the answer would be 2. Can you give me some ideas about how to approach this problem? We don't want to script this, but will use a separate cell on a sheet for each date and time range. Thanks in advance for your help. -DL |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question: How many occurences in date range?
"dlowrey" wrote:
I think you all are saying that I need to first transform a date-time value stored in a cell into into 2 text values in two additional columns, one for date, one for time. This seems a little strange. I concur. I, for one, did __not__ say you need to first make a transformation. I would think that there must be some way to compare them directly. My formula does exactly that. I think the formulas posted by "p45cal" are similar. ----- original message ----- "dlowrey" wrote in message ... Thanks guys for the answers. I think you all are saying that I need to first transform a date-time value stored in a cell into into 2 text values in two additional columns, one for date, one for time. This seems a little strange. If the date/time in a cell is a unique number (I understand that dates are actually stored as integers incremented by seconds), then I would think that there must be some way to compare them directly. Thanks for adding some clarification. -DL "JoeU2004" wrote: "dlowrey" wrote: We need to count the number of transactions occurring on a each day between two times. Suppose your data is in column A of Sheet1, in column A and column B on Sheet2, you have the list of dates and time ranges to be counted. 8/9/09 8:00 8/9/09 10:00 8/9/09 9:00 8/9/09 12:00 ....etc... Then in column C of Sheet2, you would enter the following formula and copy down: =sumproduct( (A1 <= Sheet1!$A$1:$A$100) * (Sheet1!$A$1:$A$100 < B1) ) Note: Generally, it is better test for "less than" some upper time limit than to test for "less or equal to" some time minus one minute. This has to do with the internal form in which Excel stores date/time information. However, if you enter date/time information manually, it is not a problem, and "less than or equal" would work just as well. ----- original message ----- "dlowrey" wrote in message ... Good afternoon Col A has a list of dates and times. The data looks like this: 08/09/09 08:12 08/09/09 09:15 08/10/09 07:59.. etc. Each date/time represents one transaction. We need to count the number of transactions occurring on a each day between two times. For example, on 8/09/09 between 8:00 and 9:59 the answer would be 2. Can you give me some ideas about how to approach this problem? We don't want to script this, but will use a separate cell on a sheet for each date and time range. Thanks in advance for your help. -DL |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of occurences within a time range | Excel Discussion (Misc queries) | |||
SUMPRODUCT to find occurences within date range | Excel Discussion (Misc queries) | |||
How to count the occurences in my range with 2 characters only? | Excel Worksheet Functions | |||
counting occurences in data range (e.g 31-60) | Excel Worksheet Functions | |||
How do I count occurences in a date range? | Excel Worksheet Functions |