UDF. If "not a date" set output to "0"

[AusSteelMan]

Hi guys,

Just a small one I can't get right.

Public Function OtHours(Workday As Date, Hours As Single)
 
Dim DayNumber As Integer
 
DayNumber = Weekday(Workday, vbSunday)
 
If Hours = 0 Then OtHours = 0
If Workday = 0 Then OtHours = 0
 
If DayNumber = 1 Then
    OtHours = Hours * 2
Else
    If Hours > 2 Then
        OtHours = 3 + ((Hours - 2) * 2)
    Else
        OtHours = Hours * 1.5
    End If
End If
End Function

The problem is if the "Workday" cell is left empty, the date is seen as 12:00:00 AM according to the watch, not 0.

What do I need to change in the following line to make it valid

If Workday = 0 Then OtHours = 0

I tried a few things (incl Nothing, "12:00:00 am" amongst others) but can't get it.

Everything is OK if the hours are 0 and the calcs work properly when both are valid arguments, just doesn't work how I intended if there is no date (or even if it is text: although I am happy if text is entered for the result to be #VALUE!)

Any help appreciated

Thanks
ASM

 

[Jonmo1]

Try

If Not Isdate(Workday) Then OtHours = 0

 

[Jonmo1]

Oh wait, I see...

You need to tell it to stop executing the rest of the code in the function...

Try

If Not Isdate(Workday) Then 
    OtHours = 0
    Exit Function
End if

 

[AusSteelMan]

Hi Jonmo1,

2 things.

1. I tried to End Function not Exit Function, so thanks for clearing that up.

2. Still doesn't work as it does recognise a blank cell as a date. I think that is because it identifies it "Workday As Date" in the 1st line.

When I step through the Function, I can see that the test is applied, but it does not fail so proceeds to the the step.

Can you see any other way around it?

Thanks,
ASM

 

[Jonmo1]

Hmm, ok it's becaue workday is dimmed as date..

You can either

1. Dim worday as Variant
2. Or try If workday <=1 Then

You might also consider changing the name of that variable...
Workday may be confused with other built in functions...

Try wkdy

 

[AusSteelMan]

Here is what I ended up with. Seems to work OK. Will only get a #VALUE! result if text is entered - and I am ok with that.

I just wanted to be able to enter the date and have it figure out what day of the week it was.

Note: I changed from workday, to wrkday but it had no impact.

Public Function OtHours(Wrkday As Date, Hours As Single)
 
Dim DayNumber As Integer
 
If IsEmpty(Wrkday) Then
    OtHours = 0
    Exit Function
End If
 
DayNumber = Weekday(Wrkday, vbSunday)
 
If Hours <= 0 Then OtHours = 0
 
If DayNumber > 0 Then
    If DayNumber < 8 Then
 
        If DayNumber = 1 Then
            OtHours = Hours * 2
        Else
            If Hours > 2 Then
                OtHours = 3 + ((Hours - 2) * 2)
            Else
                OtHours = Hours * 1.5
            End If
        End If
    Else
        OtHours = 0
    End If
Else
    OtHours = 0
End If
 
End Function

Thanks jonmo for your thoughts and help.

ASM