Forum Discussion
Need formula to sum the total duration of hours spent by an employee in a training
Can any one give a formula to how to sum of all the below durations into consolidated duration in HH MM SS.
1h 41m 46s
38s
2h 3m 23s
2h 2m 45s
2h 43m 30s
3h 44m 10s
3h 15m 54s
14m 24s
3h 20m 12s
With Excel 2016, dynamic arrays and the newer text functions are not available. Unfortunately, Excel 2016 does not do elegance!
It really depends on how much data you have but if I were you I'd fix the data up a bit first before using any formulas.
Then use the ol' workhorse SUMPRODUCT:
=SUMPRODUCT({3600,60,1},E7:G7)/3600
8 Replies
- Try:
=SUM(TIME(IFERROR(TEXTBEFORE(A1:A9,"h"),0),IFERROR(TEXTBEFORE(IFERROR(TEXTAFTER(A1:A9,"h "),A1:A9),"m"),0),IFERROR(TEXTBEFORE(IFERROR(TEXTAFTER(A1:A9,"m "),A1:A9),"s"),0)))
and using the number formatting options you can choose [H]:MM:SS- Hi Thanks for the response . I am using excel 2016 and my cells in excel sheet are in E7:E15 and i replaced A1:A9 in the formula with E7:E15 and i formatted the formula cell to [H]:MM:SS by using CTL+1 >> Under Number tab >> Custom >> Changed to [H]:MM:SS.
but the cell is showing as the result with 00:00:00 so kindly let me know am i doing any wrong thing hereWith Excel 2016, dynamic arrays and the newer text functions are not available. Unfortunately, Excel 2016 does not do elegance!
It really depends on how much data you have but if I were you I'd fix the data up a bit first before using any formulas.
Then use the ol' workhorse SUMPRODUCT:
=SUMPRODUCT({3600,60,1},E7:G7)/3600
