RSS

VBScript: nth Day of the month

24 Dec

Recently I needed a function to calculate the nth day of the month, that is, first sunday, 2nd thursday, last friday, etc. I thought such functions would be easy to find in the internet, however, I couldn’t find a complete function and so decided to write one:

Apologise for the formatting, wordpress don’t like programming codes in their blogs

Option Explicit

Wscript.echo nthDayOfMonth(now(), 4, "wed") 'get 4th wed of this month
Wscript.echo nthDayOfMonth("22/05/2007", 3, "wed") 'get 3rd wed of ref month/year

'======================================================
Function nthDayOfMonth(strRefDate, IntOrder, strDayName)
'======================================================

'calculates the first, 2nd...last day (sun ... sat) based on the ref date
'Returns the date in dd/mm/yyyy format

'strRefDate = A reference date for the calculation: format dd/mm/yyyy
'IntOrder = 1 - 5 (1=first, 2=2nd, >=5: last)
'strDayName = sun, mon, tue, wed, thu, fri, sat

Dim dicDayNum
Dim intRefMonth
Dim intRefYear
Dim dtFirstDay, dtLastDay
Dim intFirstDayNum, intLastDayNum
Dim strDayNameL
Dim intBackStep, intFwdStep

nthDayOfMonth = ""

Set dicDayNum = CreateObject("Scripting.Dictionary")
dicDayNum.Add "sun", 1
dicDayNum.Add "mon", 2
dicDayNum.Add "tue", 3
dicDayNum.Add "wed", 4
dicDayNum.Add "thu", 5
dicDayNum.Add "fri", 6
dicDayNum.Add "sat", 7

strDayNameL = lcase(strDayName)

if intOrder <= 0 then exit function
if dicDayNum.item(strDayNameL) = "" then exit function


intRefMonth = month(strRefDate)
intRefYear = year(strRefDate)
dtFirstDay = dateserial(intRefYear, intRefMonth, 1)

if IntOrder >= 5 then 'calculate the last xx day of month

dtLastDay = (DateAdd("m", 1, dtFirstDay)) - 1
intLastDayNum = weekday(dtLastDay, vbSunday)

if dicDayNum.item(strDayNameL) = intLastDayNum then
nthDayOfMonth = dtLastDay

elseif intLastDayNum < dicDayNum.item(strDayNameL) then
intBackStep = -((7 + intLastDayNum) - dicDayNum.item(strDayNameL))

nthDayOfMonth = DateAdd("d", intBackStep, dtLastDay)

else
intBackStep = dicDayNum.item(strDayNameL) - intLastDayNum
nthDayOfMonth = dateadd("d", intBackStep, dtLastDay)

end if

else 'calculates the other nth order days

intFirstdayNum = weekday(dtFirstDay, vbSunday)

if dicDayNum.item(strDayNameL) = intFirstdayNum then
intFwdStep = (intOrder -1) * 7
nthDayOfMonth = dateadd("d", intFwdStep, dtfirstday)

elseif intFirstdayNum > dicDayNum.item(strDayNameL) then
intFwdStep = (7 - intFirstdayNum) + dicDayNum.item(strDayNameL) + ((intOrder -1) * 7)
nthDayOfMonth = dateadd("d", intFwdStep, dtFirstDay)

else
intFwdStep = dicDayNum.item(strDayNameL) - intFirstdayNum + ((intOrder -1) * 7)
nthDayOfMonth = dateadd("d", intFwdStep, dtFirstDay)

end if

end if

End Function

Advertisements
 
3 Comments

Posted by on December 24, 2008 in Scripts, VBScripts

 

Tags:

3 responses to “VBScript: nth Day of the month

  1. SMarshall

    October 27, 2009 at 9:55 pm

    Excellant function. Exactly what I was looking for, thanks.

     
  2. alexk013

    May 11, 2012 at 8:38 am

    Perfect, just was I have been looking for.

     
  3. alexk013

    May 11, 2012 at 8:55 am

    Reblogged this on alexk013 and commented:
    Exactly what I was looking for for figuring out the date of every 3rd Wednesday of each month.
    WIll work perfectly for a small project I am working on (more on this later)

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: