This VBA function passes back the last date that a particular weekday occurred.
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