I need assistance with calculating Reg and Daily/weekly OT

[stefanoluigi]

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.

Payroll Name	ee id	pay rate	Pay Date	Hours	Week		Reg	Day OT	Week OT
Jane Doe	123456	$11.95	01/11/2016	6.80	1		6.80
Jane Doe	123456	$11.95	01/12/2016	9.22	1		9.22
Jane Doe	123456	$11.95	01/13/2016	6.07	1		6.07
Jane Doe	123456	$11.95	01/14/2016	5.85	1		5.85
Jane Doe	123456	$11.95	01/15/2016	12.07	1		12.00	0.07
Jane Doe	123456	$11.95	01/18/2016	14.00	2		12.00	2.00
Jane Doe	123456	$11.95	01/19/2016	8.17	2		8.17
Jane Doe	123456	$11.95	01/20/2016	12.00	2		12.00	0.00
Jane Doe	123456	$11.95	01/21/2016	16.00	2		12.00	4.00
Jane Doe	123456	$11.95	01/22/2016	12.02	2		12.00	0.02
Bi Weekly Total	123456						80.00	6.08	16.17

<tbody>
</tbody>

Any assistance would be appreciated.

 

[ranman256]

adjust as needed...

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

 

[Eric W]

Welcome to the board.

Here's a formula based solution:

	A	B	C	D	E	F	G	H	I	J
1	Payroll Name	ee id	pay rate	Pay Date	Hours	Week		Reg	Day OT	Week OT
2	Jane Doe	123456	$11.95	1/11/2016	6.8	1		6.8
3	Jane Doe	123456	$11.95	1/12/2016	9.22	1		9.22
4	Jane Doe	123456	$11.95	1/13/2016	6.07	1		6.07
5	Jane Doe	123456	$11.95	1/14/2016	5.85	1		5.85
6	Jane Doe	123456	$11.95	1/15/2016	12.07	1		12	0.07
7	Jane Doe	123456	$11.95	1/18/2016	14	2		12	2
8	Jane Doe	123456	$11.95	1/19/2016	8.17	2		8.17
9	Jane Doe	123456	$11.95	1/20/2016	12	2		12
10	Jane Doe	123456	$11.95	1/21/2016	16	2		12	4
11	Jane Doe	123456	$11.95	1/22/2016	12.02	2		12	0.02
12	Bi Weekly Total	123456						79.94	6.09	16.17

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H2 =MIN(E2,12) I2 =IF(E2<=12,"",E2-H2) H12 =MIN(40,SUM(H2:H6))+MIN(40,SUM(H7:H11)) I12 =SUM(I2:I11) J12 =SUM(H2:H11)-H12 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.

 

[stefanoluigi]

Thank you, both!!