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