Finding the Next or Previous Weekday

In many financial calculations, you’ll need to know the next specific weekday after a given date. For example, you might need to know the date of the Friday immediately following April 29, 1997, or the Monday immediately preceding the same date. As when finding the first or last day in a week, calculating these dates counts on the fact that you can subtract an integer from a date value and end up with another date value.

In this case, it seems simplest to just calculate the beginning of the week containing the specified date and then add on enough days to get to the requested date. That code, from the procedures in Listing 2.5, looks like this:

intTemp = WeekDay(dtmDate)
dhPreviousDOW = dtmDate - intTemp + intDOW

Say you’re looking for the Thursday before 10/7/97 (a Tuesday). In this case, intTemp will be 3 (Tuesday’s day of the week) and intDOW will contain 5 (Thursday’s day of the week). The expression

dtmDate - intTemp + intDOW
' the same as:
' #10/7/97# - 3 + 5

will return the date 10/9/97. This, clearly, is not the Thursday before 10/7/97, but the Thursday after. The final step of the calculation, then, is to subtract one week, if necessary. The entire expression looks like this:

dhPreviousDOW = dtmDate - intTemp + intDOW - _
 IIf(intTemp > intDOW, 0, 7)

When would you not need to subtract 7 to move to the previous week? Reverse the dates in the example. If you’re looking for the Tuesday before 10/9/97, the expression would be

dtmDate - intTemp + intDOW
' the same as:
' #10/9/97# - 5 + 3

which returns #10/7/97#, the correct answer. There’s no need to subtract 7 to move to the previous week. The same logic applies to calculating the following weekday, but reversed. In this case, you may need to add 7 to move to the next week if the day you were looking for has already occurred in the current week.

Listing 2.5: Find the Previous or Next Specific Weekday

Function dhPreviousDOW(intDOW As Integer, _
 Optional dtmDate As Date = 0) As Date
    ' Find the previous specified day of the week
    ' before the specified date.
    Dim intTemp As Integer
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    intTemp = WeekDay(dtmDate)
    dhPreviousDOW = dtmDate - intTemp + intDOW - _
     IIf(intTemp > intDOW, 0, 7)
End Function
Function dhNextDOW(intDOW As Integer, _
 Optional dtmDate As Date = 0) As Date
    ' Find the next specified day of the week
    ' after the specified date.
    Dim intTemp As Integer
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    intTemp = WeekDay(dtmDate)
    dhNextDOW = dtmDate - intTemp + intDOW + _
     IIf(intTemp < intDOW, 0, 7)
End Function

The following examples demonstrate calling the two functions:

Debug.Print "The Monday before 12/25/97 is " & _
 dhPreviousDOW(vbMonday, #12/25/97#)
Debug.Print "The Friday after 12/25/97 is " & _
 dhNextDOW(vbFriday, #12/25/97#)
Debug.Print "It's " & Date & _
 ". The next Monday is " & dhNextDOW(vbMonday)