CODEWITHSUNDEEP
vbaexcel
The Gootch
The Gootch

Reputation: 85

VBA vbWednesday Not Working

This has always worked for me to get me the current week's Thursday date.

This week it's giving me problems and showing last week's Thursday.

Can someone help me understand why this is happening?

Format$(Date - Weekday(Date, vbWednesday) + 2, "m.d.yy")

Thanks in advance!

Upvotes: 1

Views: 182

Answers (1)

Vityata
Vityata

Reputation: 43593

All over the world the first day of the week is different - Saturday, Sunday or Monday in general:

enter image description here Map from reddit

Thus, whenever we are writing such code, we should try to specify the first day of the week or take care of it. In the code below, I am specifying the first of the week is vbMonday (different from the vbSunday default):

enter image description here

Public Sub TestMe()

    'Sunday of the current week:
    Debug.Print Date - Weekday(Date, vbMonday) + 0

    'Monday:
    Debug.Print Date - Weekday(Date, vbMonday) + 1

    'Tuesday:
    Debug.Print Date - Weekday(Date, vbMonday) + 2

    'Wednesday:
    Debug.Print Date - Weekday(Date, vbMonday) + 3

    'Thursday:
    Debug.Print Date - Weekday(Date, vbMonday) + 4

    'Friday:
    Debug.Print Date - Weekday(Date, vbMonday) + 5

    'Saturday:
    Debug.Print Date - Weekday(Date, vbMonday) + 6

End Sub

Check this simulation for the first 366 days after 1.January.Current Year. For every day, it prints the Thursday of the current week, assuming that the week starts on Monday.

Public Sub TestMe()

    Dim currentDate As Date
    Dim myDay       As Long

    For myDay = 1 To 366
        currentDate = DateAdd("d", myDay, DateSerial(Year(Date), 1, 1))
        Debug.Print "Today is " & currentDate & " " & _
            WeekdayName(Weekday(currentDate, vbMonday), 1, vbMonday)
        Debug.Print "Thursday is on " & _
            currentDate - Weekday(currentDate, vbMonday) + 4
    Next myDay
End Sub

This is what it prints:

Today is 29.12.2018 Sa
Thursday is on 27.12.2018
Today is 30.12.2018 Su
Thursday is on 27.12.2018
Today is 31.12.2018 Mo
Thursday is on 03.01.2019
Today is 01.01.2019 Tu
Thursday is on 03.01.2019

MSDN How to Find the First and Last Day in a Given Week, Month

Upvotes: 2

Related Questions