Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a formula to calculate rates based on current age | Excel Worksheet Functions | |||
How can I calculate overtime? | Excel Discussion (Misc queries) | |||
Could you help me to calculate overtime | Excel Worksheet Functions | |||
How can I calculate trend growth rates in Excel? | Excel Discussion (Misc queries) | |||
how do i calculate pay from given hours and pay rates? | Excel Worksheet Functions |