CODEWITHSUNDEEP
vbaexcel
sbagnato
sbagnato

Reputation: 496

Excel VBA - Weekday and/or datediff function not working properly

I have the code below which is supposed to parse a column with dates/times and advise (by highlighting) if the value is older than 24 hours ago as of 6:30am on the current day. If the day is Monday, it is supposed to look back 72 hours (since Saturday and Sunday are not work days) and advise if the value is older than 72 hours as of 6:30am.

Something is amiss as it is not highlighting values that are breaching the 24 hours threshold, but I don't know what it is.

'Highlight breached tickets
Dim updateRange As Range, updateCell As Range
Set updateRange = Range("D2:D" & lastRow)
If Weekday(Date, vbMonday) Then
    For Each updateCell In updateRange
        If DateDiff("h", CDate(updateCell.Value), CDate(Format(Now(), "mm/dd/yy")) + TimeSerial(6, 30, 0)) > 72 Then
            updateCell.Interior.Color = 13311
            updateCell.Offset(0, -3).Interior.Color = 13311
        End If
    Next updateCell
Else
    For Each updateCell In updateRange
        If DateDiff("h", CDate(updateCell.Value), CDate(Format(Now(), "mm/dd/yy")) + TimeSerial(6, 30, 0)) > 24 Then
            updateCell.Interior.Color = 13311
            updateCell.Offset(0, -3).Interior.Color = 13311
        End If
    Next updateCell
End If

Upvotes: 1

Views: 1111

Answers (2)

Tom
Tom

Reputation: 9878

As others have said you need to test Weekday against something as it doesn't return a Boolean value. However, thought I'd add this as you could re-write your code so you don't repeat yourself as nearly all of it is doing exactly the same

'Highlight breached tickets
Dim updateRange As Range, updateCell As Range
Dim TimeDiff As Long
Set updateRange = Range("D2:D" & Lastrow)

TimeDiff = IIf(Weekday(Date, vbMonday) = 1, 72, 40)

For Each updateCell In updateRange
    If DateDiff("h", CDate(updateCell.Value), CDate(Format(Now(), "mm/dd/yy")) + TimeSerial(6, 30, 0)) > TimeDiff Then
        updateCell.Interior.Color = 13311
        updateCell.Offset(0, -3).Interior.Color = 13311
    End If
Next updateCell

Writing it this way makes it much easier to maintain

Update after comments To modify this to cope with other days as well, the simplest (and easiest to read) would be to change the TimeDiff = line and using a Select Case statement to set the value

'Highlight breached tickets
Dim updateRange As Range, updateCell As Range
Dim TimeDiff As Long
Set updateRange = Range("D2:D" & Lastrow)

Select Case Weekday(Date, vbMonday)
    ' Monday
    Case 1
        TimeDiff = 72
    ' Tuesday
    Case 2
        TimeDiff = 96
    ' Any other day
    Case Else
        TimeDiff = 40
End Select

For Each updateCell In updateRange
    If DateDiff("h", CDate(updateCell.Value), CDate(Format(Now(), "mm/dd/yy")) + TimeSerial(6, 30, 0)) > TimeDiff Then
        updateCell.Interior.Color = 13311
        updateCell.Offset(0, -3).Interior.Color = 13311
    End If
Next updateCell

Upvotes: 2

Vityata
Vityata

Reputation: 43593

As mentioned by Kostas K. in the comments, Weekday() returns an Integer number from 1 to 7, which is always evaluated to a True boolean - MSDN Weekday!

However, if you use If Weekday(Date, vbMonday) = vbMonday most probably you will get a False if the today is Monday.

That is because the default day of the beginning of the Weekday is Sunday. And you are changing it to Monday. And from the contrary, vbMonday is always evaluated to 2 (write ?vbMonday at the immediate window)

Anyhow, it is probably a bit easier to see it than to explain it. Today it is Friday (TGIF). Thus, simply run the following code:

Public Sub TestMe()
    Debug.Print Weekday(Date) = vbFriday            'True
    Debug.Print Weekday(Date, vbMonday) = vbFriday  'False
End Sub

Upvotes: 1

Related Questions