Forum Discussion

Clare76's avatar
Clare76
Copper Contributor
Jan 13, 2021
Solved

Excel - calculating hours worked on single cell values

Hi  I'm trying to figure out what I'm sure is a simple excel query. I need to calculate total weekly hours on a staff rota and the rota is formatted with each shift eg 09:00-17:00 in one single cell...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jan 13, 2021

    Clare76 

    In I3 as an array formula, confirmed with Ctrl+Shift+Enter:

     

    =SUM(IFERROR(MOD(TIMEVALUE(RIGHT(B3:H3,5))-TIMEVALUE(LEFT(B3:H3,5)),1),0))

     

    Apply the custom number format [h]:mm to I3, then fill down.

HansVogelaar's avatar
HansVogelaar
MVP
Jan 13, 2021

Clare76 

In I3 as an array formula, confirmed with Ctrl+Shift+Enter:

 

=SUM(IFERROR(MOD(TIMEVALUE(RIGHT(B3:H3,5))-TIMEVALUE(LEFT(B3:H3,5)),1),0))

 

Apply the custom number format [h]:mm to I3, then fill down.

tjforshort's avatar
tjforshort
Copper Contributor
Mar 04, 2022

HansVogelaar 

Hi Sir Hans,

I can't find a way to total the number work hours our employees.

