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:

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

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:

Check yourself:

Tagged with: , , ,