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

[getitdone]

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

	A	B	C	D	E	F	G	H
Date	Name	Project	Time In	Time Out	Total Hours Worked	Reg Hours	OT Hours Based on a 40 hour week
Employee E	Project 1	9.5	8

<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]

 

[GR00007]

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.....

 

[jtakw]

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?

 

[getitdone]

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!

 

[getitdone]

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!

 

[GR00007]

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.