How Many Mondays in June?

If your application needs to know how many occurrences there are of a particular weekday in a given month, the dhCountDOWInMonth function is for you. This function, shown in Listing 2.15, allows you to specify a date and, optionally, a specific day of the week. It returns the number of times the specified day of the week occurs in the month containing the date. If you don’t pass a day of the week value, the function counts the number of times the day indicated by the date parameter occurs within its own month.

Listing 2.15: Count the Number of Specific Weekdays in a Month

Function dhCountDOWInMonth(ByVal dtmDate As Date, _
 Optional intDOW As Integer = 0)
    ' Calculate the number of specified days in
    ' the specified month.
    Dim dtmFirst As Date
    Dim intCount As Integer
    Dim intMonth As Integer
    If (intDOW < vbSunday Or intDOW > vbSaturday) Then
        ' Caller must not have specified DOW, or it
        ' was an invalid number.
        intDOW = WeekDay(dtmDate)
    End If
    intMonth = Month(dtmDate)
    ' Find the first day of the month
    dtmFirst = DateSerial(Year(dtmDate), intMonth, 1)
    ' Move dtmFirst forward until it hits the
    ' matching day number.
    Do While WeekDay(dtmFirst) <> intDOW
        dtmFirst = dtmFirst + 1
    Loop
    ' Now, dtmFirst is sitting on the first day
    ' of the requested number in the month. Just count
    ' how many of that day type there are in the month.
    intCount = 0
    Do While Month(dtmFirst) = intMonth
        intCount = intCount + 1
        dtmFirst = dtmFirst + 7
    Loop
    dhCountDOWInMonth = intCount
End Function

The dhCountDOWInMonth function takes four simple steps to do its work. It must

Verify the parameters
Find the first day of the specified month
Move forward within the month until it hits the first day matching the day of week you’re interested in
Count the days, adding increments of seven days, until the day is no longer in the same month

To verify the parameters, the code checks the intDOW parameter, making sure the value is between vbSunday and vbSaturday. If not, it overrides the value and uses the day of the week represented by the dtmDate parameter:

If (intDOW < vbSunday Or intDOW > vbSaturday) Then
    ' Caller must not have specified DOW, or it
    ' was an invalid number.
    intDOW = WeekDay(dtmDate)
End If

Finding the first day of the month requires yet another call to the DateSerial function:

intMonth = Month(dtmDate)
dtmFirst = DateSerial(Year(dtmDate), intMonth, 1)

Moving through the days until the code finds a day matching the required day of the week takes just a few lines:

' Move dtmFirst forward until it hits the
' matching day number.
Do While WeekDay(dtmFirst) <> intDOW
    dtmFirst = dtmFirst + 1
Loop

Finally, counting the number of matching days requires looping, adding seven days at a time, until the date falls out of the specified month:

' Now, dtmFirst is sitting on the first day
' of the requested number in the month. Just count
' how many of that day type there are in the month.
intCount = 0
Do While Month(dtmFirst) = intMonth
    intCount = intCount + 1
    dtmFirst = dtmFirst + 7
Loop

To test this function, you might write code like this:

If dhCountDOWInMonth(#3/98#, vbFriday) > 4 Then
    MsgBox "There are more than 4 Fridays in March 1998!"
End If