I need assistance with calculating Reg and Daily/weekly OT

S

stefanoluigi

New Member
Joined
May 3, 2018
Messages
2
Hi everyone,

I need some assistance calculating regular and OT hours by day and week. Anything over 12 hours in a day and over 40 hours in a week. If the hours were already counted as OT for the daily over 12, they should not be counted for the over 40 hours for the respective week. This is an example of a time file that I have. I am looking for 1 summary line by employee that I have bolded below.

The employees don't all work 5 days, or may have multiple rows for one day.

[TABLE="width: 617"]
<tbody>[TR]
[TD]Payroll Name[/TD]
[TD]ee id[/TD]
[TD]pay rate[/TD]
[TD]Pay Date[/TD]
[TD]Hours[/TD]
[TD]Week[/TD]
[TD][/TD]
[TD]Reg[/TD]
[TD]Day OT[/TD]
[TD]Week OT[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]123456[/TD]
[TD]$11.95[/TD]
[TD]01/11/2016[/TD]
[TD]6.80[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]6.80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]123456[/TD]
[TD]$11.95[/TD]
[TD]01/12/2016[/TD]
[TD]9.22[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]9.22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]123456[/TD]
[TD]$11.95[/TD]
[TD]01/13/2016[/TD]
[TD]6.07[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]6.07[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]123456[/TD]
[TD]$11.95[/TD]
[TD]01/14/2016[/TD]
[TD]5.85[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]5.85[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]123456[/TD]
[TD]$11.95[/TD]
[TD]01/15/2016[/TD]
[TD]12.07[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]12.00[/TD]
[TD]0.07[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]123456[/TD]
[TD]$11.95[/TD]
[TD]01/18/2016[/TD]
[TD]14.00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]12.00[/TD]
[TD]2.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]123456[/TD]
[TD]$11.95[/TD]
[TD]01/19/2016[/TD]
[TD]8.17[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]8.17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]123456[/TD]
[TD]$11.95[/TD]
[TD]01/20/2016[/TD]
[TD]12.00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]12.00[/TD]
[TD]0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]123456[/TD]
[TD]$11.95[/TD]
[TD]01/21/2016[/TD]
[TD]16.00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]12.00[/TD]
[TD]4.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]123456[/TD]
[TD]$11.95[/TD]
[TD]01/22/2016[/TD]
[TD]12.02[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]12.00[/TD]
[TD]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bi Weekly Total [/TD]
[TD]123456[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]80.00[/TD]
[TD="align: right"]6.08[/TD]
[TD="align: right"]16.17[/TD]
[/TR]
</tbody>[/TABLE]


Any assistance would be appreciated.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
adjust as needed...

Code: 
Public Sub CalcHrs()
Dim vMsg
Dim rst   'As Recordset
Dim vRate, vTotReg, vTotOT
Dim vHrs As Single, vReg As Single, vOT As Single
Dim vCurrWk, vPrevWk, vCurrID, vPrevID

Const kiOffTotReg = 10
Const kiOffTotOT = 9
Const kiOffOtHrs = 8
Const kiOffRegHrs = 7
Const kiOffWk = 5
Const kiOffHrs = 4
Const kiOffRate = 2
Const kiOffID = 1

Const kiMaxReg = 40
Const kiMaxDay = 12

vPrevWk = "*&%"

Range("A2").Select
    While ActiveCell.Value <> ""
        vOT = 0
        vHrs = 0
        vCurrID = ActiveCell.Offset(0, kiOffID).Text
        vRate = ActiveCell.Offset(0, kiOffRate).Value
        vHrs = ActiveCell.Offset(0, kiOffHrs).Value
        vCurrWk = ActiveCell.Offset(0, kiOffWk).Value
        
        
        Select Case True   'reset the hours
            Case vPrevWk <> vCurrWk
                GoSub ResetHrs
            Case vCurrID <> vPrevID
                GoSub ResetHrs
        End Select

                '-----------------------
                'set the Reg Hrs, OT hrs.
                '-----------------------
        
        If vHrs > kiMaxDay Then
          vOT = vHrs - kiMaxDay
          vHrs = vHrs - vOT
        End If
        
        If vTotReg + vHrs >= kiMaxReg Then
           vReg = kiMaxReg - vTotReg
           vOT = vOT + (vHrs - vReg)
        Else
           vReg = vHrs
        End If
         
        vTotReg = vTotReg + vReg
        vTotOT = vTotOT + vOT
 
        ActiveCell.Offset(0, kiOffRegHrs).Value = vReg
        ActiveCell.Offset(0, kiOffOtHrs).Value = vOT
 
        vPrevID = vCurrID
        vPrevWk = vCurrWk
        
       ActiveCell.Offset(1, 0).Select  'next row
   Wend
   
