This VBA function passes back the last date that a particular weekday occurred.

Code:

Public Function LastWeekDayDate(CurrentDate, LastWeekDay)
' This procedure calculates the date of the last occurrence of the day of the
' week represented by LastWeekDay.  For example, if today is Wednesday, with
' CurrentDate=12/17/08, and you would like last Friday's date (LastWeekDay=6),
' the result would be 12/12/08.  It returns a Null if there is a problem.

' LastWeekDay values:
' Sunday = 1
' Monday = 2
' Tuesday = 3
' Wednesday = 4
' Thursday = 5
' Friday = 6
' Saturday = 7

' LastWeekDayDate() Version 1.0.0
' Copyright © 2009 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.

On Error GoTo Err_LastWeekDayDate

    Dim dteTemp As Date

    ' If CurrentDate is not a date, then pass back a Null.
    If Not IsDate(CurrentDate) Then
        LastWeekDayDate = Null
        GoTo Exit_LastWeekDayDate
    End If

    ' Initialize the date were are starting with.
    dteTemp = CurrentDate

    ' Subtract a day until the week day = LastWeekDay.
    Do Until WeekDay(dteTemp) = LastWeekDay
        dteTemp = DateAdd("d", -1, dteTemp)
    Loop

    ' Pass back that value.
    LastWeekDayDate = dteTemp

Exit_LastWeekDayDate:
    On Error Resume Next
    Exit Function

Err_LastWeekDayDate:
    MsgBox Err.Number & " " & Err.DESCRIPTION, vbCritical, "LastWeekDayDate()"
    LastWeekDayDate = Null
    Resume Exit_LastWeekDayDate

End Function

Download Code:

basLastWeekDay.zip