If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
California Overtime Calculation
Hello,
I'm looking for a formula that will calculate overtime. But here's the problem... We do business in California and are required to pay overtime for any hours 1.) worked over 8 hours in a day 2.) worked over 40 hours in a week and 3.) worked on the 7th consecutive day (in our case Sunday when hours have been worked Monday through Saturday). I can figure out the overtime for each requirement but I'm trying to get an overtime calculation in one cell with all three criteria being met. Any help would be appreciated. Thanks, Joe D. |
#2
|
|||
|
|||
Hi Joe
Nothing would beat a supersexy oneliner here, but for a task like this I'd probably go for a lot of helper cells, some dealing with its own single requirement and others coordinating them. I think you must post pretty detailed where what is entered how to get working suggestions on this, there are all kinds of timesheets out there. HTH. Best wishes Harald "Joe D." Joe skrev i melding ... Hello, I'm looking for a formula that will calculate overtime. But here's the problem... We do business in California and are required to pay overtime for any hours 1.) worked over 8 hours in a day 2.) worked over 40 hours in a week and 3.) worked on the 7th consecutive day (in our case Sunday when hours have been worked Monday through Saturday). I can figure out the overtime for each requirement but I'm trying to get an overtime calculation in one cell with all three criteria being met. Any help would be appreciated. Thanks, Joe D. |
#3
|
|||
|
|||
Hi, Harald:
I agree WRT to the need for helper cells. There is too much room for "double counting" here, i.e. a week with 5 days, with 8, 8, 8, 8, and 10 hrs/day, puts the total at 42 hours but I would assume only counts as 2 hours overtime (not 2 hours for 8 hr/day PLUS 2 hours for 40 hr/wk). But the OP need to clarify what the priorities are in situations like this. And it won't work to weasel out of it by saying "that won't ever happen", because it definitely will, probably in the first month g. On Mon, 9 May 2005 20:44:17 +0200, "Harald Staff" wrote: Hi Joe Nothing would beat a supersexy oneliner here, but for a task like this I'd probably go for a lot of helper cells, some dealing with its own single requirement and others coordinating them. I think you must post pretty detailed where what is entered how to get working suggestions on this, there are all kinds of timesheets out there. HTH. Best wishes Harald "Joe D." Joe skrev i melding ... Hello, I'm looking for a formula that will calculate overtime. But here's the problem... We do business in California and are required to pay overtime for any hours 1.) worked over 8 hours in a day 2.) worked over 40 hours in a week and 3.) worked on the 7th consecutive day (in our case Sunday when hours have been worked Monday through Saturday). I can figure out the overtime for each requirement but I'm trying to get an overtime calculation in one cell with all three criteria being met. Any help would be appreciated. Thanks, Joe D. |
#4
|
|||
|
|||
Myrna Larson wrote...
I agree WRT to the need for helper cells. There is too much room for "double counting" here, i.e. a week with 5 days, with 8, 8, 8, 8, and 10 hrs/day, puts the total at 42 hours but I would assume only counts as 2 hours overtime (not 2 hours for 8 hr/day PLUS 2 hours for 40 hr/wk). But the OP need to clarify what the priorities are in situations like this. And it won't work to weasel out of it by saying "that won't ever happen", because it definitely will, probably in the first month g. Actually, the 8 per day and 40 per week are fairly simple to handle if there should be no double counting of overtime hours. =MAX(0,SUM(DailyHoursWorked)-40-SUMIF(DailyHoursWorked,"8") +8*COUNTIF(DailyHoursWorked,"8"))+SUMIF(DailyHour sWorked,"8") -8*COUNTIF(DailyHoursWorked,"8") The harder problem is 6 consecutive workdays. If an employee works 8 straight days, 7, 9, 7, 9, 7, 9, 7, and 9, what are the total overtime hours? In other words, does the start of a new 7 day period reset the overtime clock? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I use a rounded calculation result in another calculation? | vnsrod2000 | Worksheet Functions | 1 | January 26th, 2005 10:11 PM |
How do I use a rounded calculation result in another calculation? | vnsrod2000 | Worksheet Functions | 1 | January 26th, 2005 09:36 PM |
calculation field in a query and lookup tables? | King Kaos | Running & Setting Up Queries | 3 | September 25th, 2004 05:18 PM |
Time calculation | tim | Worksheet Functions | 1 | April 18th, 2004 03:49 AM |