• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

Calculate Date in Business Days in VBA Without Database Tables

March 25, 2014 by Ryan McCormick 2 Comments

In a recent project, I had to build a program to calculate an end date in either business days or calendar days based on certain criteria.

This solution had to work in both Excel and Access and needed to run without the use of database tables.

Business Day Calculation Requirements

The criteria I used to determine the definition of a business day was any non holiday weekday. For my holiday definitions, I used an array of standard, US government recognized holidays.

NOTE: For locations outside the US, please pay attention to and modify the holiday Array definitions and Array length.

VBA Business Day Calculation Code

For simplicity sake, I divided my code into three functions:

  1. businessDays(Start Date, Number of Business Days From Start). This is the main function to call to find the end result date from start date plus business days. Dependencies: isExclude, isWeekend
  2. isExclude(testDate). This function checks to see if the date entered is a holiday or excluded date based on the array. Returns Boolean (True or False)
  3. isWeekend(testDate). This function checks to see if the date entered is a weekend(Saturday or Sunday). Returns Boolean (True or False)
Function businessDays(stDay As Date, eDay As Integer) As Date
Do While i < eDay
    If isWeekend(stDay) = True Then
        i = i + 0
    ElseIf isExclude(stDay) = True Then
        i = i + 0
    Else
        i = i + 1
    End If
    stDay = stDay + 1
Loop

businessDays = stDay

End Function

Function isExclude(testDate As Date) As Boolean
Dim excludeDates(1 To 10) As Date

'Holiday List
'''''''''''''''''''''''''''''''''''''''''''''
excludeDates(1) = #1/1/2014# 'New Years Day
excludeDates(2) = #1/20/2014# 'MLK Jr. Day
excludeDates(3) = #2/17/2014# 'Presidents' Day
excludeDates(4) = #5/26/2014# 'Memorial Day
excludeDates(5) = #7/4/2014# 'Independence Day
excludeDates(6) = #9/1/2014# 'Labor Day
excludeDates(7) = #10/13/2014# 'Columbus Day
excludeDates(8) = #11/11/2014# 'Veterans Day
excludeDates(9) = #11/27/2014# 'Thanksgiving
excludeDates(10) = #12/25/2014# 'Christmas
'Missing Government Date - Add Inauguration Day:
'First January 20 following a Presidential election

For i = 1 To 10
    If testDate = excludeDates(i) Then
        isExclude = True
        Exit Function
    End If
Next i
    isExclude = False

End Function

Function isWeekend(testDate As Date) As Boolean
    Select Case Weekday(testDate)
        Case vbSaturday, vbSunday
            isWeekend = True
        Case Else
            isWeekend = False
    End Select
End Function

Test Code

To test the above code:

Function whatDay()
    Dim getDate As Date, getBusDays As Integer
    getDate = InputBox("Start Date")
    getBusDays = InputBox("Business Days")
       
    MsgBox businessDays(getDate, getBusDays)
End Function

Special Note - Holidays

This example was designed using an array of fixed dates for 2014 Holidays. Please make sure to update holiday dates for future years.

As always, please comment with improvements, suggestions,

Related

Filed Under: VBA Tagged With: Calculate Business Days, vba

Reader Interactions

Comments

  1. Zoltán Veres says

    August 10, 2016 at 9:03 am

    Your code has bugs!
    Test with 08/12/2016 friday with 1 businessday.
    Result is 08/13/2016 saturday instead of 08/15/2016 monday…

    Reply
  2. Warren Cohn says

    October 3, 2016 at 12:18 pm

    Thank you for creating this!

    I had an issue where if the return date was a weekend, it would still return that date. I also changed the code to auto calculate the holiday.

    Function businessDays(stDay As Date, eDay As Integer) As Date
    Dim i As Integer
    Do While i < eDay
    If isWeekend(stDay) = True Then
    i = i + 0
    ElseIf isExclude(stDay) = True Then
    i = i + 0
    Else
    i = i + 1
    End If
    stDay = stDay + 1
    Loop

    If isWeekend(stDay) = True Or isExclude(stDay) = True Then
    businessDays = businessDays(stDay, 1)
    Else
    businessDays = stDay
    End If
    End Function

    Function isExclude(testDate As Date) As Boolean
    Dim excludeDates(1 To 11) As Date
    Dim intyear As Integer
    intyear = Format(testDate, "YYYY")
    Dim i As Integer
    'Holiday List
    '''''''''''''''''''''''''''''''''''''''''''''
    excludeDates(1) = CDate("1/1/" & intyear) 'New Years Day
    excludeDates(2) = CDate("1/20/" & intyear) '#1/20/2014# 'MLK Jr. Day
    excludeDates(3) = CDate("2/17/" & intyear) '#2/17/2014# 'Presidents' Day
    excludeDates(4) = CDate("5/26/" & intyear) '#5/26/2014# 'Memorial Day
    excludeDates(5) = CDate("7/4/" & intyear) '#7/4/2014# 'Independence Day
    excludeDates(6) = CDate("9/16/" & intyear) '#9/1/2014# 'Labor Day
    excludeDates(7) = CDate("10/13/" & intyear) '#10/13/2014# 'Columbus Day
    excludeDates(8) = CDate("11/1/" & intyear) '#11/11/2014# 'Veterans Day
    excludeDates(9) = CDate("11/27/" & intyear) '#11/27/2014# 'Thanksgiving
    excludeDates(10) = CDate("12/25/" & intyear) '#12/25/2014# 'Christmas

    'Missing Government Date – Add Inauguration Day:
    'First January 20 following a Presidential election

    For i = 1 To 10
    If testDate = excludeDates(i) Then
    isExclude = True
    Exit Function
    End If
    Next i
    isExclude = False

    End Function

    Function isWeekend(testDate As Date) As Boolean
    Select Case Weekday(testDate)
    Case vbSaturday, vbSunday
    isWeekend = True
    Case Else
    isWeekend = False
    End Select
    End Function

    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