creating a formula for a timecard obiding by CA OT laws

[ness]

I have been using the IF formula to calculate everything else on this time
card that I am trying to creat, but in certain instances for CA if a person
does not meet 40 hours in a M-F workweek, and works on say Saturday the time
worked on Saturday does not count as OT until the 40 hour threshold is met.

ie: worked 35 hours M-F, worked 7 hours on Saturday, we need 5 hours to go
into regular hours, and the remaining 2 hours into OT. it gets really
complicated. I'm hoping someone already created a worksheet with all the
formulas and can answer my question.

 

[Daniel CHEN]

Assume you have hours filled in Range A1:G1
A1:E1 for Monday to Friday and F1 for Sat and G1 for Sunday.
The calculation of overtime is
=IF(SUM(A1:E1)>=40,SUM(A1:G1)-40,IF(SUM(A1:G1)>40,SUM(A1:G1)-40,0))

If the total hours for M-F >=40, then OT = total hours for all 7 days -
40;
If the total hours for M-F < 40, but for all 7 days >40 then OT = total
hours for all 7 days - 40;
If the total hours for all 7 days <= 40 then OT = 0

===== * ===== * ===== * =====
Daniel CHEN

(e-mail address removed)
www.Geocities.com/UDQServices

Free Data Processing Add-in<

===== * ===== * ===== * =====

 

[ness]

I tried to apply the formula in the exact way, and it says, "you have too
many arguments for this function" I also do not think it is as simple as
that... there are more perameters that need to be set before the result is
calculated. Any other ideas?

 

[Harlan Grove]

ness wrote...

I tried to apply the formula in the exact way, and it says, "you have too
many arguments for this function" I also do not think it is as simple as
that... there are more perameters that need to be set before the result is
calculated. Any other ideas?

....

First, Daniel's formula is correct as written, so you must have screwed
it up entering it, perhaps when changing the cells references you
inadvertently deleted the inner IF call or added a comma.

As I understand it, CA overtime applies both per day (anything over 8
hours in a single day) and per week (anything over 40 hours, but no
double counting per day overtime hours). So if hours worked were M 7,
Tu 7, W 9, Th 8, F 9, Sa 3, there'd be daily overtime hours on W (1)
and F (1), and 1 hour weekly overtime (regular time M-F = 38 plus 3
hours on Sa gives 41 hours, so 1 over 40).

I seem to recall there may also be rules about working more than 7
consecutive days, but I'll ignore that. Ditto rules about holidays.

If you have daily hours worked per day in B2:B8, then total overtime
hours would be given by

=SUMIF(B2:B8,">8")-8*COUNTIF(B2:B8,">8")
+MAX(0,SUMIF(B2:B8,"<=8")+8*COUNTIF(B2:B8,">8")-40)

or

=SUMPRODUCT((B2:B8>8)*(B2:B8-8))
+MAX(0,SUMPRODUCT((B2:B8<=8)*B2:B8+(B2:B8>8)*8)-40)

 

[ness]

your formulas make sense, but I think we fell off on the wrong page. I'm
looking for a formula to calculate the regular hours minus OT if any since OT
has it's own formula, but first thing's first. (see table below)

Assume each header is a different column, we'll start rows at 1 starting
Monday.
I tried this formula (corresponding with this current table...
=IF(SUM(G1:G5)>40,0,IF((G6+SUM(G1:G5))>40,40-G6-SUM(G1:G5),IF(G6>8,8,G6)))

as a result I get a negative number if there are not enough hours worked
Monday through Friday. otherwise it would work if the world were so simple
that an employee would just work a whole 40 hours in a week and no OT. LOL
I hope the concept is clear because I really need help... all suggestions
welcome

-----------------------------------------------------------------------------------------------
DAY IN OUT IN OUT TOTAL
REG OT

HOURS HOURS HOURS
Mon 7:00 AM 11:30 AM 12:00 PM 4:30 PM 9.00 8.00 1.00
Tues 7:00 AM 11:30 AM 12:00 PM 9:00 PM 13.50 8.00 4.00
Wed 7:00 AM 11:30 AM 12:00 PM 8:00 PM 12.50 8.00 4.00
Thurs 6:00 AM 11:30 AM 5.50 5.50
Fri 7:00 AM 11:30 AM 4.50 4.50
Sat 6:00 AM 11:30 AM 12:00 PM 2:00 PM 7.50
Sun 6:00 AM 11:30 AM 12:00 PM 5:00 PM 10.50 6.00 8.00