Date/Time Array Problem

[dotban]

Hi,

I have 2 columns of data. Column A has data in the format Date and Tim
and Column B has numbers.

Monday, June 21, 2004 12:00:01 AM 22
Monday, June 21, 2004 12:00:01 AM 45
Monday, June 21, 2004 12:00:01 AM 47
Monday, June 21, 2004 12:15:01 AM 23
Monday, June 21, 2004 12:15:01 AM 59
Monday, June 21, 2004 12:15:01 AM 62
Monday, June 21, 2004 12:30:01 AM 4
Monday, June 21, 2004 12:30:01 AM 19
Monday, June 21, 2004 12:30:01 AM 30
Monday, June 21, 2004 12:45:01 AM 44
Monday, June 21, 2004 12:45:01 AM 44
Monday, June 21, 2004 12:45:01 AM 116

 

[Bob Phillips]

Congratulations.

.... and the question is?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[dotban]

Hi,

The question is, why is it from 11:00 AM to 12:00 PM and onwards th
formula stops working

 

[Bob Phillips]

You don't make it easy do you? What formula is not working?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[dotban]

Hi,

In the original post I stated the ouptut. It looks like this:

(These are daily, 1 hour intervals)

Mon, June 21, 2004 12:00 AM Mon, June 21, 2004 01:00 AM 478
Mon, June 21, 2004 01:00 AM Mon, June 21, 2004 02:00 AM 516
Mon, June 21, 2004 02:00 AM Mon, June 21, 2004 03:00 AM 173
Mon, June 21, 2004 03:00 AM Mon, June 21, 2004 04:00 AM 425
Mon, June 21, 2004 04:00 AM Mon, June 21, 2004 05:00 AM 332
Mon, June 21, 2004 05:00 AM Mon, June 21, 2004 06:00 AM 640

This info is in Columns D, E and F


Column D is START TIME, Column E is STOP TIME and Column F has th
array formula from F2 to F169.

{=SUM(IF(DateTime>D2,IF(DateTime<=E2,ActualVolume)))}

 

[Bob Phillips]

What is ActualVolume?

Finally seeing the formula, it looks to me that the second DateTime should
actually be StopTime

=SUM(IF(DateTime>D2,IF(StopTime<=E2,ActualVolume)))

or

=SUM(IF(AND(DateTime>D2,StopTime<=E2),ActualVolume))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[dotban]

Hi,

Column A (date/time data) is DateTime and Column B (numbers) i
ActualVolume.

Columns D and E has my criteria which I need to extract from Columns
and B. Column F2 has the formula

=SUM(IF(DateTime>D2,IF(DateTime<=E2,ActualVolume)))
copied through F169.



I tried the formula

=SUM(IF(AND(DateTime>D2,DateTime<=E2),ActualVolume))

in cell F2 but it didn't work.

Any other suggestions, please

 

[dotban]

Hi,

Column A (date/time data) is DateTime and Column B (numbers) i
ActualVolume.

Columns D and E has my criteria which I need to extract from Columns
and B. Column F2 has the formula

=SUM(IF(DateTime>D2,IF(DateTime<=E2,ActualVolume)))
copied through F169.



I tried the formula

=SUM(IF(AND(DateTime>D2,DateTime<=E2),ActualVolume))

in cell F2 but it didn't work.

Any other suggestions, please