Calculating Overtime Daily after 40 hours worked in a week

S

SundanceStriping

New Member
Joined
Jul 23, 2014
Messages
8
I am at a loss. I have tried for four days now to figure out a formula to calculate regular hours, overtime hours and total hours per day and per week.

My current time card is as follows:

B13-B20 = Sun (noon until) -Sun (until noon)
C-L = in & out times for all days of the week above
M13-N20 = regular hours
N13-N20 = overtime hours
O13-O20 = Total Hours

Also:

M21 = total weekly regular hours
N21 = total weekly overtime hours
o21 = total weekly hours

I have already formatted the in & out times, regular, overtime and hour times to show/calculate correctly according to time/decimal conversion.


My main issue is that I want the daily time to show in regular hour until 40 hours for the week has been met. Once met, I would like the hours for the day to be shown in the overtime column.

Please help! Thanks
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
The only way I could figure out how to do this is to copy and paste. Does this help?
DayInOutInOutInOutIn OutInOutRegular Hrs.Overtime HoursTotal Hours
Sunday Noon Until8:00 AM11:00 AM 3:00 3:00
Monday8:00 AM11:00 AM12:00 PM6:00 PM 9:00 9:00
Tuesday8:00 AM11:00 AM 3:00 3:00
Wednesday8:00 AM11:00 AM 3:00 3:00
Thursday8:00 AM11:00 AM 3:00 3:00
Friday8:00 AM11:00 AM 3:00 3:00
Saturday8:00 AM11:00 AM12:00 PM6:00 PM8:00 AM11:00 AM 12:00 12:00
Sunday Til Noon8:00 AM11:00 AM12:00 PM6:00 PM 9:00 9:00
Total Hrs.45:00 45:00

<colgroup><col><col span="9"><col><col span="3"></colgroup><tbody>
</tbody>

<colgroup><col><col span="9"><col><col span="3"><col></colgroup><tbody>
</tbody>
 
Upvote 0
In Over time:

=If(O13> 40:00, O13 - 40:00, 0)

in Regular hrs:

=If(SUM(D13-C13, F13-E13, H13- G13, J13-I13, L13-K13) <= 40:00, SUM(D13-C13, F13-E13, H13- G13, J13-I13, L13-K13), 40:00
 
Upvote 0
Has the regular hours function i gave you work?

If not it might have something to do with the data set, perhaps it doesn't know that the Time is in hours and so subtracting them is giving you a problem

EDIT: OHHH I misread your document...i thought the In and Out across the top was the days, and the rows were the people.....if you do it that way, than you'll get over time per person....currrently you don't have it setup to calculate overtime...just total time spent on a day
 
Upvote 0
I am trying to make this form for each employee individually. I need the daily time to total and once 40 hours has been worked, I would like the overtime to roll over to the the overtime column for each day that overtime kicks in. Also, I would like to have total regular and overtime hours to be totaled at the end of the week.

I'm not sure what I need to adjust at this point. If I need to start from scratch I will. At this point, I feel like that may be my only option. I need to have this completed by tomorrow.
 
Upvote 0
Overtime Calculation

PLEASE PLEASE HELP

I need formulas to help me calculate regular hours, overtime hours and total hours. overtime begins after 40 hours has been worked in one week. I would like the daily hours to roll over to the overtime column on the particular day that overtime kicked in. I have attached my excel sheet below. Any help would be appreciated. I need to have this completed ASAP.

I believe that I currently have all of the columns formatted to show as time and convert to decimal in the totals columns but any suggestions for me to check myself would be great.

This is my second Thread to post. I am pretty desperate at this moment.

Thanks in advance for any and all help.

DayInOutInOutInOutInOutInOutRegular HoursOvertime HoursTotal Hours
Sun Noon Until
Mon
Tues
Wed
Thurs
Frid
Sat
Sunday until noon
Total Weekly Hours

<tbody>
</tbody>
 
Upvote 0
Re: Overtime Calculation

SundanceStriping said:
I need to have this completed ASAP.
Click to expand...

I'm just going to give you guidance so you can test and put the formulas in yourself. The back and forth won't get it done as quickly as you say you need it.

In the regular hours, your formula is going to be essentially:

Code: 
=([out]-[in])+([out]-[in]) etc.
This will give you the hours for the day worked.

In overtime hours, you're going to have and if statement:

Code: 
=IF([total hours of the previous day] + [regular hours] >= 40, [that whole sum function previous] + [total hours previous] - 40, 0)

Now, you're going to need quite a few additional if statements for regular hours to figure out if the previous end day total is over 40.
 
Upvote 0
Please note that both of these formula must be array confirmed with Shift Ctrl Enter, if this is not done correctly then they will return #VALUE!

Enter the first formula in M13

=MINA("40:00",SUM(IF($C$12:$L$12="Out",C$13:L13))-SUM(IF($C$12:$L$12="In",C$13:L13)))-SUM(M$12:M12)

Enter the second formula in N13

=MAX((SUM(IF($C$12:$L$12="Out",C$13:L13))-SUM(IF($C$12:$L$12="In",C$13:L13)))-"40:00",0)

After the formula have been array confirmed, use the fill handle to copy them down to row 20.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Spreadsheet to calculate overtime and total hours
Replies
1
Views
682
HyraxWithAFlamethrower
H
D
  • Solved
Timesheet Calculation
Replies
7
Views
786
jorismoerings
J
I
  • Solved
Calculate total hours into days, hours, minutes
Replies
3
Views
406
Issie_52
I
S
  • Question
Scheduling running staff hours
Replies
1
Views
180
awoohaw
awoohaw
D
  • Question
Calculating OT Labor hours
Replies
5
Views
425
awoohaw
awoohaw

Forum statistics

Threads
1,215,029
Messages
6,122,757
Members
449,094
Latest member
dsharae57

Share this page

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