Solved: Average Difference with Date:Time Format

[JimS]

I have a column of Time Stamps that are formated with mm/dd/yyyy hh:mm:ss.

The Time Stamps start in cell A4 and can have varying number of entries in column A.

I need to determine 2 things:

1) What is the average difference for all the Time Stamps. I'm trying to detemine what the average time in seconds is between all the Time Stamps (ie: the Time stamps can be around 15 or 20 or ?? second - on average a part - I need to know what the actual average interval is of all the entries).

2) In Hours how long did this process run (ie: Last Time Stamp minus the First Time Stamp - rounded down to the nearst whole number).

This can be figured with a formula or vba, whatever is easier.

I have included an example of the Time Stamps.

Thanks...

Jim

[Bob Phillips]

Try these

=AVERAGE(INT(MOD(MOD(A4:A5000,1)*24*60*60,60)))

an array formula,

and

=ROUND((MAX(A:A)-MIN(A:A))*24,0)

[JimS]

The ROUND works but the Average doesn't seem correct. I tested it in a smaller sampling of data and it resulted in an average of 35 but it should be 19.

[Bob Phillips]

It seems okay to me, although it could be a bit better

=AVERAGE(ROUND(MOD(MOD(A4:A7,1)*24*60*60,60),1))

What range did you try on?

[JimS]

The formula on just these 2 cells results in 30.5.

'=AVERAGE(ROUND(MOD(MOD(A6:A7,1)*24*60*60,60),1))

The difference of these 2 Cells is only 19 seconds.

A6 = 06/16/2009 09:58:21

A7 = 06/16/2009 09:58:40

[Bob Phillips]

I think I mis-read it. Do you want the average of the difference of row 4 and 3, and the difference of row 5 and 4, and so on?

[JimS]

I think the way you are describing this is correctly, I need the Average difference between all the rows.
It would need to determine the difference between rows 4 & 5, and then 5 & 6, and then 6 & 7, and then 7 & 8, and so on to the last row in column A and then figure what the Average Difference is.


Sorry if I was clear...

[Bob Phillips]

What happens if say

A4: 17/06/2009 13:34:40
A5: 18/06/2009 13:34:21

are we adding -19 to the average then?

[JimS]

Did you mean for A5 to be 18/06/2009 or 17/06/2009?

If it's 17/06/2009 then yes 19 (seconds) is the difference.
If it's 18/06/2009 then the difference is much more (19 seconds + 24 hours - a whole day).

The difference in the data intervals for the most part will very close to each other.

Also, it might be easier to look at as the difference from 5 & 4, then 6 & 5, then 7 & 6, then 8 & 7, then 9 & 8, etc because each rows Time Stamps occurs after the previous row.

[Bob Phillips]

Aah, I thought you were only interested in the seconds, I see you now wnat the difference in seconds.

Try this array formula

=AVERAGE(IF(A5:A5000<>"",A5:A5000-A4:A4999))*24*60*60

[JimS]

You got it - Thanks...