(VBA) Converting a regular IF Statement to a VBA "FORMULA".

[Ryan Saari]

I need help converting this if statement I created to a "FORMULA" in vba.

This formula is used to subtract unpaid breaks from total hours worked in a day.

For Example:

J5 = 5:00:00am
H5 = 8:30:00am

Formula will conver J5 to pm, then subtrack J5 from H5 = 8.5 hours and then based on the hours worked of 8.5 subtracts 1 hour for an unpaid lunch.

=IF(J5=0, 0, IF((((J5+0.5)-H5)*24)<=4,(((J5+0.5)-H5)*24)-0,IF((((J5+0.5)-H5)*24)<6.5,(((J5+0.5)-H5)*24)-0.5,IF((((J5+0.5)-H5)*24)>=6.5,(((J5+0.5)-H5)*24)-1,IF((((J5+0.5)-H5)*24)>8.5,"OVR","")))))

My VBA code using FORMULA(START,END) results in nothing but errors.

Help!

 

[xenou]

I haven't actually tried to decipher your If() Formula, but here's a sample function. (it could be more concise, but at least it's easy to follow the logic, I hope). You will need to adjust the "Select Case" part of the code to actually work out your real lunch deductions.

Public Function HRSWRKD(dtmStart, dtmEnd) As Variant

'-------------------Test for Invalid Time Entry
If Not (IsNumeric(dtmStart) And IsNumeric(dtmEnd)) Then
    HRSWRKD = "ERROR" 'Not times
    Exit Function 'Exit
End If

If dtmStart > 1 Then
    HRSWRKD = "ERROR" 'over 23.99 hours
    Exit Function 'Exit
End If

If dtmEnd > 1 Then
    HRSWRKD = "ERROR" 'over 23.99 hours
    Exit Function 'Exit
End If


'-------------------Gross Hours
HRSWRKD = (dtmEnd - dtmStart) * 24


'-------------------Subtract Lunch
Select Case HRSWRKD
    Case Is <= 4
        HRSWRKD = HRSWRKD - 0.25
    Case Is <= 6.5
        HRSWRKD = HRSWRKD - 0.5
    Case Is <= 8
        HRSWRKD = HRSWRKD - 1
    Case Else
        HRSWRKD = "OVR"
End Select


End Function
'-------------------
Sub test()
Dim x
x = HRSWRKD([a1].Value, [b1].Value)
End Sub