Overtime formula once 40 hours have been reached.

tasha2515

New Member
Joined
Aug 2, 2017
Messages
7
hello,
I am trying to calculate the Ot hours once the Regular Hours have reached 40. If you could help with this that would be amazing!! we work shift work so our hours are from 7-3 3-11 11-7 so the time must be able to calculate into another day. we have figured out a few of the formulas, we just cant get the OT to start calculating only after 40 hours have been reached not after 8 hours per day have been reached. Thank you in advanced!! here is what our time sheet looks like
datetime inlunch outlunch in time outtotal hoursregular hoursOT hours
mon
tue
wed
thu
fri
sat
sun
totals

<tbody>
</tbody>
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Perhaps calculate the OT first then subtract it from regular hours. Like so...


Book1
ABCDEFGH
1DateTime InLunch OutLunch InTime OutTotal HoursRegularOT
2Monday07:0011:0011:3015:30880
3Tuesday07:0011:0011:3015:30880
4Wednesday07:0011:0011:3017:3010100
5Thursday07:0011:0011:3015:30880
6Friday07:0011:0011:3015:30862
7Saturday07:0011:0011:3015:308010
8Sunday07:0011:0011:3015:308018
Sheet1
Cell Formulas
RangeFormula
F2=(MOD(E2-B2, 1)-MOD(D2-C2, 1))*24
G2=MAX(F2-H2, 0)
H2=IF(SUM($F$2:F2)>40, SUM($F$2:F2)-40, 0)
 
Last edited:
Upvote 0
i added these into my spread sheet and all it gave me was 0.00 and #VALUE ! it would not calculate any hours at all aside form fri, sat and sun that have no times in them. for some odd reason it calculated for those days but not for ones with times, also you do not have to include lunch as we get paid for it, its just on the time sheet as a requirement from our staffing company.
 
Upvote 0
I have my data starting in row 2, some adjustment would be required to suit your exact need. I also use actual times, some companies just enter the hour for start time, again some adjustment might be needed.

In the above the formulas expand as you copy down. Here is the full formula list with some days deleted to demonstrate integrity of the formulas.


Book1
ABCDEFGH
1DateTime InLunch OutLunch InTime OutTotal HoursRegularOT
2Monday000
3Tuesday000
4Wednesday000
5Thursday07:0011:0011:3015:308.58.50
6Friday07:0011:0011:3015:308.58.50
7Saturday000
8Sunday000
Sheet1
Cell Formulas
RangeFormula
F2=MOD(E2-B2, 1)*24
F3=MOD(E3-B3, 1)*24
F4=MOD(E4-B4, 1)*24
F5=MOD(E5-B5, 1)*24
F6=MOD(E6-B6, 1)*24
F7=MOD(E7-B7, 1)*24
F8=MOD(E8-B8, 1)*24
G2=MAX(F2-H2, 0)
G3=MAX(F3-H3, 0)
G4=MAX(F4-H4, 0)
G5=MAX(F5-H5, 0)
G6=MAX(F6-H6, 0)
G7=MAX(F7-H7, 0)
G8=MAX(F8-H8, 0)
H2=IF(SUM($F$2:F2)>40, SUM($F$2:F2)-40, 0)
H3=IF(SUM($F$2:F3)>40, SUM($F$2:F3)-40, 0)
H4=IF(SUM($F$2:F4)>40, SUM($F$2:F4)-40, 0)
H5=IF(SUM($F$2:F5)>40, SUM($F$2:F5)-40, 0)
H6=IF(SUM($F$2:F6)>40, SUM($F$2:F6)-40, 0)
H7=IF(SUM($F$2:F7)>40, SUM($F$2:F7)-40, 0)
H8=IF(SUM($F$2:F8)>40, SUM($F$2:F8)-40, 0)
 
Upvote 0
i dont understand how this isnt working.. i put every formula in the way you have it yet its still not calculating correctly i need to find a way to show you what our exact time sheet looks like.


 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

Please post back here with links to all your posts that asked this same question in other forums.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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