• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

How to Find Fourth Thursday of November in VBA

June 18, 2014 by Ryan McCormick 2 Comments

I recently built a project where I had to count business days by excluding weekends and US Federal holidays. In my previous post: , my example used hard-coded dates. This new example can be adapted and re-coded to find the date of any specific weekday or holiday of the year without the need to hard-code.

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.

Related

Filed Under: Microsoft Access, VBA, VBScript Tagged With: access, Excel, Find Day of Week, VB

Reader Interactions

Comments

  1. Raghu Prabhu says

    February 22, 2018 at 11:52 pm

    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.

    Reply
    • Ryan McCormick says

      March 3, 2018 at 3:50 pm

      Did you try swapping vbThursday for vbTuesday AND “If dCount = 4” to “If dCount = 1”?

      Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in