Calculating Over Time after 40 hours in a week

Islandlady

New Member
Joined
Feb 24, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I am looking for assistance on calculating regular hours and overtime hours based on the numbers of hours worked up until that point in the week. Employees may have multiple time entries per day and I need it to slip the hours into regular and overtime once the 40 regular hour requirement has been fulfilled. As you will see with my current formula, when the total hours hits 40 hours all time in that entry goes to overtime, where some should go to regular time and some should go to over time. (See K22 & L22)

I tried to upload a mini sheet but I am having trouble.

Thanks you in advance for your assistance.

1635359188371.png
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,062
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Consider the following and then adapt to your spreadsheet.

Date and Time 4.xlsm
GHIJK
2TotalRegularOT
334.2534.250.00
409:00 AM11:30 AM36.7536.750.00
511:45 AM08:15 PM45.2540.005.25
6
2d
Cell Formulas
RangeFormula
J3:J5J3=MIN(I3,40)
K3:K5K3=I3-J3
I4:I5I4=I3+(H4-G4)*24
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,675
Members
425,229
Latest member
Rashid mahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top