We help IT Professionals succeed at work.

calculate payroll - shift overtime

Medium Priority
406 Views
Last Modified: 2012-05-06
Please see the attached code & images.  When I calculate the row for payroll if the TotalOursWeek1 is > then 40 then it begins to kick over into OT hours, but I have found an issue that I can't wrap my brain around.
Say I am at a total of 33 hrs of regular time for week 1, then I work another 9 hours then next day to put me over 40 hours.  The 9 hours goes to OT and the 33 stays regular, where actuyally only 3 hours should be OT.  Any help is much appreciated.  Thanks
Private Sub CalculateHours()
        Dim drv As DataRowView = DirectCast(Me.PAYROLL_DETAILSBindingSource.Current, DataRowView)
 
        drv.Item("RegHrs") = 0
        drv.Item("OTHrs") = 0
        drv.Item("SS_RegHrs") = 0
        drv.Item("SS_OTHrs") = 0
 
        TotalHoursWeek1 = 0
        TotalHoursWeek2 = 0
        TotalHoursHift1 = 0
        RegHoursShift1 = 0
        OTHoursShift1 = 0
        TotalHoursHift2 = 0
        RegHoursShift2 = 0
        OTHoursShift2 = 0
        ShiftTotal = 0
        For i = 0 To PAYROLL_TIMETICKETGridEX.RowCount - 1
 
            r = PAYROLL_TIMETICKETGridEX.GetRow(i)
            Select Case r.Cells("Week").Value
                Case "WEEK 1"
                    Select Case r.RowIndex
                        Case i To 6
                            TotalHoursWeek1 += r.Cells("ShiftHours").Value
                            Select Case TotalHoursWeek1
                                Case Is <= 40
                                    Select Case r.Cells("Shift").Value
                                        Case "1ST"
                                            drv.Item("RegHrs") += r.Cells("ShiftHours").Value
                                        Case "2ND"
                                            drv.Item("SS_RegHrs") += r.Cells("ShiftHours").Value
                                    End Select
                                Case Is > 40
                                    Select Case r.Cells("Shift").Value
                                        Case "1ST"
                                            drv.Item("OTHrs") += r.Cells("ShiftHours").Value
                                        Case "2ND"
                                            drv.Item("SS_OTHrs") += r.Cells("ShiftHours").Value
                                    End Select
                            End Select
                    End Select
            End Select
        Next
        drv.Item("TotalHrs") = RegHoursShift1 + OTHoursShift1
        drv.Item("SS_TotalHrs") = RegHoursShift2 + OTHoursShift2
    End Sub

Open in new window

tt-1.jpg
tt-2.jpg
Comment
Watch Question

Often the easiest way to come up with algorithms try to implement it similiar to the way you think of it in your head. In my head, I tend to break it into two parts.
[1] Calculate regular hours.
[2] If the # of regular hours exceeds 40 hours, cap them at 40, and dump the put the remaining hours into the OT variable.

Looking at my quick mockup of the implementation, be sure to consider the performance implications of binding the values to the GUI rather than a local value.

'add up regular hours
'switch
regularHours += dailyHours
'end switch
 
if(regularHours > 40) then
	OtHours = regularHours - 40
	regularHours = 40
end if

Open in new window

Author

Commented:
Thanks for the reply.  That is very true in the instance of single shifts, as there is no 2nd shift variable to deal with.
When calculating the reg time & ot for each shift it is about when the total hours worked on both shifts equals 40, then sending the appropriate 1st reg, 2nd shift reg, 1st shift ot, & 2nd shift ot to the appropriate spaces depending on when they are wored during the week...wherein lies my issue.
I am basically on the cusp of it all, but am lookig for another set of eyes to look on it.  I included the images as it is an extremely difficult algorithm.
Thanks.

Author

Commented:
here is an excel spread sheet that may help clarify the issue a bit better.
Thanks

PAYROLL-EXAMPLE.xls
sorry for misunderstanding.  What would happen if you calculated the normal hours and ot hours right at the beginning.  I tried to splice this idea into the code that you have.  I may have removed too much, but you should get the idea

Private Sub CalculateHours()
        Dim drv As DataRowView = DirectCast(Me.PAYROLL_DETAILSBindingSource.Current, DataRowView)
 
        drv.Item("RegHrs") = 0
        drv.Item("OTHrs") = 0
        drv.Item("SS_RegHrs") = 0
        drv.Item("SS_OTHrs") = 0
 
        TotalHoursWeek1 = 0
        TotalHoursWeek2 = 0
        TotalHoursHift1 = 0
        RegHoursShift1 = 0
        OTHoursShift1 = 0
        TotalHoursHift2 = 0
        RegHoursShift2 = 0
        OTHoursShift2 = 0
        ShiftTotal = 0
        For i = 0 To PAYROLL_TIMETICKETGridEX.RowCount - 1
 
            r = PAYROLL_TIMETICKETGridEX.GetRow(i)
            Select Case r.Cells("Week").Value
                Case "WEEK 1"
                    Select Case r.RowIndex
                        Case i To 6
                            TotalHoursWeek1 += r.Cells("ShiftHours").Value
							dim ot as double, regular as double
							ot = TotalHoursWeek1 - 40 '(totalHours + value) -40
							ot = IIf(ot > 0,ot,0) ' don't allow negative numbers
							value -= ot
                            Select Case TotalHoursWeek1
                                    Select Case r.Cells("Shift").Value
                                        Case "1ST"
                                            drv.Item("RegHrs") += regular
											drv.Item("OTHrs") += ot
                                        Case "2ND"
                                            drv.Item("SS_RegHrs") += regular
											drv.Item("SS_OTHrs") += ot
                                    End Select
                            End Select
                    End Select
            End Select
        Next
        drv.Item("TotalHrs") = RegHoursShift1 + OTHoursShift1
        drv.Item("SS_TotalHrs") = RegHoursShift2 + OTHoursShift2
    End Sub

Open in new window

sorry about the formatting!!

Author

Commented:
Ill give it a try today & see what happens.  No worries on the formatting :)
Thanks for your help and I will post back.
V

Author

Commented:
It is still giving me the incorrect values.  The hard part is the timing & distribution of the shift hours for reg & ot.
Just keep racking my brain.
Let me know if you still need help with this problem.  I haven't had the chance to look at this further.  Likely you have already figured this out, but just in case you haven't....

Author

Commented:
Have not figured it out yet.  it is a PITA.  
I have to take a hiatus from it every so often :)
Thanks!
I just wrote a quick mockup in vba using the data in your excel sheet.  I didn't calculate the sums, but I doubt you will have any problem with that.  Take a peek and see if the algorithm works.  Hopefully, it will be easier to diagnose bugs if we start from the same workable sandbox.  Feel free to switch the shift and number of hours worked, but the number of entries are currently static.
Press Alt+F11 to view the code, it is on Sheet1.

ee.xls

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
AHHHH!  It works great!!  I have played with the numbers & have not found any bugs.  Looks like it calculates perfectly!

Thanks!!!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.