by Shauli (3 Submissions)
Category: Math/Dates
Compatability: VB Script
Difficulty: Advanced
Date Added: Wed 3rd February 2021
Rating: (4 Votes)
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).
Code Returns
Date
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