Need formula for calculating daily labor rates including overtime.

Hello,

First time post. If I am missing information, let me know and I will add.

I have created a schedule builder for two salary and four hourly employees. I am integrating data calculations from one of my weekly reports to help speed things along.

I want to be able to calculate the total labor rates per day for all employees working that day including the salary. I am having trouble creating the formula to calculate the daily labor rates for the hourly employees once overtime rates come into play. I have been using nested IF formulas to calculate up to 40 hours and over 40 hours. The part of the formula I am stuck on is when there are both regular and overtime hours for an employee on a single day.

Attached is a test version of my workbook. I have blanked out unrelevant data calculations. You can see the formulas I have been developing on row 34 columns B-O. Column J is where I put the day with both regular and overtime rates. The current formula in column J is my attempt to calculate the overtime rate minus the regular rate, but I am stumped on how to add the regular hours back into the rate.

Thank you in advance for any help.

Formula is row 38 (not 34) of Sheet1 .

Not sure what your calculation is but I would unmerge cells in B3 etc and have B3 as regular hours and C3 as OT hours: I would remove all merged cells involved in calculations

in B3 (Regular hours)

=MIN(8,(C2-B2)*24)

in C3 (OT hours)

=MAX(0,((C2-B2)*24)-8)

in S2

=MAX(0,P2-40)

in R2

=SUMPRODUCT(($B3:$O3)*(MOD(COLUMN($B3:$O3),2)=0))

See attached Sheet3 for "Robert" (row 10)