Help with an Overtime Formula!!!!!

[Lmao]

Hello! i have created a (probably overly complex) formula to calculate the overtime generated after adding all the hours worked in a week. The formula works for OT and DT the first five days but does not add anything over 40 hours as OT automatically. I want the OT field to add the california 7 day pay rule (any time over 40 hours, or 7 days as overtime) If I type anything other than numbers into cells it comes back an !VALUE error. I need the formula to ignore the word "OFF" so it does not affect the calculation or just add it as "0" so it will not return a !VALUE error and does not change the sum. I also need to have certain trigger words like if I type in "C/O" "VAC" "JURY" etc. trigger a conditional format and convert the value either in the cell or of the cell to 8 (to represent 8 hours for the day). Hopefully that makes sense i attached the file with the formula to make it simpler to work through any help would be greatly appreciated.

[gak67]

I am not familiar with the details of the California 7 day pay rule, so what would happen in your example 1 if John Doe also worked more than 8 hours in one day? Would they get overtime for the extra time in that day, as well as for the additional time over 40 hours they worked in that 7 day period?

[6StringJazzer]

I had to look this up. Lmao, you should really include this level of detail for a question like this.

In California, the general overtime provisions are that a nonexempt employee 18 years of age or older, or any minor employee 16 or 17 years of age who is not required by law to attend school and is not otherwise prohibited by law from engaging in the subject work, shall not be employed more than eight hours in any workday or more than 40 hours in any workweek unless he or she receives one and one-half times his or her regular rate of pay for all hours worked over eight hours in any workday and over 40 hours in the workweek. Eight hours of labor constitutes a day's work, and employment beyond eight hours in any workday or more than six days in any workweek is permissible provided the employee is compensated for the overtime at not less than:

1. One and one-half times the employee's regular rate of pay for all hours worked in excess of eight hours up to and including 12 hours in any workday, and for the first eight hours worked on the seventh consecutive day of work in a workweek; and

2. Double the employee's regular rate of pay for all hours worked in excess of 12 hours in any workday and for all hours worked in excess of eight on the seventh consecutive day of work in a workweek.

[gak67]

So if a person worked 12 hours on their 7th workday in a week would that be 16 hours of overtime they are paid for, ie 12 hours for working on the 7th day, plus 4 for working more than 8 hours a day? Or is it one or the other, but not both?

[Lmao]

I am not familiar with the details of the California 7 day pay rule, so what would happen in your example 1 if John Doe also worked more than 8 hours in one day? Would they get overtime for the extra time in that day, as well as for the additional time over 40 hours they worked in that 7 day period?

It would add that in the OT column as well for example he works 5 days and normally has Saturday Sunday off. So Monday through Thursday are 8 hours counted as regular hours. If Friday he works 9 hours. This the totals should be 40 hours regular 1 hour OT. If he works one hour on saturday it would change to 40 hours regular two hours OT (as he has worked more than 40 for the week). If he works 1 hour sunday it would be 40 hours regular three hours ot.

[Lmao]

I had to look this up. Lmao, you should really include this level of detail for a question like this.

Sorry you are correct should have been far more specific.

[Lmao]

So if a person worked 12 hours on their 7th workday in a week would that be 16 hours of overtime they are paid for, ie 12 hours for working on the 7th day, plus 4 for working more than 8 hours a day? Or is it one or the other, but not both?

If they work 8 hours Mon-Fri =40 hrs reg. If they then work 8 hours Saturday and 12 Sunday it should be: 40 hrs reg. 16 OT and 4 hours DT. OT = Overtime DT= Doubletime. Hopefully that helps thank you.

[scasas]

hello, this sheet is very useful. thank you! any way you can add a column for Regular hours (with appropriate formula)?

[6StringJazzer]

Welcome to the Forum scasas!

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

This thread is almost three years old. Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[kvsrinivasamurthy]

If Mon to Fri is 32 hrs and sat 8 hrs what is OT & D.T
If Mon to Fri is 30 hrs and sat 8 hrs Sun 2 Hrs what is OT & DT.