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.

frances1265's avatar
frances1265
Copper Contributor
Jul 17, 2024

HansVogelaar 

Hello HansVogelaar, 

Hope you're well. Ive seen your work above, and was hoping you can assist in providing with a formula that would work for the format I have in https://docs.google.com/spreadsheets/d/1rhXorSRabhfnQirU-5f8eTpmZQE9zGXE/edit?usp=sharing&ouid=117244844375929167928&rtpof=true&sd=true. Essentially I want to calculate total hours scheduled in column M, this formula should take into account "OFF", "PTO". 

 

Thank you in advance for your assistance. 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 28, 2024

    frances1265 

    In M12:

    =SUM(IFERROR(TIMEVALUE(TEXTAFTER(D12:K12, " - "))-TIMEVALUE(TEXTBEFORE(D12:K12, " - ")), 0))

    Apply the custom number format [h]:mm to M12 to allow for total times over 24 hours.

    Copy M12 and paste to the cells below where you want a total.

    See attached version.

Resources