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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 |