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.
Original Author: Shauli
Inputs
Required day (Sunday-Saturday), month (1-12), year (any), first (0) or last (1).
Returns
Date
Code
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
Loading Comments ...
Comments
No comments have been added for this post.
You must be logged in to make a comment.