Public Function GetNthWeekdayOfMonth(DayIndex As Long, DayOfWeek As Long, DateOfMonth As Date) As Variant
'thedbguy@gmail.com
'2/4/2019
'Returns the nth weekday of the month
'Parameters: DayIndex = 1 to 5, DayOfWeek = 1 to 7 (Sun to Sat), DateOfMonth = any date within the month of interest
'Usage: To return the 4th Monday in February
'GetNthWeekdayOfMonth(4,2,#2/14/2019#)
'Returns: 2/25/2019
'Note: A day index of zero (0) or less will return the 1st date
'and an index of 5 or greater will return the "last" date with a matching weekday
Dim dteNthDate As Date
'set up
If DayIndex <= 4 Then
dteNthDate = DateSerial(Year(DateOfMonth), Month(DateOfMonth), 1)
Else
dteNthDate = DateSerial(Year(DateOfMonth), Month(DateOfMonth) + 1, 0)
End If
'get first ocurrence of date
Do While Weekday(dteNthDate) <> DayOfWeek
If DayIndex <= 4 Then
dteNthDate = DateAdd("d", 1, dteNthDate)
Else
dteNthDate = DateAdd("d", -1, dteNthDate)
End If
Loop
'get nth date
If DayIndex >= 1 And DayIndex <= 4 Then
dteNthDate = DateAdd("ww", DayIndex - 1, dteNthDate)
End If
GetNthWeekdayOfMonth = dteNthDate
End Function