• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

Calculate Business Days in VBA Without Table

December 8, 2015 by Ryan McCormick Leave a Comment

Recently, I was working on a side project that did some cool things with business days. In one of the use cases, I needed to figure out the date five business days from the current date.

My solution needed to be built for technical staff to easily modify exclusion dates. My solution also had to be something that could be set once and forgotten as the end-user pool is expected to be non-technical.

The main function that is used to calculate business days is getBusinessDate([startDate], [numBusinessDays]). Example: five business days from the date before Thanksgiving (also excluded from my example is the day after Thanksgiving as it is a floating holiday for most major US corporations): getBusinessDate(#11/25/2015#, 5) results in 12/04/2015. This function works in reverse as well: getBusinessDate(#12/04/2015#, -5) results in 11/25/2015.

While I understand most of our visitors are from all areas of the globe, the examples built in the code sample are based on non-working days at a typical corporation in the United States. However, these examples can be easily adapted to any region.

To modify the dynamic list of holiday/non-working days:

  • Ensure proper array length under the boolean function isHoliday(). The length of the array needs to be updated if any new holidays are added or if any of the currently existing holidays are removed.
  • Add new exclusion days according to the array index in the example.

Most organizations publish payroll calendars that can be cross referenced to determine logic for non working days. As a developer, it is important to think of the following scenarios when building your holiday schedule:

If a holiday lands on a weekend, does your organization consider the previous or next weekday a non-working day? Or, if a holiday lands on a Saturday, does your organization consider the previous Friday a non working day? Or, if a holiday lands on a Sunday, does your organization consider the following Monday a non working day? Here are some functions that can be used:

  1. prevWeekDay([date]): Returns the closest weekday that occurs before the date entered.
  2. nextWeekDay([date]): Returns the closest weekday that occurs following the date entered.
  3. satPrevSunNext([date]): Returns the closest Friday before if the date entered is a Saturday, or the closest Monday if the date entered was a Sunday.
  4. dateFromDesc([instance],[dayOfWeek],[Month],[year]): Instance is 1-4 or -1 for the last weekday instance. dayOfWeek (Sun = 1, Sat = 7). Month (1 – 12). Year (YYYY). Returns the date described for the current year. Like the example stated above, Thanksgiving in the United States is celebrated on the fourth Thursday in November: dateFromDesc(4,5,11,2015).

Code Example: Calculate Business Days in VBA for Access and Excel

Function getBusinessDate(startDate As Date, _
businessdays As Long) As Date
    Dim curDate As Date, calDays As Long, busDay As Long
    calDays = 0: busDays = 0
    curDate = CDate(IIf(businessdays > 0, _
    startDate + 1, startDate - 1))
    
    Do While busDays < Abs(businessdays)
        If isWeekend(curDate) Then
            calDays = calDays + 1
        ElseIf isHoliday(curDate) Then
            calDays = calDays + 1
        Else
            calDays = calDays + 1
            busDays = busDays + 1
        End If
        curDate = CDate(IIf(businessdays > 0, _
        curDate + 1, curDate - 1))
    Loop
    getBusinessDate = CDate(IIf(businessdays > 0, _
    startDate + calDays, startDate - calDays))
End Function

Function isHoliday(curDate As Date) As Boolean
    Dim holiday(1 To 8) As Date
    ' ////////////////////////////////////////////////////
    ' // BUSINESS HOLIDAY SCHEDULE
    ' ////////////////////////////////////////////////////
    
    ' New Years Day 01/01. Custom function 'nextWeekDay'
    ' Basis: Some companies offer the next business
    ' day after new years as a vacation day if
    ' new years falls on a Sunday. OR the previous business
    ' day if new years falls on a saturday.
    ' /////////////////////////////////////////////////
    holiday(1) = satPrevSunNext(CDate("01/01/" & year(curDate)))
    
    ' Martin Luther King JR Day.
    ' Rule: Third Monday in January
    ' /////////////////////////////////////////////////
    holiday(2) = dateFromDesc(3, 2, 1, year(curDate))
    
    ' Memorial Day
    ' Rule: Last Monday in May
    ' /////////////////////////////////////////////////
    holiday(3) = dateFromDesc(-1, 2, 5, year(curDate))
    
    ' Independence Day 07/04. Custom function 'julyFour'
    ' Basis: Some companies offer the business day before
    ' Independence day as a vacation day if July 4th falls
    ' on a weekend.
    holiday(4) = prevWeekDay(CDate("07/04/" & year(curDate)))
    
    ' Labor Day
    ' Rule: First Monday in September
    ' /////////////////////////////////////////////////
    holiday(5) = dateFromDesc(1, 2, 9, year(curDate))
    
    ' Thanksgiving.
    ' Rule: Fourth Thursday in November.
    ' /////////////////////////////////////////////////
    holiday(6) = dateFromDesc(4, 5, 11, year(curDate))
    
    ' Day after Thanksgiving.
    ' Basis: Some companies, offer the day after Thanksgiving
    ' as a floating holiday. Notice, the date target has been
    ' expressed as thanksgiving + 1 day. If written as the fourth
    ' Friday in November, results will be inaccurate.
    ' ///////////////////////////////////////////////////
    holiday(7) = dateFromDesc(4, 5, 11, year(curDate)) + 1
    
    ' Christmas Day 12/25. Custom function 'nextWeekDay'
    ' Basis: Some companies offer the business day after
    ' Christmas day as a vacation day if December 25th falls
    ' on a weekend.
    ' ///////////////////////////////////////////////////
    holiday(8) = nextWeekDay(CDate("12/25/" & year(curDate)))
    
   
    ' Check to see if current date is holiday according to above
    ' holiday schedule.
    ' ///////////////////////////////////////////////////
    For i = 1 To UBound(holiday)
        If curDate = holiday(i) Then
            isHoliday = True
            Exit Function
        End If
    Next i
    isHoliday = False
End Function

Function isWorkDay(iDate As Date) As Boolean
    If isWeekend(iDate) Then
        isWorkDay = False
    ElseIf isHoliday(iDate) Then
        isWorkDay = False
    Else
        isWorkDay = True
    End If
End Function

Function isWeekend(iDate As Date) As Boolean
    Select Case Weekday(iDate)
        Case 1:
            isWeekend = True
        Case 7:
            isWeekend = True
        Case Else
            isWeekend = False
    End Select
End Function

Function satPrevSunNext(iDate As Date) As Date
    Dim actualDay As Date: actualDay = iDate
    If Weekday(actualDay) = vbSaturday Then
        actualDay = actualDay - 1
        satPrevSunNext = actualDay
    ElseIf Weekday(actualDay) = vbSunday Then
        actualDay = actualDay + 1
        satPrevSunNext = actualDay
    Else
        satPrevSunNext = actualDay
    End If
End Function

Function prevWeekDay(iDate As Date) As Date
    Dim actualDay As Date: actualDay = iDate
    If isWeekend(actualDay) Then
        Do While isWeekend(actualDay)
            actualDay = actualDay - 1
        Loop
        prevWeekDay = actualDay
    Else
        prevWeekDay = actualDay
    End If
End Function

Function nextWeekDay(iDate As Date) As Date
    Dim actualDay As Date: actualDay = iDate
    If isWeekend(actualDay) Then
        Do While isWeekend(actualDay)
            actualDay = actualDay + 1
        Loop
        nextWeekDay = actualDay
    Else
        nextWeekDay = actualDay
    End If
End Function

Function dateFromDesc(instance, dayOfWeek, month, year) As Date
    Dim startDate As Date, endDate As Date, dayCount As Integer, _
    instanceCount As Integer, curDate As Date, i
    instanceCount = 0
    If instance > 0 Then
        startDate = CDate(month & "/01/" & year)
        endDate = DateSerial(year, month + 1, 0)
        curDate = startDate
        For i = 1 To (Abs(DateDiff("d", startDate, endDate)) + 1)
            If Weekday(curDate) = dayOfWeek Then
                instanceCount = instanceCount + 1
                If instanceCount = instance Then
                    dateFromDesc = curDate
                    Exit Function
                End If
            End If
            curDate = startDate + i
        Next i
    Else
        startDate = DateSerial(year, month + 1, 0)
        endDate = CDate(month & "/01/" & year)
        curDate = startDate
        For i = 1 To (Abs(DateDiff("d", startDate, endDate)) + 1)
            If Weekday(curDate) = dayOfWeek Then
                instanceCount = instanceCount + 1
                    If instanceCount = 1 Then
                        dateFromDesc = curDate
                        Exit Function
                    End If
            End If
            curDate = startDate - i
        Next i
    End If
End Function

As always, comment if you get stuck or have feedback.

Related

Filed Under: Microsoft Access, Microsoft Excel, Microsoft Outlook, Microsoft Word, VBA Tagged With: business days in vba, MS Access, ms excel, ms office

Reader Interactions

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