I recently built a project where I had to count business days by excluding weekends and US Federal holidays. In my previous post:
Public Function dThanksgiving(curDate As Date) As Date
'Define variables
Dim curYear As Integer 'Current Year
Dim sDate As Date 'First Day of Month
Dim eDate As Date 'Last Day of Month
Dim ctDate As Date 'Count Date
Dim dCount As Integer 'Weekday hit counter
'Pull year value from input: curDate
curYear = Format(curDate, "yyyy")
'Set Start Date
sDate = CDate("11/01/" & curYear)
'Set End Date
eDate = CDate("11/30/" & curYear)
'Count Date starts at start date
ctDate = sDate
'Define Loop from 1 to the number of days
'difference between start and end date
For i = 1 To DateDiff("d", sDate, eDate)
If Weekday(ctDate) = vbThursday Then
'As weekdays are getting checked
'add 1 to the counter when hitting
'a thursday
dCount = dCount + 1
'If thursday counter equal 4, you
'have found the fourth thursday of
'the month of November
If dCount = 4 Then
'Return the date of the fourth
'thursday
dThanksgiving = ctDate
End If
End If
ctDate = ctDate + 1
Next i
End Function
The above example can be passed any date and will return the fourth thursday of the month for the year of the date passed in.
Thats all I have for now. Please comment with questions.
Raghu Prabhu says
I want to find out the first Thursday in November every year. How do I modify this please? Plus do you have a file. I don’t know how to use this function.
Thanks.
Ryan McCormick says
Did you try swapping vbThursday for vbTuesday AND “If dCount = 4” to “If dCount = 1”?