date/time average

[Hookette]

I have two columns and in each column I have a date and time.

A1 has 7/22/09 13:31
B1 has 7/29/09 14:53

I need to know the average length of time it took to get from A1 to B1 (if
it's possible) Basically A1 is when something was submitted and B1 is when
someone actually worked on it. Need to know the average time it took to get
it worked on.

Thanks.

 

[Jacob Skaria]

In Column C enter formula
=B1-A1
and custom format to [h]:mm:ss

Once done apply the AVERAGE() formula, Make sure this cell also is formatted
to [h]:mm:ss

If this post helps click Yes

 

[Jacob Skaria]

With out the helper column you can use the below array formula. Make sure all
entries in the range a1:B4 are filled with valid date/time entries..Please
note that this is an array formula. Within the cell in edit mode (F2) paste
this formula and press Ctrl+Shift+Enter to apply this formula. If successful
in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

Col A Col A Col C
7/22/2009 13:31 7/22/2009 15:31 2:00:00
7/22/2009 15:31 7/22/2009 15:51 0:20:00
7/22/2009 15:31 7/22/2009 17:35 2:04:00
7/22/2009 15:31 7/22/2009 18:31 3:00:00

Average =AVERAGE(C1:C4)

OR

Array formula =AVERAGE(B1:B4-A1:A4)


If this post helps click Yes