Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

calculate payroll - shift overtime

Posted on 2009-02-16
11
Medium Priority
?
399 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
0
Comment
Question by:vwalla
  • 6
  • 5
11 Comments
 
LVL 6

Expert Comment

by:obrienslalom
ID: 23655602
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

0
 
LVL 1

Author Comment

by:vwalla
ID: 23655784
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.
0
 
LVL 1

Author Comment

by:vwalla
ID: 23655810
here is an excel spread sheet that may help clarify the issue a bit better.
Thanks

PAYROLL-EXAMPLE.xls
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 6

Expert Comment

by:obrienslalom
ID: 23655924
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

0
 
LVL 6

Expert Comment

by:obrienslalom
ID: 23655927
sorry about the formatting!!
0
 
LVL 1

Author Comment

by:vwalla
ID: 23658761
Ill give it a try today & see what happens.  No worries on the formatting :)
Thanks for your help and I will post back.
V
0
 
LVL 1

Author Comment

by:vwalla
ID: 23659733
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.
0
 
LVL 6

Expert Comment

by:obrienslalom
ID: 23773736
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....
0
 
LVL 1

Author Comment

by:vwalla
ID: 23773878
Have not figured it out yet.  it is a PITA.  
I have to take a hiatus from it every so often :)
Thanks!
0
 
LVL 6

Accepted Solution

by:
obrienslalom earned 2000 total points
ID: 23786099
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
0
 
LVL 1

Author Closing Comment

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

Thanks!!!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question