07-21-2020 04:02 PM
07-21-2020 04:02 PM
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 next day 1 or 2 am.
In below example i need actual value as my end result cant go with total which includes non operating hrs.
|15-06-2020 11:59:05 AM||23-06-2020 09:06:05 AM||189:07:00||Sunday||4:20||1:00|
07-21-2020 05:04 PM
@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.
07-21-2020 05:15 PM
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.
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.
07-22-2020 07:11 AM
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.
08-05-2020 09:33 PM
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.
08-06-2020 03:10 AM
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)))
08-06-2020 05:54 PM
You are genius, but i found a small flaw, I have attach the attachment for you. Please let me know.