California Overtime Calculation

[Guest]

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.

 

[Harald Staff]

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

 

[Myrna Larson]

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>.

 

[Harlan Grove]

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(DailyHoursWorked,">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?