Jose,

I do not know how you can get this passed any union ruleas but that's your

concern.

If a person works into overtime during the dayshift after a long night shift

surely you would pay them still the night shift overtime rates......... As I

said not my concern.

I've worked on this a fair while................I WAS going into my overtime

in the day shift hrs calcs I have run out of nested if's.

It will not be correct if a person starts before 6 a.m. and finishes the

next morning after midnight.

I hope that won't happen often.

Any way.....here goes.

A2 is the start time

B2 is the end time

C2 is the total hrs =MOD(B2-A2,1)*24

D2 is dayshift hrs

=24*(IF(AND(A2>=TIME(6,0,0),A2<=TIME(18,0,0),B2>=TIME(6,0,0),B2<=TIME(18,0,0),A2<B2),C2/24,IF(AND(A2>=TIME(6,0,0),A2<=TIME(18,0,0),B2>=TIME(18,0,0)),TIME(18,0,0)-A2,IF(AND(A2>=TIME(6,0,0),A2<=TIME(18,0,0),B2<=TIME(6,0,0)),TIME(18,0,0)-A2,IF(AND(A2<TIME(18,0,0),A2>=TIME(6,0,0),A2>B2,B2>TIME(6,0,0),B2<TIME(18,0,0)),(TIME(18,0,0)-A2)+(B2-TIME(6,0,0)),IF(OR(AND(B2>=TIME(6,0,0),B2<=TIME(18,0,0),A2>TIME(18,0,0)),AND(B2>=TIME(6,0,0),B2<=TIME(18,0,0),A2<TIME(6,0,0))),B2-TIME(6,0,0),IF(AND(A2<=TIME(6,0,0),B2>=TIME(18,0,0)),0.5,0)))))))

E2 is night shift hrs =C2-D2

F2 is total overtime hrs =IF(C2>8,C2-8,0)

G2 is dayshift overtime hrs

'=(IF(AND(A2>=TIME(6,0,0),A2<B2,B2<=TIME(18,0,0)),F2,IF(OR(AND(A2>=TIME(6,0,0),A2<TIME(10,0,0)),AND(B2>TIME(18,0,0),B2<=TIME(6,0,0))),TIME(18,0,0)-(A2+8/24),IF(AND(C2>8,B2>TIME(6,0,0),B2<TIME(18,0,0),A2<TIME(6,0,0)),F2/24,IF(AND(A2<=TIME(6,0,0),B2>=TIME(18,0,0)),4/24,0)))))*24

H2 is nightshift overtime hrs =F2-G2

good luck

do some more testing and let me know