Calculate time within a specified timeframe only

[jaribgv]

Hello! I need to calculate the time that has passed between two specific times, however I need to calculate them only between 7am and 4pm and ignore the rest of the time. So for example if the start time is 12/4/19 3:50PM and the ending date is 12/5/19 7:10AM I need the formula to calculate that only 20mins passed, instead of 15 hours and 20mins. And if the start time is after 4pm, I need the formula to calculate starting the following business day at 7am.

Looking through this forum I found a formula that works perfectly if the start and end dates are within the schedule, but it's not able to calculate it if it falls outside the schedule. The formula I'm using is the following:

=((NETWORKDAYS(I16,K16))*15/24)+16/24-MOD(I16,1)+MOD(K16,1)-7/24

I16= Start Date/Time
K16= End Date/Time

The following are two examples where the first one works great but the second one doesn't:

I appreciate any help you can provide to improve this formula.

Thanks,

 

[yky]

How long is a shift? Can a shift be greater than 12 hours?

 

[jaribgv]

How long is a shift? Can a shift be greater than 12 hours?

No, the shift will always be 9 hours long

 

[yky]

See if this works. This is an array formula. You need use Ctrl-Shift-Enter to enter the formula.

=SUMPRODUCT(LOOKUP(ROW(INDIRECT("1:2880")),{1;421;961;1441;1861;2401;2881},{0;1;0;0;1;0}),LOOKUP(ROW(INDIRECT("1:2880")),IFERROR(SUMIF(OFFSET(C$1,,,ROW(C1:C3)-1),"<>")+1,1),{0;1;0}))

You need three help cells. In the example, I use C1:C3.

C1: =ROUND(MOD(K16,1)*1440,1)
C2: =ROUND(IF(INT(L16)-INT(K16)=0,(MOD(L16,1)-MOD(K16,1))*1440,(1-MOD(K16,1)+MOD(L16,1))*1440),1)
C3: =ROUND(IF(INT(L16)-INT(K16)=0,(2-MOD(L16,1))*1440,(1-MOD(L16,1))*1440),1)

Oh, I have the two times in K16 and L16, not I16 and K16.