I tried your given formula.

 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Mar 04, 2022

    tjforshort 

    Could you attach a sample workbook or make it available through OneDrive, Google Drive or similar?

    Or at the least, post a screenshot in which I can see the row numbers and column letters, and one of the formulas you're using.

    • socialrusty1987's avatar
      socialrusty1987
      Copper Contributor
      Jun 07, 2022
      Hi HansVogelaar you appear to be somewhat of an excel wizard can you help I tried the formula from above and it didn't appear to work.

      https://1drv.ms/x/s!AlkQovMKLEseoksLr_TY2vEd5noq?e=v8L3RS

      any chance you could help me with the file and show me how to do this in future

      thank you
      • V-GEe7's avatar
        V-GEe7
        Brass Contributor
        Jun 07, 2022

        socialrusty1987 This is the formula I came up with, it is pretty long so I have attached the file where I have the steps to what I did as well.

         

        =IFERROR(IF(((TIME(MID(D5,7,2),RIGHT(D5,2),0))-(TIME(LEFT(D5,2),MID(D5,4,2),0)))<0,(1+(TIME(MID(D5,7,2),RIGHT(D5,2),0))-(TIME(LEFT(D5,2),MID(D5,4,2),0))),((TIME(MID(D5,7,2),RIGHT(D5,2),0))-(TIME(LEFT(D5,2),MID(D5,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D6,7,2),RIGHT(D6,2),0))-(TIME(LEFT(D6,2),MID(D6,4,2),0)))<0,(1+(TIME(MID(D6,7,2),RIGHT(D6,2),0))-(TIME(LEFT(D6,2),MID(D6,4,2),0))),((TIME(MID(D6,7,2),RIGHT(D6,2),0))-(TIME(LEFT(D6,2),MID(D6,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D7,7,2),RIGHT(D7,2),0))-(TIME(LEFT(D7,2),MID(D7,4,2),0)))<0,(1+(TIME(MID(D7,7,2),RIGHT(D7,2),0))-(TIME(LEFT(D7,2),MID(D7,4,2),0))),((TIME(MID(D7,7,2),RIGHT(D7,2),0))-(TIME(LEFT(D7,2),MID(D7,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D8,7,2),RIGHT(D8,2),0))-(TIME(LEFT(D8,2),MID(D8,4,2),0)))<0,(1+(TIME(MID(D8,7,2),RIGHT(D8,2),0))-(TIME(LEFT(D8,2),MID(D8,4,2),0))),((TIME(MID(D8,7,2),RIGHT(D8,2),0))-(TIME(LEFT(D8,2),MID(D8,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D9,7,2),RIGHT(D9,2),0))-(TIME(LEFT(D9,2),MID(D9,4,2),0)))<0,(1+(TIME(MID(D9,7,2),RIGHT(D9,2),0))-(TIME(LEFT(D9,2),MID(D9,4,2),0))),((TIME(MID(D9,7,2),RIGHT(D9,2),0))-(TIME(LEFT(D9,2),MID(D9,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D10,7,2),RIGHT(D10,2),0))-(TIME(LEFT(D10,2),MID(D10,4,2),0)))<0,(1+(TIME(MID(D10,7,2),RIGHT(D10,2),0))-(TIME(LEFT(D10,2),MID(D10,4,2),0))),((TIME(MID(D10,7,2),RIGHT(D10,2),0))-(TIME(LEFT(D10,2),MID(D10,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D11,7,2),RIGHT(D11,2),0))-(TIME(LEFT(D11,2),MID(D11,4,2),0)))<0,(1+(TIME(MID(D11,7,2),RIGHT(D11,2),0))-(TIME(LEFT(D11,2),MID(D11,4,2),0))),((TIME(MID(D11,7,2),RIGHT(D11,2),0))-(TIME(LEFT(D11,2),MID(D11,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D12,7,2),RIGHT(D12,2),0))-(TIME(LEFT(D12,2),MID(D12,4,2),0)))<0,(1+(TIME(MID(D12,7,2),RIGHT(D12,2),0))-(TIME(LEFT(D12,2),MID(D12,4,2),0))),((TIME(MID(D12,7,2),RIGHT(D12,2),0))-(TIME(LEFT(D12,2),MID(D12,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D13,7,2),RIGHT(D13,2),0))-(TIME(LEFT(D13,2),MID(D13,4,2),0)))<0,(1+(TIME(MID(D13,7,2),RIGHT(D13,2),0))-(TIME(LEFT(D13,2),MID(D13,4,2),0))),((TIME(MID(D13,7,2),RIGHT(D13,2),0))-(TIME(LEFT(D13,2),MID(D13,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D14,7,2),RIGHT(D14,2),0))-(TIME(LEFT(D14,2),MID(D14,4,2),0)))<0,(1+(TIME(MID(D14,7,2),RIGHT(D14,2),0))-(TIME(LEFT(D14,2),MID(D14,4,2),0))),((TIME(MID(D14,7,2),RIGHT(D14,2),0))-(TIME(LEFT(D14,2),MID(D14,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D15,7,2),RIGHT(D15,2),0))-(TIME(LEFT(D15,2),MID(D15,4,2),0)))<0,(1+(TIME(MID(D15,7,2),RIGHT(D15,2),0))-(TIME(LEFT(D15,2),MID(D15,4,2),0))),((TIME(MID(D15,7,2),RIGHT(D15,2),0))-(TIME(LEFT(D15,2),MID(D15,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D16,7,2),RIGHT(D16,2),0))-(TIME(LEFT(D16,2),MID(D16,4,2),0)))<0,(1+(TIME(MID(D16,7,2),RIGHT(D16,2),0))-(TIME(LEFT(D16,2),MID(D16,4,2),0))),((TIME(MID(D16,7,2),RIGHT(D16,2),0))-(TIME(LEFT(D16,2),MID(D16,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D17,7,2),RIGHT(D17,2),0))-(TIME(LEFT(D17,2),MID(D17,4,2),0)))<0,(1+(TIME(MID(D17,7,2),RIGHT(D17,2),0))-(TIME(LEFT(D17,2),MID(D17,4,2),0))),((TIME(MID(D17,7,2),RIGHT(D17,2),0))-(TIME(LEFT(D17,2),MID(D17,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D18,7,2),RIGHT(D18,2),0))-(TIME(LEFT(D18,2),MID(D18,4,2),0)))<0,(1+(TIME(MID(D18,7,2),RIGHT(D18,2),0))-(TIME(LEFT(D18,2),MID(D18,4,2),0))),((TIME(MID(D18,7,2),RIGHT(D18,2),0))-(TIME(LEFT(D18,2),MID(D18,4,2),0))))*24,0)+IFERROR(IF(((TIME(MID(D19,7,2),RIGHT(D19,2),0))-(TIME(LEFT(D19,2),MID(D19,4,2),0)))<0,(1+(TIME(MID(D19,7,2),RIGHT(D19,2),0))-(TIME(LEFT(D19,2),MID(D19,4,2),0))),((TIME(MID(D19,7,2),RIGHT(D19,2),0))-(TIME(LEFT(D19,2),MID(D19,4,2),0))))*24,0)

         

         

        Hopefully this is what you were looking for

Resources