Forum Discussion

Onlinehelp's avatar
Onlinehelp
Copper Contributor
May 22, 2021
Solved

Total hours between two time ranges

Hello,   I would like to find the total hours between 7:30 am to 12:30 pm on same day in excel. TIME TOTAL HOURS 7:30 to 12:30 ?   Please guide. Thank you so much.
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    May 22, 2021

    Onlinehelp 

    As variant

    with

    =SUM(
   IFERROR(
     MOD(RIGHT(C7:I7, LEN(C7:I7)-SEARCH("-",C7:I7)) -
         LEFT(C7:I7, SEARCH("-",C7:I7)-1),
     0.5),0))

    but it very depends on do you have overnight shifts or not and do you keep exactly the same format for all time periods.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 22, 2021

Onlinehelp 

Forgot to say above is for Excel which supports dynamic arrays, otherwise array formula with Ctrl+Shift+Enter shall be used.

  • Onlinehelp's avatar
    Onlinehelp
    Copper Contributor
    May 22, 2021

    SergeiBaklan Getting zero for me. It is excel home and student 2019 edition. Thank you for the help.

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 22, 2021

      Onlinehelp 

      2019 doesn't support dynamic arrays. I have no machine with it to test right now, please try to enter formula as array one. Instead of Enter use Ctrl+Shift+Enter. {} shall appear around the formula.

Resources