Split specific hours at the start of the week

[thor1124]

Dear all,

I am trying to write an excel vba to calculate the overtime hours of our staff during every week of a month but my coding fails to calculate the hours correctly in some scenarios. (case 3 in sample data)

Target: the overtime period starts from Monday to Sunday every week and the first 4 hours claimed by our staff will be calculated by normal rate while the remaining hours will be calculated by extra rate.



File: Sample Data is under sheet "Sample Data" and the expected result is under sheet "Result".

Coding is as follows (multiple IF statements to check whether the 1st 4 hours have been splitted to calculate the hours at normal rate and I suspect that the coding fails to cater for some scenarios)

For Each WS In wb.Worksheets
    Select Case WS.Name
        Case "Officer_List", "Sheet1"
        Case Else
            For i = wb.Sheets(WS.Name).Range("A1").End(xlDown).Row To wb.Sheets(WS.Name).Range("A1048576").End(xlUp).Row
                If IsEmpty(wb.Sheets(WS.Name).Range("A" & i)) Then
                    GoTo next_row:
                End If
                For j = wb.Sheets(WS.Name).Range("A1").End(xlToRight).Column To wb.Sheets(WS.Name).Range("XFD1").End(xlToLeft).Column
                    If Not IsEmpty(wb.Sheets(WS.Name).Cells(1, j)) Then
                        Src_DataRow = wb.Sheets(WS.Name).Range("A" & i)
                        Set Srh_DataRow = wb.Sheets(OT_Data).Range("C1:C1048576").Find(what:=Src_DataRow, LookIn:=xlValues, lookat:=xlWhole)
                        If Srh_DataRow Is Nothing Then
                            MsgBox " New Employee ID " & Src_DataRow & " with OT hours Found"
                            Exit Sub
                        End If
                        Src_DataCol = wb.Sheets(WS.Name).Cells(1, j)
                        Set Srh_DataCol = wb.Sheets(OT_Data).Range("L18:AP18").Find(what:=Src_DataCol, LookIn:=xlValues, lookat:=xlWhole)
                        If Srh_DataCol Is Nothing Then
                            MsgBox " New Month Date " & Src_DataCol & " with OT hours Found"
                            Exit Sub
                        End If
                        If wb.Sheets(WS.Name).Cells(i, j).Value = 0 Or wb.Sheets(WS.Name).Cells(i, j).Value = "" Then
                            wb.Sheets("OT_Data").Cells(Srh_DataRow.Row, Srh_DataCol.Column).Value = wb.Sheets(WS.Name).Cells(i, j).Value
                            Accumulated_OT = Accumulated_OT + wb.Sheets(WS.Name).Cells(i, j).Value
                            If Weekday(Src_DataCol, vbMonday) = 7 Then
                                Accumulated_OT = 0
                            End If
                        ElseIf wb.Sheets(WS.Name).Cells(i, j).Value >= 4 And Accumulated_OT = 4 Then
                            wb.Sheets("OT_44_Upload_Data").Cells(Srh_DataRow.Row, Srh_DataCol.Column).Value = 0
                            wb.Sheets("OT_44_Upload_Data").Cells(Srh_DataRow.Row, Srh_DataCol.Column).Offset(1, 0).Value = wb.Sheets(WS.Name).Cells(i, j).Value
                            If Weekday(Src_DataCol, vbMonday) = 7 Then
                                Accumulated_OT = 0
                            End If
                        ElseIf wb.Sheets(WS.Name).Cells(i, j).Value >= 4 And Accumulated_OT < 4 Then
                            wb.Sheets("OT_Data").Cells(Srh_DataRow.Row, Srh_DataCol.Column).Value = 4 - Accumulated_OT
                            wb.Sheets("OT__Data").Cells(Srh_DataRow.Row, Srh_DataCol.Column).Offset(1, 0).Value = _
                            wb.Sheets(WS.Name).Cells(i, j).Value - wb.Sheets("OT_Data").Cells(Srh_DataRow.Row, Srh_DataCol.Column).Value
                            Accumulated_OT = Accumulated_OT + wb.Sheets("OT_Data").Cells(Srh_DataRow.Row, Srh_DataCol.Column).Value
                            If Weekday(Src_DataCol, vbMonday) = 7 Then
                                Accumulated_OT_44 = 0
                            End If
                        ElseIf wb.Sheets(WS.Name).Cells(i, j).Value >= 0.01 And wb.Sheets(WS.Name).Cells(i, j).Value < 4 And Accumulated_OT = 4 Then
                            wb.Sheets("OT_Data").Cells(Srh_DataRow.Row, Srh_DataCol.Column).Value = 0
                            wb.Sheets("OT_Data").Cells(Srh_DataRow.Row, Srh_DataCol.Column).Offset(1, 0).Value = wb.Sheets(WS.Name).Cells(i, j).Value
                            If Weekday(Src_DataCol, vbMonday) = 7 Then
                                Accumulated_OT = 0
                            End If
                        ElseIf wb.Sheets(WS.Name).Cells(i, j).Value >= 0.01 And wb.Sheets(WS.Name).Cells(i, j).Value < 4 Then
                            wb.Sheets("OT_Data").Cells(Srh_DataRow.Row, Srh_DataCol.Column).Value = wb.Sheets(WS.Name).Cells(i, j).Value
                            Accumulated_OT = Accumulated_OT + wb.Sheets("OT_Data").Cells(Srh_DataRow.Row, Srh_DataCol.Column).Value
                            If Weekday(Src_DataCol, vbMonday) = 7 Then
                                Accumulated_OT = 0
                            End If
                        Else: MsgBox "Check!"
                    End IF
                End If
            Next j
        next_row:
    Next i
End Select