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
    Mar 24, 2021

    Clare76 

    In AL3:  =COUNTIF(C3:AJ3,"Holiday")*8/24

    In AM3:  =SUM(AK3:AL3)

    • Clare76's avatar
      Clare76
      Copper Contributor
      Mar 26, 2021
      Hans, thanks once again for your excel wizardry, this is perfect. Your knowledge has saved me a lot of time with staff admin, much appreciated! Clare