Forum Discussion

Vijayvr's avatar
Vijayvr
Copper Contributor
Dec 06, 2023
Solved

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 ...
excel
  • Patrick2788's avatar
    Patrick2788
    Dec 06, 2023

    Vijayvr 

    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

     

mtarler's avatar
mtarler
Silver Contributor
Dec 06, 2023
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
  • Vijayvr's avatar
    Vijayvr
    Copper Contributor
    Dec 06, 2023
    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 here
    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Dec 06, 2023

      Vijayvr 

      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

       

      • Vijayvr's avatar
        Vijayvr
        Copper Contributor
        Dec 07, 2023

        Patrick2788 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

Resources