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

Some videos you may like

Excel Facts

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

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
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
Joined
Jun 29, 2014
Messages
5,868
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 7, 2018
Messages
3
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
Joined
Dec 7, 2018
Messages
3
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
Joined
Apr 22, 2015
Messages
184
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:

Watch MrExcel Video

Forum statistics

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