Automatic change for time constraint

[bong]

Thanks for the code, it is however quite difficult to understand.
Would you mind implementing the shift C1 in my file in which the time
for the shift is 19:30 to 4:00 such that inside the period, 1 will be
presented using your formula?
You can change the format of the page as you like
Hope that not cause too many troubles to you
Thank you
Bong

 

[Rob van Gelder]

I downloaded your workbook.

Hightlight Row 3 and insert a Row
In cell C3: =C2+(8.5/24)
Fill that formula across

In cell C4 (no spaces in formula):
=--(IF(OR(AND($A4<=$B4,$B4<=C$2),AND($A4>=C$3,OR($A4<=$B4,$B4<=C$2))),0,IF(AND($B4<=C$3,$B4>=C$2,OR($A4<=C$2,$A4>=C$3)),$B4-C$2,IF(OR(AND($A4<=C$2,$B4>=C$3),AND($A4>=$B4,OR($A4<=C$2,$B4>=C$3))),C$3-C$2,IF(AND($A4<=$B4,$A4>=C$2,$B4<=C$3),$B4-$A4,IF(AND($A4>=C$2,$A4<=C$3,OR($B4<=C$2,$B4>=C$3)),C$3-$A4,($B4-C$2)+(C$3-$A4))))))<>0)

Fill that formula to fill list.

HTH