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 with newly introduced for Insiders functions that could be
=SUM( TEXTSPLIT(B1, " ") - TEXTSPLIT(A1, " ") )with applying dd hh:mm:ss format