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]

Welcome to the Forum!

Your IF() statement hasn't posted correctly, and the VBA logic doesn't make sense, e.g. in the code snippet below, the lines in red will never be executed.

If StartTime > ShiftChange Then
    If StartTime > ShiftChange And EndTime > ShiftChange Then
        If StartTime < ShiftChange And EndTime < ShiftChange Then
[COLOR=#ff0000]            Shift2 = ((EndTime - StartTime) / 1) * 24[/COLOR]
        Else
            If StartTime < ShiftChange And EndTime > ShiftChange Then
[COLOR=#ff0000]                Shift2 = ((ShiftChange - StartTime) / 1) * 24[/COLOR]
            Else
                Shift2 = ((EndTime - StartTime) / 1) * 24
            End If
        End If
    Else
...

Your code also has a typo (ShiftChang) which will cause you problems unless you are using Option Explicit?

It's also not clear what the various hard-coded references to 0.58 are designed to do.

Instead, can you perhaps explain how you want the Shift2 results to be calculated, and provide some examples with the results you expect to see.

<w1d1shiftchange,w1d1actualend<w1d1shiftchange),

</w1d1shiftchange,w1d1actualend<w1d1shiftchange),

 

[Dragonman86]

=IF(W1D1ActualStart>W1D1ShiftChange,IF(AND(W1D1ActualStart>W1D1ShiftChange,W1D1ActualEnd>W1D1ShiftChange),MOD(W1D1ActualEnd-W1D1ActualStart,1),IF(AND(W1D1ActualStart<W1D1ShiftChange,W1D1ActualEnd<W1D1ShiftChange),MOD(W1D1ActualEnd-W1D1ActualStart,1),IF(AND(W1D1ActualStart<W1D1ShiftChange,W1D1ActualEnd>W1D1ShiftChange),MOD(W1D1ShiftChange-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

Here is my Excel IF() statement again. I have actually gone back and reworked the logic for this statement as well after examining the logic for the VBA (as the two codes should be identical). The new VBA is:

Function Shift2(StartTime, EndTime, ShiftChange)
    If StartTime > ShiftChange Then
        If StartTime > ShiftChange And EndTime > ShiftChange Then
            Shift2 = ((EndTime - StartTime) / 1) * 24
        Else
            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
                    If StartTime > ShiftChange And EndTime < ShiftChange And EndTime < 0.58 Then
                        Shift2 = (EndTime - StartTime) * 24
                    Else
                        Shift2 = 0
                    End If
                End If
            End If
        End If
    Else
        If StartTime < ShiftChange And EndTime < ShiftChange And StartTime < 0.58 Then
            Shift2 = 0
        Else
            If StartTime < ShiftChange And EndTime > ShiftChange 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

It's also not clear what the various hard-coded references to 0.58 are designed to do.

To answer this, when setting up the original IF() statement I needed a set time stamp to help determine which shift the hours worked should be calculated against. This is the two decimal representation of 2pm (14:00) in Excel.

Instead, can you perhaps explain how you want the Shift2 results to be  calculated, and provide some examples with the results you expect to  see.

the result for Shift2, for the second set of times ("Start Time": 20:55, "End Time": 00:45, "Shift Change": 18:37.) should produce a result of 3.83 however it is currently producing a result of -20.17.

 

[Dragonman86]

   =IF(W1D1ActualStart ">" W1D1ShiftChange,IF(AND(W1D1ActualStart ">" W1D1ShiftChange,W1D1ActualEnd ">" W1D1ShiftChange),
   MOD(W1D1ActualEnd-W1D1ActualStart,1),IF(AND(W1D1ActualStart "<" W1D1ShiftChange,W1D1ActualEnd "<" W1D1ShiftChange),
   MOD(W1D1ActualEnd-W1D1ActualStart,1),IF(AND(W1D1ActualStart "<" W1D1ShiftChange,W1D1ActualEnd ">" W1D1ShiftChange),
   MOD(W1D1ShiftChange-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

Sorry it appears that my IF() statement did not post correctly again. It was due to the shear number of "<" and ">" that I had in my code so I added the " " to keep this from happening again

 

[StephenCrump]

Sorry it appears that my IF() statement did not post correctly again. It was due to the shear number of "<" and ">" that I had in my code so I added the " " to keep this from happening again

< and > characters get interpreted as HTML. You can also wrap them in space characters to prevent this happening.

I think the function I've coded below replicates your formula. When it's laid out this way, it's easier to see that you have a lot of redundant testing for various conditions, and both your formula and the VBA could be substantially simplified. I'll leave that to you for the moment.

Function ShiftCalc2(W1D1ActualStart As Double, W1D1ActualEnd As Double, W1D1ShiftChange As Double) As Double

    Dim dTemp As Double
    
    If W1D1ActualStart > W1D1ShiftChange Then
        If W1D1ActualStart > W1D1ShiftChange And W1D1ActualEnd > W1D1ShiftChange Then
            dTemp = W1D1ActualEnd - W1D1ActualStart - Int(W1D1ActualEnd - W1D1ActualStart)
        Else
            If W1D1ActualStart < W1D1ShiftChange And W1D1ActualEnd < W1D1ShiftChange Then
                dTemp = W1D1ActualEnd - W1D1ActualStart - Int(W1D1ActualEnd - W1D1ActualStart)
            Else
                If W1D1ActualStart < W1D1ShiftChange And W1D1ActualEnd > W1D1ShiftChange Then
                    dTemp = W1D1ShiftChange - W1D1ActualStart - Int(W1D1ShiftChange - W1D1ActualStart)
                Else
                    If W1D1ActualStart > W1D1ShiftChange And W1D1ActualEnd < W1D1ShiftChange And W1D1ActualEnd < 0.58 Then
                        dTemp = W1D1ActualEnd - W1D1ActualStart - Int(W1D1ActualEnd - W1D1ActualStart)
                    Else
                        dTemp = 0
                    End If
                End If
            End If
        End If
    Else
        If W1D1ActualStart < W1D1ShiftChange And W1D1ActualEnd < W1D1ShiftChange And W1D1ActualStart < 0.58 Then
            dTemp = 0
        Else
            If W1D1ActualStart < W1D1ShiftChange And W1D1ActualEnd > W1D1ShiftChange Then
                dTemp = W1D1ActualEnd - W1D1ShiftChange - Int(W1D1ActualEnd - W1D1ShiftChange)
            Else
                If W1D1ActualStart < W1D1ShiftChange And W1D1ActualEnd < W1D1ShiftChange And W1D1ActualStart > 0.58 And W1D1ActualEnd < 0.58 Then
                    dTemp = W1D1ActualEnd - W1D1ShiftChange - Int(W1D1ActualEnd - W1D1ShiftChange)
                Else
                    dTemp = 0
                End If
            End If
        End If
    End If
    
    ShiftCalc2 = dTemp * 24

End Function

 

[Dragonman86]

Thank you very much for the help. Changing the formula's calculations to mach the ones you have in your code worked perfectly, so now like you said I just need to figure out a better, simpler way of coding this function.

 

[StephenCrump]

Changing the formula's calculations to mach the ones you have in your code worked perfectly ...

That wasn't the aim of the exercise!

You said you had a formula that was working the way it was supposed to, and you wanted to put this into a UDF.

All I did was translate your formula into VBA. Where your formula used MOD, I had to use a different calculation because MOD behaves differently in VBA.

To simplify the Formula/VBA I'd have to understand what you're trying to do, hence my initial request for examples.

I don't understand the logic your formula uses, e.g. the discontinuity around 2:00pm (=0.58 days):

	A	B	C	D
1	Start	End	ShiftChange	Result
2	23:55	14:00	19:00	0
3	23:55	13:55	19:00	14
4	0:05	13:55	19:00	0

<tbody>
</tbody>

And there are other anomalies that don't immediately make sense, e.g.

	A	B	C	D
6	Start	End	ShiftChange	Result
7	4:00	16:00	14:00	2
8	16:00	4:00	2:00	12

<tbody>
</tbody>

​

 

[Dragonman86]

That wasn't the aim of the exercise!

You said you had a formula that was working the way it was supposed to, and you wanted to put this into a UDF.

The UDF was working for only one set of times, all other times were producing incorrect outputs, however once I corrected the calculations in VBA to match that of Excels MOD function, the formula worked for all time ranges.

I don't understand the logic your formula uses, e.g. the discontinuity around 2:00pm (=0.58 days):

The formula is a small part of a "Labor Spreadsheet" designed to track hours worked, labor dollars, yield per man hour as well as various other items

Let me see if I can explain the goal a little better

Employee	Start Time	End Time	Shift Change	Day Shift	Night Shift
Greg	8:37	18:48	18:37	10.0	.18
Greg (Split)	20:55	00:45	18:37	0	3.83
Char	17:53	20:55	18:37	.73	2.30
Dakota	12:58	16:06	18:37	3.13	0
Libby	17:55	22:26	18:37	.70	3.82
Robert	15:27	21:09	18:37	3.17	2.53
Carl	10:31	15:51	18:37	5.33	0

<tbody>
</tbody>

This function happens to be the one used to calculate the "Night Shift" hours which will then be used to calculate labor dollars, total hours worked, and total shift hours used.

At this point in time I really do not remember my though process when I was setting up the original IF() statement, however when dealing with times which pass over the midnight mark, I found it necessary to insure that the Start and/or End time was well before any possible shift change would be happening. This brings us to the arbitrary 2pm (0.58) time.

*please note that the times and hours listed in the spreadsheet above were pulled directly from my original spreadsheet (although laid out a little differently for the purpose of this example) the actual calculations and hourly break down will be happening on a separate sheet within the work book*

 

[StephenCrump]

So perhaps:

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

	A	B	C	D	E	F
1	Employee	Start Time	End Time	Shift Change	Day Shift	Night Shift
2	Greg	8:37	18:48	18:37	10.00	0.18
3	Greg (Split)	20:55	0:45	18:37	0.00	3.83
4	Char	17:53	20:55	18:37	0.73	2.30
5	Dakota	12:58	16:06	18:37	3.13	0.00
6	Libby	17:55	22:26	18:37	0.70	3.82
7	Robert	15:27	21:09	18:37	3.17	2.53
8	Carl	10:31	15:51	18:37	5.33	0.00

<tbody>
</tbody>

</b2,1,0)-max(b2,d2))
</b2,1,0)-b2)-f2

 

[Dragonman86]

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))?