• Home
 · 
• Math/Dates
 · 
• Date of First/Last weekday in month

Date of First/Last weekday in month

What date is Thanksgiving, or Labor Day? Get date of First or last weekday in month. i.e. Get first Monday of September, or last Thursday of November.

Inputs
Required day (Sunday-Saturday), month (1-12), year (any), first (0) or last (1).

Returns
Date

Rate Date of First/Last weekday in month

• 1
• 2
• 3
• 4
• 5

(5(5 Vote))

Public Function GetFirstLastDate(ByVal fnDay As String, fnMonth As Integer, fnYear As Integer, fnFirstLast As Byte) As Date
Dim tmpDate As Date, dLoop As Integer, addDate As Date, tmpLastDate As Date
addDate = DateSerial(fnYear, fnMonth, 1)
Select Case fnFirstLast
 Case 0
 If WeekdayName(Weekday(addDate)) = fnDay Then
  GetFirstLastDate = addDate
  Exit Function
 End If
 For dLoop = 1 To 7
  tmpDate = DateAdd("w", dLoop, addDate)
  If WeekdayName(Weekday(tmpDate)) = fnDay Then
   GetFirstLastDate = tmpDate
   Exit For
  End If
 Next dLoop
 Case 1
 tmpLastDate = DateAdd("d", -1, DateAdd("m", 1, addDate))
 If WeekdayName(Weekday(tmpLastDate)) = fnDay Then
  GetFirstLastDate = tmpLastDate
  Exit Function
 End If
 For dLoop = 7 To 1 Step -1
  tmpDate = DateAdd("w", -dLoop, tmpLastDate)
  If WeekdayName(Weekday(tmpDate)) = fnDay Then
   GetFirstLastDate = tmpDate
   Exit For
  End If
 Next dLoop
End Select
End Function
'Usage example:
Private Sub Command1_Click()
MsgBox GetFirstLastDate("Monday", 9, 2004, 0)
End Sub