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

D

Dragonman86

New Member
Joined
Mar 16, 2016
Messages
7
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

Code: 
=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:

Code: 
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),
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
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.

Code: 
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),
 
Upvote 0
Code: 
=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:

Code: 
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.
Click to expand...

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.

Code: 
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.
 
Upvote 0
Code: 
   =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
 
Upvote 0
Dragonman86 said:
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
Click to expand...

< 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.

Code: 
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
 
Upvote 0
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.
 
Upvote 0
Dragonman86 said:
Changing the formula's calculations to mach the ones you have in your code worked perfectly ...
Click to expand...

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

ABCD
ShiftChange

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Start[/TD]
[TD="align: right"]End[/TD]

[TD="align: right"]Result[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]23:55[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]23:55[/TD]
[TD="align: right"]13:55[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0:05[/TD]
[TD="align: right"]13:55[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]0[/TD]

</tbody>

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

ABCD
ShiftChange

<tbody>
[TD="align: center"]6[/TD]
[TD="align: right"]Start[/TD]
[TD="align: right"]End[/TD]

[TD="align: right"]Result[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]4:00[/TD]
[TD="align: right"]2:00[/TD]
[TD="align: right"]12[/TD]

</tbody>
​
 
Upvote 0
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.
Click to expand...

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):
Click to expand...

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

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

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*
 
Upvote 0
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))


ABCDEF
Employee
Greg
Greg (Split)
Char
Dakota
Libby
Robert
Carl

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Start Time[/TD]
[TD="align: right"]End Time[/TD]
[TD="align: right"]Shift Change[/TD]
[TD="align: right"]Day Shift[/TD]
[TD="align: right"]Night Shift[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8:37[/TD]
[TD="align: right"]18:48[/TD]
[TD="align: right"]18:37[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]0.18[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]20:55[/TD]
[TD="align: right"]0:45[/TD]
[TD="align: right"]18:37[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]3.83[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]17:53[/TD]
[TD="align: right"]20:55[/TD]
[TD="align: right"]18:37[/TD]
[TD="align: right"]0.73[/TD]
[TD="align: right"]2.30[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]12:58[/TD]
[TD="align: right"]16:06[/TD]
[TD="align: right"]18:37[/TD]
[TD="align: right"]3.13[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]17:55[/TD]
[TD="align: right"]22:26[/TD]
[TD="align: right"]18:37[/TD]
[TD="align: right"]0.70[/TD]
[TD="align: right"]3.82[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]15:27[/TD]
[TD="align: right"]21:09[/TD]
[TD="align: right"]18:37[/TD]
[TD="align: right"]3.17[/TD]
[TD="align: right"]2.53[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10:31[/TD]
[TD="align: right"]15:51[/TD]
[TD="align: right"]18:37[/TD]
[TD="align: right"]5.33[/TD]
[TD="align: right"]0.00[/TD]

</tbody>

</b2,1,0)-max(b2,d2))
</b2,1,0)-b2)-f2
 
Upvote 0
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))?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
color cell in red, when entering empty time value
Replies
2
Views
166
robertvdb
R
S
  • Question Question
Using VBA to Flag and Calculate Overlapping Shift Times
Replies
0
Views
452
Snort
S
pcc
  • Question Question
Can anyone explain this behaviour?
Replies
5
Views
345
Jeffrey Mahoney
Jeffrey Mahoney
P
  • Question Question
Cycle Time - Macro Help
Replies
5
Views
447
offthelip
O
N
  • Question Question
Infinite loop slideshow
Replies
1
Views
184
Domenic
D

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top