Getting the Date of the nth Weekday in Every Month

[David Morgan]

Hi

Hopefully the subject says it all. Given a weekday, (1 to 7) and an
occurrence with in a month, (1 to 4), I need to ascertain the date on which
it first occurs in any given month and year.

For example, what is the date of the third Tuesday in October 2004.

Has anyone done this before? Would be grateful for some assistance.

Regards

David

 

[Manohar Kamath [MVP]]

Essentially, you need to first find out what day is the first of the month.
Then figure out how many days to the next "x"day and then add it to
mm/1/yyyy

nYear = 2004
nMonth = 2

' xDay = the day you want to find out the date for
xDay = vbTuesDay

' Get the first of the month as a date
yourDate = DateSerial(nYear, nMonth, 1)

' Let us say first of the week is a Sunday (as in the US)
firstOfWeek = vbSunday

' Find out what day is that
nDay = WeekDay(yourDate, firstOfWeek)

' First "xDay" will be as follows
' See if the xDay is beyond the first of the month
' Else you have to go the xDay of next week
If (xDay - nDay) >= 0 Then
firstXDate = DateSerial(nYear, nMonth, 1 + (xDay - nDay))
Else
firstXDate = DateSerial(nYear, nMonth, 1 + (xDay - nDay) + 7)
End If

Hope fully that helps your "date problem"

 

[Manohar Kamath [MVP]]

A small change, for Nth xDay:

' Nth "xDay" will be as follows
' See if the xDay is beyond the first of the month
' Else you have to go the xDay of next week
If (xDay - nDay) >= 0 Then
firstXDate = DateSerial(nYear, nMonth, 1 + (xDay - nDay) + (N-1) * 7)
Else
firstXDate = DateSerial(nYear, nMonth, 1 + (xDay - nDay) + (N * 7))
End If


--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com