GoSub ResetHrs
Range("J1").Value = "Weekly OT Total"
Range("k1").Value = "Weekly Reg Total"

MsgBox "DONE"
Exit Sub

ResetHrs:
ActiveCell.Offset(-1, kiOffTotReg).Value = vTotReg
ActiveCell.Offset(-1, kiOffTotOT).Value = vTotOT
vTotReg = 0
vTotOT = 0
Return

ErrRemove:
MsgBox Err.Description, , "CalcHrs():" & Err
End Sub
 
Upvote 0
Welcome to the board.

Here's a formula based solution:

ABCDEFGHIJ
Payroll Nameee idpay ratePay DateHoursWeekRegDay OTWeek OT
Jane Doe
Jane Doe
Jane Doe
Jane Doe
Jane Doe
Jane Doe
Jane Doe
Jane Doe
Jane Doe
Jane Doe
Bi Weekly Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]123456[/TD]
[TD="align: right"]$11.95 [/TD]
[TD="align: right"]1/11/2016[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6.8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]123456[/TD]
[TD="align: right"]$11.95 [/TD]
[TD="align: right"]1/12/2016[/TD]
[TD="align: right"]9.22[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9.22[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]123456[/TD]
[TD="align: right"]$11.95 [/TD]
[TD="align: right"]1/13/2016[/TD]
[TD="align: right"]6.07[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6.07[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]123456[/TD]
[TD="align: right"]$11.95 [/TD]
[TD="align: right"]1/14/2016[/TD]
[TD="align: right"]5.85[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5.85[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]123456[/TD]
[TD="align: right"]$11.95 [/TD]
[TD="align: right"]1/15/2016[/TD]
[TD="align: right"]12.07[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0.07[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]123456[/TD]
[TD="align: right"]$11.95 [/TD]
[TD="align: right"]1/18/2016[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]123456[/TD]
[TD="align: right"]$11.95 [/TD]
[TD="align: right"]1/19/2016[/TD]
[TD="align: right"]8.17[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8.17[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]123456[/TD]
[TD="align: right"]$11.95 [/TD]
[TD="align: right"]1/20/2016[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]123456[/TD]
[TD="align: right"]$11.95 [/TD]
[TD="align: right"]1/21/2016[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]123456[/TD]
[TD="align: right"]$11.95 [/TD]
[TD="align: right"]1/22/2016[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0.02[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]123456[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]79.94[/TD]
[TD="align: right"]6.09[/TD]
[TD="align: right"]16.17[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=MIN(E2,12)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=IF(E2<=12,"",E2-H2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H12[/TH]
[TD="align: left"]=MIN(40,SUM(H2:H6))+MIN(40,SUM(H7:H11))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I12[/TH]
[TD="align: left"]=SUM(I2:I11)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J12[/TH]
[TD="align: left"]=SUM(H2:H11)-H12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Note that the regular hours don't quite add up to 80, because the first week (H2:H6) doesn't quite add up to 40.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Reg Hr, OT and DT
Replies
3
Views
686
awoohaw
awoohaw
D
  • Question Question
Monthly / Bi-Weekly Payroll Calculator
Replies
0
Views
2K
DawnAZ
D
T
  • Question Question
Using a dynamic formula to calculate value of reinvested dividends over changing time periods
Replies
2
Views
3K
Tuckbox
T
G
  • Question Question
Overtime Calculation Based on a 40 hour week - Large Data Set
Replies
5
Views
969
GR00007
G
K
  • Question Question
Timesheet OT over 40 hrs/Wk Formula Help Needed - NOVICE
Replies
2
Views
699
KKHanke
K

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

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