Translating Excel "If" statement into VBA "IF/Else" statement

[Dragonman86]

I have created a "Labor Sheet" to help track the labor hours, labor dollars, total time, strait time, and over time (among other things) of the employees who work under me. While setting up the spreadsheet, I wrote a rather large nested "IF" statement which rests within each cell because I am trying to break the hours worked down by shift. The "IF" statement is working the way it is supposed to and is as follows

=IF(W1D1ActualStart>W1D1ShiftChange,IF(AND(W1D1ActualStart>W1D1ShiftChange,W1D1ActualEnd>W1D1ShiftChange),
     IF(AND(W1D1ActualStart<w1d1shiftchange,w1d1actualend<w1d1shiftchange),
        MOD(W1D1ActualEnd-W1D1ActualStart,1),
        IF(AND(W1D1ActualStart<w1d1shiftchange,w1d1actualend>W1D1ShiftChange),
           MOD(W1D1ShiftChange-W1D1ActualStart,1),
           MOD(W1D1ActualEnd-W1D1ActualStart,1))),
     IF(AND(W1D1ActualStart>W1D1ShiftChange,W1D1ActualEnd<w1d1shiftchange,w1d1actualend<0.58),
        MOD(W1D1ActualEnd-W1D1ActualStart,1),
        0)),
IF(AND(W1D1ActualStart<w1d1shiftchange,w1d1actualend<w1d1shiftchange,w1d1actualstart<0.58),
    0,
    IF(AND(W1D1ActualStart<w1d1shiftchange,w1d1actualend>W1D1ShiftChange),
       MOD(W1D1ActualEnd-W1D1ShiftChange,1),
       IF(AND(W1D1ActualStart<w1d1shiftchange,w1d1actualend<w1d1shiftchange,w1d1actualstart>0.58,W1D1ActualEnd<0.58),
          MOD(W1D1ActualEnd-W1D1ShiftChange,1),
          0))))*24

In order to make it easier to update the entire spreadsheet, I have tried to put this "IF" statement into a UDF. I have the formula working for one of the inputs, but as soon as I try and have it calculate the next set of times, it returns an incorrect time. The following is my VBA:

Function Shift2(StartTime, EndTime, ShiftChange)
    If StartTime > ShiftChange Then
        If StartTime > ShiftChange And EndTime > ShiftChange Then
            If StartTime < ShiftChange And EndTime < ShiftChange Then
                Shift2 = ((EndTime - StartTime) / 1) * 24
            Else
                If StartTime < ShiftChange And EndTime > ShiftChange Then
                    Shift2 = ((ShiftChange - StartTime) / 1) * 24
                Else
                    Shift2 = ((EndTime - StartTime) / 1) * 24
                End If
            End If
        Else
            If StartTime > ShiftChange And EndTime < ShiftChange And EndTime < 0.58 Then
                Shift2 = (EndTime - StartTime) * 24
            Else
                Shift2 = 0
            End If
        End If
    Else
        If StartTime < ShiftChange And EndTime < ShiftChange And StartTime < 0.58 Then
            Shift2 = 0
        Else
            If StartTime < ShiftChange And EndTime > ShiftChang Then
                Shift2 = ((EndTime - ShiftChange) / 1) * 24
            Else
                If StartTime < ShiftChange And EndTime < ShiftChange And StartTime > 0.58 And EndTime < 0.58 Then
                    Shift2 = ((EndTime - ShiftChange) / 1) * 24
                Else
                    Shift2 = 0
                End If
            End If
        End If
    End If
End Function

The first sets of times (which are working) are "Start Time": 08:37, "End Time": 18:48, and "Shift Change": 18:37. The second set of times (which are not working) are "Start Time": 20:55, "End Time": 00:45, "Shift Change": 18:37.

The result for the second set of times which the Excel "IF" statement returns is 3.83, however the result for the VBA statement is -20.17.

I am not sure where, if at all, I have messed up and would appreciate any and all help

</w1d1shiftchange,w1d1actualend<w1d1shiftchange,w1d1actualstart></w1d1shiftchange,w1d1actualend></w1d1shiftchange,w1d1actualend<w1d1shiftchange,w1d1actualstart<0.58),
</w1d1shiftchange,w1d1actualend<0.58),
</w1d1shiftchange,w1d1actualend></w1d1shiftchange,w1d1actualend<w1d1shiftchange),

 

[StephenCrump]

I am not sure if this would work as I am unclear of the logic path that the calculations fallow, also how would you calculate hours worked for those shifts which pass over the midnight hour such as the shift shown in Row 3 (Greg (Split))?

So far, all I have done is replicate your results.

If you're saying now that you don't know how the calculations should be done, that's something you'll need to check with your payroll people.

We can help you with Excel/VBA, but we can't tell you what you should be paying your employees.

 

[Dragonman86]

The second set of formulas did not seem like the same calculations that I was doing, that is why I am having a slight problem following the logic of the equations:

E2: =24*(C2+IF(C2 < B2,1,0)-B2)-F2
F2: =24*MAX(0,C2+IF(C2 < B2,1,0)-MAX(B2,D2))

To me this seams like a completely different logic path than my original IF() statement... if it is not that is fine too.