calculate overtime at 2 different rates

[jose123]

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.

 

[Per Jessen]

Jose,

I am not sure I understand your example.

What is Start and End timr for normal hours?

Regards,
Per

 

[jose123]

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.

 

[Bill Kuunders]

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

 

[jose123]

MOD, this is a function I have not worked with before. I was calculating
total hours as
c2 = IF(a2>b2,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.

 

[Bill Kuunders]

I have had a look via google and there was someone who worked with a table
and vlookup functions.
As you, I was more familiar with the "if "or" and" combinations.
The mod function is interestingly simple that's all.
It was a good brain excercise for an older guy.........
Thanks for the feed back

--
Greetings from New Zealand

MOD, this is a function I have not worked with before. I was calculating
total hours as
c2 = IF(a2>b2,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.