Forum Discussion
Excel Start & Finish Time with with Operating Hrs each day
Jeev125 I would say you take the total and subtract the #day * the down time but saying 1 or 2 am makes it impossible to know unless there is a complete list of hours each day or something. Furthermore your example doesn't follow your criteria. If there were 8 days and you are shutdown for at least 2am - 4:20am that is >2hrs a day or more than 16hours over 8 days which means the actual hours has to be LESS than 173 hrs and really a max of about 170 hrs but your "actual total" of ~176 hrs is MORE than that which doesn't make sense with the other data/info you gave. We, or at least I, need more clarification.
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.
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.
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.
