VBA – What day is Friday in the week of a given date?

Imagine that you want to check when is Friday for the week of Christmas in the next 10 years. In general, the week of Christmas is the week that contains the 25.December.

Getting a given day from a given week is quite an issue all over the world – mainly because the first day of the week is different – Saturday, Sunday or Monday in general:

Map from Reddit

Thus, whenever writing such code, 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):

This gives the dates of the current week:

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

Once you have it, if you want the Friday of the week of Christmas in the next 10 years, this would be ok:

Option Explicit

Public Sub FindChristmasFriday()

    Dim christmasDay    As Date
    Dim yearCnt         As Long
    
    christmasDay = DateSerial(Year(Date), 12, 25)
    
    For yearCnt = 0 To 9
        christmasDay = DateAdd("yyyy", 1, christmasDay)
        Debug.Print "In " & Year(christmasDay) & " Christmas is on " & _
            WeekdayName(Weekday(christmasDay, vbMonday), , vbMonday); ""
        Debug.Print "Friday is on " & christmasDay - Weekday(christmasDay, vbMonday) + 5
        Debug.Print "--------------------------"
    Next yearCnt
    
End Sub

It loops from 0 to 9, incrementing the Christmas Day with 1 year every time. Then it prints the Christmas day and the Friday of the given week. The week explicitly starts on Monday, as vbMonday is mentioned twice:

In 2019 Christmas is on Wednesday
Friday is on 12/27/2019
--------------------------
In 2020 Christmas is on Friday
Friday is on 12/25/2020
--------------------------
In 2021 Christmas is on Saturday
Friday is on 12/24/2021
--------------------------
In 2022 Christmas is on Sunday
Friday is on 12/23/2022
--------------------------
In 2023 Christmas is on Monday
Friday is on 12/29/2023
--------------------------
In 2024 Christmas is on Wednesday
Friday is on 12/27/2024
--------------------------
In 2025 Christmas is on Thursday
Friday is on 12/26/2025
--------------------------
In 2026 Christmas is on Friday
Friday is on 12/25/2026
--------------------------
In 2027 Christmas is on Saturday
Friday is on 12/24/2027
--------------------------
In 2028 Christmas is on Monday
Friday is on 12/29/2028
--------------------------

Check yourself:

For the last 20 Christmases, this is the code that you might need:

Public Sub WriteChristmasDayInLast20Years()

    Dim christmasDay As Date, currentChristmasDay As Date
    Dim i As Long
    Dim wks As Worksheet: Set wks = Sheet2
        
    wks.Cells.Clear
    currentChristmasDay = DateSerial(Year(Date), 12, 25)
    
    For i = 1 To 20 Step 1
        christmasDay = DateSerial(Year(currentChristmasDay) - i, Month(currentChristmasDay), Day(currentChristmasDay))
        Debug.Print christmasDay
        Debug.Print VBA.WEEKDAYNAME(Weekday(christmasDay))
        wks.Cells(i, "A") = christmasDay
        wks.Cells(i, "B") = VBA.WEEKDAYNAME(Weekday(christmasDay))
    Next
    
End Sub