Forum Discussion
Need formula to sum the total duration of hours spent by an employee in a training
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
=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
but the cell is showing as the result with 00:00:00 so kindly let me know am i doing any wrong thing here
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)/3600Patrick2788 Wonderful the formula was perfect. Little more help required.
Attached excel sheet for your reference.
I have Main base data sheet ( refer tab 1 in attached sheet ) in one sheet and LOOKUP DATA SHEET in other sheet where i have unique employees data .
Now in LOOKUP DATA SHEET i have 4 columns ( EMP ID , EMP Name, Total no of days , Total Duration columns). I need to fetch each employee consolidated Total no of days & Total Duration hours by lookup the EMP ID from the Main data sheet tab).
Note : Total no of days - count should be only for the attended days excluding SAT & SUN
I think this will do it. I think one of the counts should be 9 instead of 7. Here it is:
- alternative to get those H,M,S columns you can use:
H: =IFERROR(LEFT(A1,SEARCH("h",A1)-1),0)
M: =IFERROR(--TRIM(MID(A1,SEARCH("m",A1)-2,2)),0)
S: =IFERROR(--TRIM(MID(A1,SEARCH("s",A1)-2,2)),0)
based on the original value in A1