Separate reg and ot from hours worked

KLFT

New Member
Joined
Jun 3, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi,
I am needing a formula that will calculate the hours from cell: I through K and accumulate in column L (Regular Hours). Then once column L totals 40 hours, I need the additional hours to populate in to column N (Overtime Hours) and accumulate and then total.

Below is what I have so far and I appreciate any help at all from any of you because I can do simple formulas but that's it.
1591239971425.png
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I'm not sure how to attach the spreadsheet but I do have formula's in column H (Time on lunch), column I ( Hours Worked). I am needing formula's for column L (Regular), column M (Overtime) and I can plug in PTO and Holiday as needed.

[/QUOTE]
 
Upvote 0
I really really really need help with a formula for this before Monday if at all possible and anyone is willing to help me out. I am in need of a formula to accumulated daily for hours worked up to 40 hours under a regular hours column. Then I need the ot hours to calculate in the next column once regular hours have exceeded 40 hours.
 
Upvote 0
Assuming 'Week 1 - Sunday' is row 3, then would this work for you?

Set L3 = MIN(SUM(I3:K3),40)
Set M3 = MAX(SUM(I3:K3)-40,0)
 
Upvote 0
Assuming 'Week 1 - Sunday' is row 3, then would this work for you?

Set L3 = MIN(SUM(I3:K3),40)
Set M3 = MAX(SUM(I3:K3)-40,0)

Shakenblake, thank you and this formula works perfectly for the bottom total. What I am needing is for Column L row 11 to add to row 12 and each row following so the hours accumulate up to 40 hours. Once 40 hours have been reached, I need the overtime hours to show in Column M on the day overtime began.

Below is the full spreadsheet. I don't know how to attach she sheet for you to have access to it.
1591497889092.png


1591497889092.png
 
Upvote 0
Try this for your daily totals:

Book1
IJKLM
1hrshrshrsReg HrsOT Hrs
26.52282.5
Sheet1
Cell Formulas
RangeFormula
L2L2=IF(SUM(I2:K2)<8,SUM(I2:K2),8)
M2M2=IF(SUM(I2:K2)<8,"",SUM(I2:K2)-8)
 
Upvote 0
Hurricanedaphne, I am needing the hours under column L to accumulate up to 40 hours. Then I am needing column M to pickup the remaining hours.
 
Upvote 0
Hurricanedaphne, this is the outcome i'm needing. I just don't know how to get it here.
1591587582889.png
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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
Back
Top