Question: How many occurences in date range?

[dlowrey]

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

 

[Per Jessen]

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

 

[p45cal]

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 da
between
two times. For example, on 8/09/09 between 8:00 and 9:59 the answe
would be
2.

Can you give me some ideas about how to approach this problem? W
don't
want to script this, but will use a separate cell on a sheet for eac
date
and time range.

Thanks in advance for your help.
-DL

Try

Code
-------------------
=SUMPRODUCT(--(A2:A4>D2),--(A2:A4<D3)
-------------------

where
A2:A4 contains your list of dates (only three in your example)
D2 The earlier of the two date/time thresholds
D3 The later of the two date/time thresholds

This does really give the number of occurences *between *the two date
and does not count transactions *at *the two times, which can b
adjusted for with
Code

 

[JoeU2004]

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]

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]

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