Click here to Skip to main content
14,693,352 members
Articles » Languages » VBScript » General
Tip/Trick
Posted 3 Dec 2015

Stats

20.9K views
1 bookmarked

Get date from weeknumber and weekday in VBScript

Rate me:
Please Sign up or sign in to vote.
4.67/5 (4 votes)
5 Dec 2015CPOL
Explained step by step

Introduction

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

Background

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 'Friday when using German localization
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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

NightWizzard
Software Developer (Senior)
Germany Germany
30+ years experience as developer with VB.NET, VB, VBA, VBScript, C#, WPF, WinForms, JavaScript, jQuery, PHP, Delphi, ADO, ADO.NET, ASP.NET, Silverlight, HTML, CSS, XAML, XML, T-SQL, MySQL, MariaDb, MS-ACCESS, dBase, OLE/COM, ActiveX, SEPA/DTAUS, ZUGFeRD, DATEV Format and DATEVconnect, DSGVO, TNT Web-API, MS-Office Addins, etc., including:
- 10+ years experience as developer and freelancer
- 10+ years experience as team leader
- 13+ years experience with CRM solutions

Comments and Discussions

 
QuestionVoting Pin
Eric Lapouge5-Dec-15 0:29
MemberEric Lapouge5-Dec-15 0:29 
QuestionISO Pin
Eric Lapouge5-Dec-15 0:28
MemberEric Lapouge5-Dec-15 0:28 
AnswerRe: ISO Pin
NightWizzard5-Dec-15 0:45
MemberNightWizzard5-Dec-15 0:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.