Overtime Calculation Based on a 40 hour week - Large Data Set

getitdone

New Member
Joined
Dec 7, 2018
Messages
3
Hello,

I need some advise please - I'm at a loss. I need to find overtime hours worked based on a 40 hour working week. I have done preliminary research and most of the formulas online I found do not work for me because of my large data set and the way the spreadsheet is set up.
The below shows how the spreadsheet is set up, but I have over 20 Thousand rows of data.
I'm looking to see if there are formulas I can input in column H that would tell me the overtime hours based on the dates employees worked, but the overtime hours would need to be the only hours identified as overtime hours that are over 40 hours in a week for each employee.
This data is used for job costing purposes where I need to identify the overtime hours for each project. If there is another way to set up this spreadsheet with large data set in mind please let me know.



Excel 2010
ABCDEFGH
DateNameProjectTime InTime OutTotal Hours WorkedReg HoursOT Hours Based on a 40 hour week
Employee EProject 19.58

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]11/5/2018[/TD]

[TD="align: right"]6:00:00 AM[/TD]
[TD="align: right"]3:30:00 PM[/TD]

</tbody>
Data Sample

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=(E2-D2+(E2<d2< font="">)</d2<>)*24[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=MIN(8,F2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
With over 20K rows do you have more than one week of dates?
Have you considered a pivot table on Employee with a sum of hours
Is lunch time deducted - is there lunch time (or break)
Too many questions.....
 
Upvote 0
Hi,

On top of questions asked by GR00007, and as said, there are many; with over 20K rows of data, you Only showed 1 line?
 
Upvote 0
With over 20K rows do you have more than one week of dates?
Have you considered a pivot table on Employee with a sum of hours.
Is lunch time deducted - is there lunch time (or break). Lunch and breaks are paid so there are no issues there.
Too many questions.....

Yes, the data is in the same format, but for entire year.
Yes the pivot table works great to figure out how much overtime to pay for each week, but I'm still facing the same problem with applying overtime, So I figured I need to apply overtime at the source data than do a pivot table to summarize by project.
Lunch and breaks are both paid so there are no issues there.

Thanks!
 
Upvote 0
Yes, the data is in the same format but for the entire year, so assume it has different employees, different working days, and different projects. I just didn't know how to properly paste it in the thread since I'm very new to the forums world. I figured that just showing a sample on one row and applying that logic to all 20K rows.

Thanks!
 
Upvote 0
You could create a running total that changes on employee:
- sort on week, employee, start date/time
- put a running total in - possibly column H start in H2: =IF(B1<>B2,F2,H1 + F2)
- May I suggest an employee#? If you have two employees named Jane Doe their tally will run together.
The tally will show when the 40 hours is reached for the week for the jobs being reported.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

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