SOLVED

Calculating Time Durations in Hours, Minutes, Seconds in Excel

Occasional Contributor

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!

 

Screen Shot 2022-03-23 at 3.29.29 PM.png

7 Replies
best response confirmed by jbonds (Occasional Contributor)
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")

@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.
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
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 )

@jbonds 

If with newly introduced for Insiders functions that could be

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

with applying dd hh:mm:ss format