MOD, this is a function I have not worked with before. I was calculating
total hours as
c2 = IF(a2b2,b2+1-a2,b2-a2)*24 is there an advantage to using MOD over
this calculation other than it's a bit cleaner?

Your calculations look similar to what I was doing except I multiplied the
time cells by 24, like the example above. I was hoping the calculation would
not need to be so complex and someone would know a better shortcut. I have
my favorite functions and have a tendency not to stray into other functions.
I didn't run out of nested IF statements but I'm still testing. My example
may not have been very realistic but I needed to get the point across hours
can vary. Thank you for the example.
--
Jose


"Bill Kuunders" wrote:

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=T IME(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=TIM E(18,0,0)),TIME(18,0,0)-A2,IF(AND(A2=TIME(6,0,0),A2<=TIME(18,0,0),B2<=TIM E(6,0,0)),TIME(18,0,0)-A2,IF(AND(A2<TIME(18,0,0),A2=TIME(6,0,0),A2B2,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),A2TIME(18,0,0)),AND(B2=TIME(6,0,0),B2<=TIM E(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(C28,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( B2TIME(18,0,0),B2<=TIME(6,0,0))),TIME(18,0,0)-(A2+8/24),IF(AND(C28,B2TIME(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

--
Greetings from New Zealand


"jose123" wrote in message
...
This shop is run 24 hrs/day; no shifts, no normal hours, logistics
industry.
Pay rate is based on day of time worked. If you work any hours between
6a -
6p you are paid at rate 1. If you work any hours between 6p - 6a you are
paid at rate 2. Any hours worked after 8 hours is considered overtime.
The
overtime will be paid based rate 1 or rate 2. Employee start/end times
are
at different times of the day.
--
Jose


"Per Jessen" wrote:

Jose,

I am not sure I understand your example.

What is Start and End timr for normal hours?

Regards,
Per

On 11 Jul., 05:06, jose123 wrote:
I need to calculate overtime but due to the start/end times overtime
may be
posted at 2 different rates. How do I calculate overtime for the
following:

Start 21:00 2 hours of overtime
End 7:00 1 hour of overtime for Rate 1 (hours 6:00 -
18:00)
Hours 10.00 1 hour of overtime for Rate 2 (hours 18:00 -
6:00)

I track the hours worked at both rates so I know 1 hour was worked
during
Rate 1 and 9 hours worked during Rate 2.

These same rules would apply if a person worked:
Start 8:00 3 hours of overtime
End 19:00 2 hours of overtime for Rate 1 (hours 6:00 -
18:00)
Hours 11.00 1 hour of overtime for Rate 2 (hours 18:00 -
6:00)

Ten hours were worked during Rate 1 and 1 hour worked during Rate 2.
--
Jose