Excel Start & Finish Time with with Operating Hrs each day

%3CLINGO-SUB%20id%3D%22lingo-sub-1537320%22%20slang%3D%22en-US%22%3EExcel%20Start%20%26amp%3B%20Finish%20Time%20with%20with%20Operating%20Hrs%20each%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1537320%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENeed%20a%20solution%20for%20the%20below.%20i%20have%20start%20and%20finish%20time%20but%20it%20expands%20to%20approx%208-9%20days.%20how%20do%20i%20calculate%20total%20hrs%20with%20only%20operating%20hrs%20which%20starts%20from%204%3A20%20am%20and%20finish%20at%20next%20day%201%20or%202%20am.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20below%20example%20i%20need%20actual%20value%20as%20my%20end%20result%20cant%20go%20with%20total%20which%20includes%20non%20operating%20hrs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22696%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22148%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22148%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EStart%3C%2FTD%3E%3CTD%3EFinish%3C%2FTD%3E%3CTD%3ETotal%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EOperating%20HRs%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22148%22%3E15-06-2020%2011%3A59%3A05%20AM%3C%2FTD%3E%3CTD%20width%3D%22148%22%3E23-06-2020%2009%3A06%3A05%20AM%3C%2FTD%3E%3CTD%3E189%3A07%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ESunday%3C%2FTD%3E%3CTD%3E4%3A20%3C%2FTD%3E%3CTD%3E1%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EMonday%3C%2FTD%3E%3CTD%3E4%3A20%3C%2FTD%3E%3CTD%3E1%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EActual%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ETuesday%3C%2FTD%3E%3CTD%3E9%3A06%3C%2FTD%3E%3CTD%3E1%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E~176hrs%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EWednesday%3C%2FTD%3E%3CTD%3E4%3A20%3C%2FTD%3E%3CTD%3E1%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EThursday%3C%2FTD%3E%3CTD%3E4%3A20%3C%2FTD%3E%3CTD%3E1%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EFriday%3C%2FTD%3E%3CTD%3E4%3A20%3C%2FTD%3E%3CTD%3E2%3A40%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ESaturday%3C%2FTD%3E%3CTD%3E4%3A20%3C%2FTD%3E%3CTD%3E2%3A40%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1537320%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1537372%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Start%20%26amp%3B%20Finish%20Time%20with%20with%20Operating%20Hrs%20each%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1537372%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F735618%22%20target%3D%22_blank%22%3E%40Jeev125%3C%2FA%3E%26nbsp%3BI%20would%20say%20you%20take%20the%20total%20and%20subtract%20the%20%23day%20*%20the%20down%20time%20but%20saying%201%20or%202%20am%20makes%20it%20impossible%20to%20know%20unless%20there%20is%20a%20complete%20list%20of%20hours%20each%20day%20or%20something.%26nbsp%3B%20Furthermore%20your%20example%20doesn't%20follow%20your%20criteria.%20If%20there%20were%208%20days%20and%20you%20are%20shutdown%20for%20at%20least%202am%20-%204%3A20am%20that%20is%20%26gt%3B2hrs%20a%20day%20or%20more%20than%2016hours%20over%208%20days%20which%20means%20the%20actual%20hours%20has%20to%20be%20LESS%20than%20173%20hrs%20and%20really%20a%20max%20of%20about%20170%20hrs%20but%20your%20%22actual%20total%22%20of%20~176%20hrs%20is%20MORE%20than%20that%20which%20doesn't%20make%20sense%20with%20the%20other%20data%2Finfo%20you%20gave.%26nbsp%3B%20We%2C%20or%20at%20least%20I%2C%20need%20more%20clarification.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1537383%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Start%20%26amp%3B%20Finish%20Time%20with%20with%20Operating%20Hrs%20each%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1537383%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20i%20take%20difference%20between%20start%20%26amp%3B%20finish%20time%20which%20is%20around%20~189%20as%20stated%20in%20table%20(Total)%2C%20but%20if%20exclude%20non%20operating%20hrs%20from%20start%20to%20finish%20to%20be%20exact%20i%20get.%3C%2FP%3E%3CTABLE%20width%3D%22127%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22127%22%3E176%3A54%3A50%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Ei%20get%20the%20actual%20value%20when%20i%20do%20calc%20for%20each%20day%20and%20sum.%26nbsp%3B%3C%2FP%3E%3CP%3Ewhat%20i%20am%20trying%20to%20achieve%20to%20get%20formula%20in%20single%20cell%20to%20dictate%20the%20working%20hrs%20only%20in%20compliance%20with%20Operating%20Hrs%20fro%20each%20day.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1538403%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Start%20%26amp%3B%20Finish%20Time%20with%20with%20Operating%20Hrs%20each%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F735618%22%20target%3D%22_blank%22%3E%40Jeev125%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20837px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207132iAE0C15502178B085%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eif%20I%20understood%20the%20logic%20correctly%20(can't%20receive%20175%20manually%20as%20in%20the%20grey%20range).%3C%2FP%3E%0A%3CP%3EIn%20shifts%20range%20Finish%20is%20the%20time%20on%20next%20day%2C%20i.e.%20%3D25%2F24%20fo%20Sun%2C%20etc.%20Duration%3DFinish-Start.%3C%2FP%3E%0A%3CP%3EFormula%20for%20operating%20time%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(INDEX(%24I%243%3A%24I%249%2CWEEKDAY(SEQUENCE(INT(C4)-INT(B4)-1%2C1%2CINT(B4)%2B1)%2C1)))%2B%0AINDEX(%24H%243%3A%24H%249%2CWEEKDAY(%24B%244%2C1))-MOD(B4%2C1)%2B%0AMOD(C4%2C1)-INDEX(%24G%243%3A%24G%249%2CWEEKDAY(%24C%244%2C1))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eassuming%20your%20version%20of%20Excel%20supports%20dynamic%20arrays.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1569745%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Start%20%26amp%3B%20Finish%20Time%20with%20with%20Operating%20Hrs%20each%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1569745%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20Formula%20works%20only%20if%20there%20is%20more%202%20day%20difference%20if%20its%20one%20day%20then%20it%20shows%20Clac%20Error%20and%20if%20its%20sameday%20then%20it%20shows%20Value%20Error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20help%20me%20with%20the%20issue.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570255%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Start%20%26amp%3B%20Finish%20Time%20with%20with%20Operating%20Hrs%20each%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570255%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F735618%22%20target%3D%22_blank%22%3E%40Jeev125%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20the%20update%2C%20formula%20is%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20828px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F210598i37CAA042BBF76A80%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(INT(%24B4)%3DINT(%24C4)%2C%24C4-%24B4%2C%0AIFERROR(SUM(INDEX(%24I%243%3A%24I%249%2CWEEKDAY(SEQUENCE(INT(C4)-INT(B4)-1%2C1%2CINT(B4)%2B1)%2C1)))%2C0)%2B%0AINDEX(%24H%243%3A%24H%249%2CWEEKDAY(%24B%244%2C1))-MOD(B4%2C1)%2B%0AMOD(C4%2C1)-INDEX(%24G%243%3A%24G%249%2CWEEKDAY(%24C4%2C1)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1572055%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Start%20%26amp%3B%20Finish%20Time%20with%20with%20Operating%20Hrs%20each%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572055%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20genius%2C%20but%20i%20found%20a%20small%20flaw%2C%20I%20have%20attach%20the%20attachment%20for%20you.%20Please%20let%20me%20know.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1573177%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Start%20%26amp%3B%20Finish%20Time%20with%20with%20Operating%20Hrs%20each%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1573177%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F735618%22%20target%3D%22_blank%22%3E%40Jeev125%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20forgot%20about%20overnight%20shifts.%20Will%20play%20with%20that%20bit%20later.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 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.

 

        
StartFinishTotal  Operating HRs
15-06-2020 11:59:05 AM23-06-2020 09:06:05 AM189:07:00  Sunday4:201:00
     Monday4:201:00
  Actual   Tuesday9:061:00
  ~176hrs  Wednesday4:201:00
     Thursday4:201:00
     Friday4:202:40
     Saturday4:202:40
        
7 Replies

@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.

@Jeev125 

Perhaps like this

image.png

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.

@Sergei Baklan 

 

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.

@Jeev125 

Please check the update, formula is

image.png

=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)))

@Sergei Baklan 

 

You are genius, but i found a small flaw, I have attach the attachment for you. Please let me know.

@Jeev125 

Sorry, forgot about overnight shifts. Will play with that bit later.