Home |
Search |
Today's Posts |
#1
|
|||
|
|||
creating a formula for a timecard obiding by CA OT laws
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. |
#2
|
|||
|
|||
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 www.Geocities.com/UDQServices Free Data Processing Add-in< ===== * ===== * ===== * ===== "ness" wrote in message ... 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. |
#3
|
|||
|
|||
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? "Daniel CHEN" wrote: 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 www.Geocities.com/UDQServices Free Data Processing Add-in< ===== * ===== * ===== * ===== "ness" wrote in message ... 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. |
#4
|
|||
|
|||
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:B88)*(B2:B8-8)) +MAX(0,SUMPRODUCT((B2:B8<=8)*B2:B8+(B2:B88)*8)-40) |
#5
|
|||
|
|||
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(G68,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 "Harlan Grove" wrote: 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:B88)*(B2:B8-8)) +MAX(0,SUMPRODUCT((B2:B8<=8)*B2:B8+(B2:B88)*8)-40) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Cell shows formula and not the result of the formula. | Excel Worksheet Functions | |||
Creating Formula using check boxes | Excel Discussion (Misc queries) |