CODEWITHSUNDEEP
excelvba
James Smith
James Smith

Reputation: 13

Excel Logic Issue

I've developed a simple vba function to work out if someone is working after hours. This is the original query I wrote:

Public Function AfterHours(StartTime As Date, EndTime As Date, ActivityDate As Date)

AfterHours = False

If (Weekday(ActivityDate) <> 1 OR Weekday(ActivityDate) <> 7) Then
    If Hour(StartTime) >= 19 Or Hour(EndTime) < 7 Then
        AfterHours = True
    End If
Else
    AfterHours = True
End If
End Function

This query however does not pick up staff who work on the weekend during normal business hours. If I change it to:

Public Function AfterHours(StartTime As Date, EndTime As Date, ActivityDate As Date)

AfterHours = False

If (Weekday(ActivityDate) = 1 Or Weekday(ActivityDate) = 7) Then
    AfterHours = True
Else
    If Hour(StartTime) >= 19 Or Hour(EndTime) < 7 Then
        AfterHours = True
    End If
End If
End Function

The query functions correctly. The logic for both is the same, just inverted. In the first function if it is not the weekend it should test whether it is outside business hours, otherwise it is the weekend and should be flagged. The second function checks if it is the weekend and flags it, otherwise it checks whether it is outside business hours.

I don't understand why these queries return different results.

Upvotes: 1

Views: 78

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

Your first If statement is wrong.

If (Weekday(ActivityDate) <> 1 OR Weekday(ActivityDate) <> 7) Then

Because you use Or this will always be true. Use And instead.

Upvotes: 3

Related Questions