Calculate overtime hours when Paying Semi-Monthly

Hello:

Please refer to attached excel sheet.

Column B : Date
Column C : Name of Employee
Column D : Clock In Time
Column E : Clock OUT Time
Column F : Total Hrs worked
Column G : Day of the week

Week starts from Sunday and ends on Saturday

Employees are paid Semi-Monthly ( 1 thru 15th and 16th thru End of month ) which makes 2 paychecks per month.

To comply with the labor law, i want to make sure that over 40 hours per week is adjusted in ALL employees paycheck as overtime.
Bare in mind that there will be some overlap hours from previous paycheck which needs to be accounted for.

I need the best solution to this problem..
Please refer to sheet 2 where i have manually started working on but confused.

I have listed Steps of evalauting overtime below:

Each employer defines the start and stop point for its workweeks as long as it comprises a 168-hour period. For example, a workweek might run from Monday morning to Sunday night at one company while another company has the workweek run from Sunday morning to Saturday night. Federal law does not permit you to average hours worked each week together for the purposes of calculating overtime, so if your company issues paychecks semimonthly, you have to calculate the overtime due each week of the pay period for each worker.

Step 1

Divide the payroll period into the workweeks as defined by your company. For example, if the semimonthly pay period begins on a Thursday and ends on a Thursday and your company defines the workweek as going from Monday morning to Sunday night, you have parts of three workweeks included in the pay period.


Step 2

Add the hours worked for the entire workweek for the first workweek of the pay period. In this example, even though the pay period started on a Thursday, you would include the hours worked from the prior Monday through the first Sunday to figure the overtime hours. For example, if the employee worked 30 hours the Monday through Wednesday before the pay period began and 20 hours from Thursday through Sunday, the employee would have 10 hours of overtime out of the 20 hours attributed to the current pay period.


Step 3

Add the number of hours worked the second workweek in the pay period, and subtract 40 from the result to find the number of overtime hours worked. The second week will always be a full week with semimonthly pay periods. For example, if the employee worked 45 hours, 40 hours would be at regular pay and 5 hours would be at overtime pay.


Step 4

Calculate the number of hours worked in the final week or partial week of the semimonthly pay period. If the employee has not yet exceeded 40 hours, do not pay overtime for the current pay period. However, include these hours when figuring the overtime payments for the next pay period. In this example, since the workweek ends on Thursday, if the employee has worked 40 hours so far that week, you would not pay any overtime, but if the employee worked any more hours that Friday through Sunday, those hours would be included as overtime on the next paycheck.


Step 5

Add the overtime, if any, from each of the weeks in the semimonthly pay period. In this example, add the 10 hours from the first week to the 5 hours from the second week to get 15 total hours of overtime.


Let me know if you have any questions.

Thanks, help is always aprreciated.

RM