Calculating Time Durations in Hours, Minutes, Seconds in Excel

[jbonds]

I need to calculate Hours, Minutes, Seconds from date/time stamps to find the duration then from the duration find the average. I've attached a screen shot for reference with all of the results coming out at hh:mm:ss 

Order Date time:

01 02:23:47

<day> <hour>:<minute>:<second>

Order Delivery Time:

<day> <hour>:<minute>:<second>

There would be 3 equations that would need to be averaged

1. Placed Order with vendor datetime (minus) customer place order datetime = duration

2. Trucker at Vendor datetime (minus) Placed order with vendor datetime = duration

3. Delivered to customer datetime (minus) Trucker at Vendor datetime = duration

Then all 3 of these I would need to average separately. Hopefully that makes sense. 

Greatly appreciate any and all help!

 

[#2 · Accepted solution]

If you just need the average time, you can just write the formula in E2 as below.

=TEXT((DATEVALUE(LEFT(D2,FIND(" ",D2)-1)&"-Jan-1900")+TIMEVALUE(RIGHT(D2,LEN(D2)-FIND(" ",B2)))-DATEVALUE(LEFT(A2,FIND(" ",A2)-1)&"-Jan-1900")-TIMEVALUE(RIGHT(A2,LEN(A2)-FIND(" ",A2))))/3,"[hh]:mm:ss")

[#3]

@Starrysky1988
Thanks again for the quick response you're my hero today!!! I might not of articulated this properly I would need the durations for 3 different durations (columns F,G,H) and then be able to average them in cells F12, G12, and H12.

[#4]

If you want to get the individual result, you may need to calculate the difference for D-C, C-B and B-A separately.
The logic here for average of 3 results is as below.
(D-C+C-B+B-A)/3=(D-A)/3

[#5]

if those are actual date/time values in excel you can just do B2-A2
if those are text (which I suspect) you can use Starrysky's formula or based on your explicit format I think this should work also:
=(NUMBERVALUE(LEFT(C3,2))+TIMEVALUE(MID(C3,4,8))) -
(NUMBERVALUE(LEFT(B3,2))+TIMEVALUE(MID(B3,4,8)))
then you highlight columns F:H and go to HOME -> more number formats and under TIME select the [HH]:MM:SS version which will display like 00:07:19
then for average you just use AVERAGE( range )

[#6]

@Starrysky1988
Perfect! Thank you Thank you!

[#7]

@mtarler @Starrysky1988
Thank you both!!!

[#8]

@jbonds 

If with newly introduced for Insiders functions that could be

=SUM( TEXTSPLIT(B1, " ") - TEXTSPLIT(A1, " ") )

with applying dd hh:mm:ss format