Forum Discussion
Calculating Time Durations in Hours, Minutes, Seconds in Excel
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!
- 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")
7 Replies
If with newly introduced for Insiders functions that could be
=SUM( TEXTSPLIT(B1, " ") - TEXTSPLIT(A1, " ") )with applying dd hh:mm:ss format
- 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 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 )
