Forum Discussion

Jeev125's avatar
Jeev125
Copper Contributor
Jul 21, 2020

Excel Start & Finish Time with with Operating Hrs each day

Hi All,   Need a solution for the below. i have start and finish time but it expands to approx 8-9 days. how do i calculate total hrs with only operating hrs which starts from 4:20 am and finish at...
excel
Jeev125's avatar
Jeev125
Copper Contributor
Jul 21, 2020

Hi mtarler 

 

If i take difference between start & finish time which is around ~189 as stated in table (Total), but if exclude non operating hrs from start to finish to be exact i get.

176:54:50

i get the actual value when i do calc for each day and sum. 

what i am trying to achieve to get formula in single cell to dictate the working hrs only in compliance with Operating Hrs fro each day.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 22, 2020

Jeev125 

Perhaps like this

if I understood the logic correctly (can't receive 175 manually as in the grey range).

In shifts range Finish is the time on next day, i.e. =25/24 fo Sun, etc. Duration=Finish-Start.

Formula for operating time

=SUM(INDEX($I$3:$I$9,WEEKDAY(SEQUENCE(INT(C4)-INT(B4)-1,1,INT(B4)+1),1)))+
INDEX($H$3:$H$9,WEEKDAY($B$4,1))-MOD(B4,1)+
MOD(C4,1)-INDEX($G$3:$G$9,WEEKDAY($C$4,1))

assuming your version of Excel supports dynamic arrays.

  • Jeev125's avatar
    Jeev125
    Copper Contributor
    Aug 05, 2020

    SergeiBaklan 

     

    Your Formula works only if there is more 2 day difference if its one day then it shows Clac Error and if its sameday then it shows Value Error.

     

    Could you please help me with the issue.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 06, 2020

      Jeev125 

      Please check the update, formula is

      =IF(INT($B4)=INT($C4),$C4-$B4,
IFERROR(SUM(INDEX($I$3:$I$9,WEEKDAY(SEQUENCE(INT(C4)-INT(B4)-1,1,INT(B4)+1),1))),0)+
INDEX($H$3:$H$9,WEEKDAY($B$4,1))-MOD(B4,1)+
MOD(C4,1)-INDEX($G$3:$G$9,WEEKDAY($C4,1)))

Resources