I started the formula from the scratch and ended with such construction:
Formula:
=MAX(0,MIN(FLOOR.MATH(A2)+TIME(16,0,0),B2)-WORKDAY(A2-1,1,$R$2:$R$100)-MAX(A2-FLOOR.MATH(A2),TIME(8,0,0)))+
MAX(0,NETWORKDAYS(WORKDAY(A2,1,$R$2:$R$100),WORKDAY(B2,-1,$R$2:$R$100),$R$2:$R$100))*TIME(8,0,0)+
IF(FLOOR.MATH(A2)=FLOOR.MATH(B2),0,MAX(0,MIN(FLOOR.MATH(B2)+TIME(16,0,0),B2)-WORKDAY(B2-1,1,$R$2:$R$100)-TIME(8,0,0)))
it consists of three rows (three parts):
- first is for time in starting day (is 0 if it's non-working day or time after 16:00)
- second is the number of working days in between day after first day and a day before last day, multiplied at the end by 8 hours per day:
two subcomments here:
a) *TIME(8,0,0) it is equivalent to end hour minus start hour: *(TIME(16,0,0)-TIME(8,0,0)) - worth noticing if starting and ending hours are written in some cells).
b) situation of no such days is handled by using MAX(0,dates difference)
- third is the number of hours on last day, but only if last day is different than first day - IF used. It could be also written as (FLOOR.MATH(A2)<>FLOOR.MATH(B2))*(MAX(0,MIN(FLOOR.MATH(B2)+TIME(16,0,0),B2)-WORKDAY(B2-1,1,$R$2:$R$100)-TIME(8,0,0))) (I originally wrote it that way, but IF is probably easier readable).
Just to analyze (and correct? if needed) I left columns F/N(P) with partial results I used while creating the formula. To show they are not needed any longer I removed them in rows 4:9
Delete all them from final version.
Bookmarks