Forum Discussion
Solved
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 comin...
- 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")
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")
=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 ) - 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- Starrysky1988
Perfect! Thank you Thank you!
