| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
More complex overtime question
Any hours past 8 in a day is paid at 1.5
Any hours past 12 is paid at 2.0, except on Sat and Sun Any hours over 40 in a week. On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5. If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8 is 2.0. I am having the most trouble with combining the 40 hour work rule and the sixth consecutive day rule. Can anyone help with the formula's? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More complex overtime question
Hi Kim,
Can you give some more info on how your spreadsheet is set up? where/how is the date and number of hours stored? - Search "Kim DuBray" wrote: Any hours past 8 in a day is paid at 1.5 Any hours past 12 is paid at 2.0, except on Sat and Sun Any hours over 40 in a week. On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5. If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8 is 2.0. I am having the most trouble with combining the 40 hour work rule and the sixth consecutive day rule. Can anyone help with the formula's? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More complex overtime question
"Search33" wrote: Hi Kim, Can you give some more info on how your spreadsheet is set up? where/how is the date and number of hours stored? - Search "Kim DuBray" wrote: Any hours past 8 in a day is paid at 1.5 Any hours past 12 is paid at 2.0, except on Sat and Sun Any hours over 40 in a week. On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5. If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8 is 2.0. I am having the most trouble with combining the 40 hour work rule and the sixth consecutive day rule. Can anyone help with the formula's? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More complex overtime question
Currently I am using numbers with a sample as follows
A B C D E F G H I J 1 REG HR MAX/OT MAX/WK MAX 8 12 40 2 IN OUT IN OUT TL HRS WRKD HRS REG HRS OT HRS 3 MON DATE 5 11 12 18 12 12 8 4 4 TUES 5 WED 6 THUR 7 FRI 8 SAT 9 SUN The formula for mon-fri reg hrs is- min($g$1,g3) The formula for ot is- if((g38,min($h$1,g3-i3),0) The formula for Sat reg is- if(h7:h3)<40,min(g1,40-sum(h7:h3),0) The Sat ot is giving me trouble under certain scenarios. This is confusing, is there a way to attach the spreadsheet? "Kim DuBray" wrote: "Search33" wrote: Hi Kim, Can you give some more info on how your spreadsheet is set up? where/how is the date and number of hours stored? - Search "Kim DuBray" wrote: Any hours past 8 in a day is paid at 1.5 Any hours past 12 is paid at 2.0, except on Sat and Sun Any hours over 40 in a week. On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5. If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8 is 2.0. I am having the most trouble with combining the 40 hour work rule and the sixth consecutive day rule. Can anyone help with the formula's? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More complex overtime question
That example didn't format the way it should have. It is confusing.
Basically the reg hrs calc on mon - fri is the min of ttl hrs worked and 8 (the standard max reg hrs per day). the ot calc on mon-fri is- if ttl hrs per day is greater than 8 then the minimum of ttl hrs- reg hrs or the standard ot max per day (4) is the result. Double time is ttl hrs less reg hrs less ot hrs. The ot for sat is where it is tricky. For sat reg hrs if the prev worked hrs for mon-fri are less than 40 then reg hrs is the lesser of ttl hrs and 40-sum of prev worked hrs. The sat ot calc is- ttl hrs - reg hrs up to a max of 12ot hrs. If the prev worked hrs are greater than 40 then the ot calc is the lesser of 12 and the ttl hours worked. Is there a way to attach my timesheet for you to view? "Kim DuBray" wrote: Currently I am using numbers with a sample as follows A B C D E F G H I J 1 REG HR MAX/OT MAX/WK MAX 8 12 40 2 IN OUT IN OUT TL HRS WRKD HRS REG HRS OT HRS 3 MON DATE 5 11 12 18 12 12 8 4 4 TUES 5 WED 6 THUR 7 FRI 8 SAT 9 SUN The formula for mon-fri reg hrs is- min($g$1,g3) The formula for ot is- if((g38,min($h$1,g3-i3),0) The formula for Sat reg is- if(h7:h3)<40,min(g1,40-sum(h7:h3),0) The Sat ot is giving me trouble under certain scenarios. This is confusing, is there a way to attach the spreadsheet? "Kim DuBray" wrote: "Search33" wrote: Hi Kim, Can you give some more info on how your spreadsheet is set up? where/how is the date and number of hours stored? - Search "Kim DuBray" wrote: Any hours past 8 in a day is paid at 1.5 Any hours past 12 is paid at 2.0, except on Sat and Sun Any hours over 40 in a week. On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5. If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8 is 2.0. I am having the most trouble with combining the 40 hour work rule and the sixth consecutive day rule. Can anyone help with the formula's? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More complex overtime question
If you would like, you can e-mail me your spreadsheet
Search33_98(at)hotmail.com "Kim DuBray" wrote: That example didn't format the way it should have. It is confusing. Basically the reg hrs calc on mon - fri is the min of ttl hrs worked and 8 (the standard max reg hrs per day). the ot calc on mon-fri is- if ttl hrs per day is greater than 8 then the minimum of ttl hrs- reg hrs or the standard ot max per day (4) is the result. Double time is ttl hrs less reg hrs less ot hrs. The ot for sat is where it is tricky. For sat reg hrs if the prev worked hrs for mon-fri are less than 40 then reg hrs is the lesser of ttl hrs and 40-sum of prev worked hrs. The sat ot calc is- ttl hrs - reg hrs up to a max of 12ot hrs. If the prev worked hrs are greater than 40 then the ot calc is the lesser of 12 and the ttl hours worked. Is there a way to attach my timesheet for you to view? "Kim DuBray" wrote: Currently I am using numbers with a sample as follows A B C D E F G H I J 1 REG HR MAX/OT MAX/WK MAX 8 12 40 2 IN OUT IN OUT TL HRS WRKD HRS REG HRS OT HRS 3 MON DATE 5 11 12 18 12 12 8 4 4 TUES 5 WED 6 THUR 7 FRI 8 SAT 9 SUN The formula for mon-fri reg hrs is- min($g$1,g3) The formula for ot is- if((g38,min($h$1,g3-i3),0) The formula for Sat reg is- if(h7:h3)<40,min(g1,40-sum(h7:h3),0) The Sat ot is giving me trouble under certain scenarios. This is confusing, is there a way to attach the spreadsheet? "Kim DuBray" wrote: "Search33" wrote: Hi Kim, Can you give some more info on how your spreadsheet is set up? where/how is the date and number of hours stored? - Search "Kim DuBray" wrote: Any hours past 8 in a day is paid at 1.5 Any hours past 12 is paid at 2.0, except on Sat and Sun Any hours over 40 in a week. On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5. If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8 is 2.0. I am having the most trouble with combining the 40 hour work rule and the sixth consecutive day rule. Can anyone help with the formula's? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More complex overtime question
I've emailed you. Let me know if you don't get it, please.
"Kim DuBray" wrote: Any hours past 8 in a day is paid at 1.5 Any hours past 12 is paid at 2.0, except on Sat and Sun Any hours over 40 in a week. On Sat (or any day in the week where 40 hrs is met) overtime is paid at 1.5. If on Sat 40 hours is not reached, the first 8 hours are paid at 1.0 until the 40 hours is reached. Once 40 hours is reached, hours between 8-12 are paid at 1.5 and over 12 at 2.0. On Sun the 40 hours rule is disregarded if it is the sixth consecutive day and the first 8 hours are paid at 1.5 and over 8 is 2.0. I am having the most trouble with combining the 40 hour work rule and the sixth consecutive day rule. Can anyone help with the formula's? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complex overtime calculation | Excel Worksheet Functions | |||
| Overtime Question Unlike Others (Pictures Included) | Excel Worksheet Functions | |||
| Hide Rows / Columns - complex question | Excel Worksheet Functions | |||
| Complex query question | Excel Worksheet Functions | |||
| complex count question | Excel Worksheet Functions | |||
Linear Mode
