Finding the nth Particular Weekday in a Month
Perhaps your application needs to find the third Tuesday in November, 1997. The function presented here, dhNthWeekday, in Listing 2.7, solves this puzzle for you. The function accepts three parameters:
A date specifying the month and year to start in
An integer greater than 1 that specifies the offset into the month
An integer specifying the day of week to retrieve (Use the vbSunday…vbSaturday constants.)
and returns a date representing the nth specific weekday in the month. If you pass an invalid day of week value or an invalid offset, the function returns the date you passed it.
Listing 2.7: Find the nth Specific Weekday in a Month
Function dhNthWeekday(dtmDate As Date, intN As Integer, _
intDOW As Integer) As Date
' Find the date of the specified day within the month. For
' example, retrieve the 3rd Tuesday's date.
Dim dtmTemp As Date
If (intDOW < vbSunday Or intDOW > vbSaturday) _
Or (intN < 1) Then
' Invalid parameter values. Just
' return the passed-in date.
dhNthWeekday = dtmDate
Exit Function
End If
' Get the first of the month.
dtmTemp = DateSerial(Year(dtmDate), Month(dtmDate), 1)
' Get to the first intDOW in the month.
Do While WeekDay(dtmTemp) <> intDOW
dtmTemp = dtmTemp + 1
Loop
' Now you've found the first intDOW in the month.
' Just add 7 for each intN after that.
dhNthWeekday = dtmTemp + ((intN - 1) * 7)
End Function
The function is moderately simple. To do its work, it must:
Verify the parameters
Find the first day of the specified month
Move to the first specified weekday in the month
Add enough weeks to find the nth occurrence of the specified weekday
It's important to verify the parameters in this case because later, the code loops until it finds the correct day of the week. If the day of the week parameter is invalid, it's possible that the code will look forever. In addition, you must verify that the offset into the month is positive. If either value is invalid, the function returns the passed-in starting date. The code that handles the verification looks like this:
If (intDOW < vbSunday Or intDOW > vbSaturday) _
Or (intN < 1) Then
' Invalid parameter values. Just
' return the passed-in date.
dhNthWeekday = dtmDate
Exit Function
End If
Finding the first day of the specified month is, as you know by now, simple. It takes one line of code:
dtmTemp = DateSerial(Year(dtmDate), Month(dtmDate), 1)
Moving to the first specified weekday requires a bit more work. Although you could use the code shown in the section “Finding the Next or Previous Weekday” earlier in this chapter, that's more work than necessary, because you're already at the first day of the month. In this case, you can just “walk” forward until you hit the day you need:
Do While WeekDay(dtmTemp) <> intDOW
dtmTemp = dtmTemp + 1
Loop
Finally, to move to the nth occurrence of the weekday, you just need to add the correct multiple of 7 to the date:
dhNthWeekday = dtmTemp + ((intN - 1) * 7)
For example, to find the date of the third Tuesday in March, 1998, you could call the function like this:
dtm = dhNthWeekday(#3/98#, 3, vbTuesday)
The return value will be the date #3/17/98#, the third Tuesday in March, 1998.