Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default calculate overtime at 2 different rates

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 703
Default calculate overtime at 2 different rates

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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default calculate overtime at 2 different rates

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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 303
Default calculate overtime at 2 different rates

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





  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default calculate overtime at 2 different rates

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







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 303
Default calculate overtime at 2 different rates

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


"jose123" wrote in message
...
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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need a formula to calculate rates based on current age Martha Excel Worksheet Functions 3 July 10th 07 03:50 PM
How can I calculate overtime? Rachel Excel Discussion (Misc queries) 0 September 1st 05 05:28 PM
Could you help me to calculate overtime Svetlana Excel Worksheet Functions 10 August 25th 05 09:00 AM
How can I calculate trend growth rates in Excel? david34 Excel Discussion (Misc queries) 4 July 16th 05 05:44 PM
how do i calculate pay from given hours and pay rates? ejm014 Excel Worksheet Functions 0 May 27th 05 01:22 PM


All times are GMT +1. The time now is 09:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"