VBScript only provides basic calendar functions - so we have to do a little work to enhance it. Week numbers are a little tricky because there are 3 methods how to determine the first week in a year:
- the first complete week in a year
- the first week with at least 4 weekdays (ISO week)
- the week that contains the first January
This sample will show the calculation using the ISO method but I think it's simple to adapt it to any other methods.
Using the Code
Now... how it's done: the ISO method defines the first week in a year as the first week that contains at least 4 weekdays - in other words: 4th January will always be in the first week. Let's get this date in the given year first:
datFourthJan = DateSerial(intDesiredYear, 1, 4)
The next step is to get the start of the week containing 4th January. Maybe 4th January itself is a Monday - otherwise we have to step back to it. The
Weekday function of VBScript will give us an index number for each day in a week, depending on the localization (country). By default, it starts with Sunday = 1, Monday = 2, .....Saturday = 7. Passing 0 (zero) as the second parameter we'll tell the function to use localized indexing - in Germany the week then starts with Monday = 1 and so forth...
By subtracting the day index of 4th January from its already calculated date, we'll get the start day of its week, e.g.: if 4th January is a Tuesday (index = 2) - to get the Monday, we have to subtract as follows:
datFirstWeekMonday = datFourthJan - WeekDay(datFourthJan, 0) + 1
Don't forget to add 1 at the end because weekday indexing is 1-based, not zero-based!
Keep in mind to adapt this calculation to your needs if you use a localization having a different weekday indexing or use the second parameter to explicitly specify the index system!
Now we have found the date the first week in year starts with, it's a simple thing to add the count of days towards the start date of our given week number. Assumed that we are looking for the 10th week of the year, we have to calculate:
weeknr = 10
datDesiredWeekStart = DateAdd("d", ((weeknr - 1) * 7), datFirstWeekMonday)
Note that we have to subtract 1 from the desired week in calculation because we're already starting with week number 1!
The last thing to do is to step forward from the first day in week towards the weekday we are looking for. Again we use the weekday index to calculate the difference between the two weekdays (if not the same):
intDesiredWeekDay = 5
datDesiredDate = DateAdd("d", (intDesiredWeekDay - 1), datDesiredWeekStart)
Again, we subtracted 1 from the desired weekday index because the Monday equals 1 (not 0) using e.g. German localization. Using different localization/indexing requires to adapt the calculation!
To shorten the steps above, we can reduce them to a single line formula:
datDesiredDate = DateAdd("d", ((weeknr - 1) * 7) + (intDesiredWeekDay - 1), _
(DateSerial(intDesiredYear,1,4) - WeekDay(DateSerial(intDesiredYear,1,4), 0) + 1))
We're done - that's all folks!