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

#### getitdone

##### New Member
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
1DateNameProjectTime InTime OutTotal Hours WorkedReg HoursOT Hours Based on a 40 hour week
211/5/2018Employee EProject 16:00:00 AM3:30:00 PM9.58

<tbody>
</tbody>
Data Sample

Worksheet Formulas
CellFormula
F2=(E2-D2+(E2<d2< font="">)</d2<>)*24
G2=MIN(8,F2)

<tbody>
</tbody>

<tbody>
</tbody>

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### GR00007

##### Board Regular
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

##### Well-known Member
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

##### New Member
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

##### New Member
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

##### Board Regular
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:

Replies
0
Views
115
Replies
7
Views
449
Replies
11
Views
333
Replies
1
Views
167
Replies
9
Views
268

1,127,201
Messages
5,623,343
Members
415,969
Latest member
Rey99

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

### Which adblocker are you using?

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

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