Forum Discussion

Jeanmentions's avatar
Jeanmentions
Copper Contributor
Mar 05, 2019

formula to calculate total time worked in a week.

Hello, I'm trying to write a formula to calculate how many hours were worked during the a week.  I either get an "value" message or 00:00. Can someone help me.

07:17
01:05
06:59
04:56
06:21
04:56
05:00
#VALUE!
  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Assuming your addends are in A1:A7, the formula for total hours is:
    =SUMPRODUCT(HOUR(A1:A7)+(MINUTE(A1:A7)/60))
    • Jeanmentions's avatar
      Jeanmentions
      Copper Contributor

      formula almost works, it did total to 13:36. when done on a calculator, it amounts to 35:15.  it seems we're almost there.

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Using my formula, the sum, in general format, 36.57 hours.
  • In general it works.

    the only result is to be formatted as elapsed time [hh]:mm

     

    It looks like in your case time is entered as text, that's why you have zero. You may test by =ISTEXT(A1)

     

    • Jeanmentions's avatar
      Jeanmentions
      Copper Contributor

      You're right I didn't realize that: =TEXT(D3-C3,"hh:mm"). how should it be written?

       

Resources