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.
Ryan McCormick says
Did you try swapping vbThursday for vbTuesday AND “If dCount = 4” to “If dCount = 1”?