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

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"bong" wrote in message
...

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


--
bong
------------------------------------------------------------------------
bong's Profile:
http://www.excelforum.com/member.php...o&userid=15087
View this thread: http://www.excelforum.com/showthread...hreadid=267228