Calculating the number of business days between two dates, excluding weekends and UK bank holidays. (VB 2005)

© copyright 05.May.2009 by Paul Bradley filed under Visual Basic


I had to develop a report today which showed if a breach of contract had occurred on thousands of database records. The calculation for identifying a breach, was 21 business days excluding all weekends and public holidays, from a given start date to closing a date event.

Below is the Visual Basic function I came up with. The code sets up an array of bank holiday dates for the next three years as published by the government.

After calculating the actual number of days between the two supplied dates, the function then checks each day between the start and end dates, and decrements the business days count by one if the day of week is a Saturday or Sunday; or is a member of the Bank Holidays array.

Business Days Usage

Below is an example of how to use the function, checking for the number of business days between the 1st of May 2009 and the 13th of May 2009. The function will return 8 days, as there are two weekends and one bank holiday Monday.

Dim Days As Integer
Dim StartDate As Date
Dim EndDate   As Date

StartDate = #5/1/2009#
EndDate   = #5/13/2009#

Days = BusinessDaysExcludeWeekendsAndHolidays(StartDate, EndDate)

MsgBox("There are " & Days & " business days between " & StartDate _
    & " and " & EndDate & " excluding weekends and UK bank holidays", _
    MsgBoxStyle.Information, "Business Days")

 

The Business Days Function

Private Function BusinessDaysExcludeWeekendsAndHolidays _
    (ByVal StartDate As Date, ByVal StopDate As Date) As Integer

Dim BankHolidays(23) As Date
'2009
BankHolidays(0) = #1/1/2009#    'New Year's Day
BankHolidays(1) = #4/10/2009#   'Good Friday
BankHolidays(2) = #4/13/2009#   'Easter Monday
BankHolidays(3) = #5/4/2009#    'May Bank Holiday
BankHolidays(4) = #5/25/2009#   'Spring Bank Holiday
BankHolidays(5) = #8/31/2009#   'Summer Bank Holiday
BankHolidays(6) = #12/25/2009#  'Christmas Day
BankHolidays(7) = #12/28/2009#  'Boxing Day * sub day
'2010
BankHolidays(8) = #1/1/2010#    'New Year's Day
BankHolidays(9) = #4/2/2010#    'Good Friday
BankHolidays(10) = #4/5/2010#   'Easter Monday
BankHolidays(11) = #5/3/2010#   'May Bank Holiday
BankHolidays(12) = #5/31/2010#  'Spring Bank Holiday
BankHolidays(13) = #8/30/2010#  'Summer Bank Holiday
BankHolidays(14) = #12/27/2010# 'Christmas Day * sub day
BankHolidays(15) = #12/28/2010# 'Boxing Day * sub day
'2011
BankHolidays(16) = #1/3/2011#   'New Year's Day * sub day
BankHolidays(17) = #4/22/2011#  'Good Friday
BankHolidays(18) = #4/25/2011#  'Easter Monday
BankHolidays(19) = #5/2/2011#   'May Bank Holiday
BankHolidays(20) = #5/30/2011#  'Spring Bank Holiday
BankHolidays(21) = #8/29/2011#  'Summer Bank Holiday
BankHolidays(22) = #12/26/2011# 'Christmas Day * sub day
BankHolidays(23) = #12/27/2011# 'Boxing Day * sub day

Dim ActualDays   As Integer
Dim BusinessDays As Integer
Dim NextDate As Date
Dim x As Double

If StopDate <= StartDate Then Return -1

ActualDays = DateDiff(DateInterval.Day, StartDate, StopDate, _
    FirstDayOfWeek.Monday, FirstWeekOfYear.Jan1) + 1
BusinessDays = ActualDays

For x = 1 To ActualDays - 1
    ' loop through all dates between the start and 
    ' end date and check if either a weekend day or
    ' a bank holiday - and take one off the total.
    NextDate = DateAdd(DateInterval.Day, x, StartDate)

    If NextDate.DayOfWeek = DayOfWeek.Saturday Or _
        NextDate.DayOfWeek = DayOfWeek.Sunday Then
        BusinessDays = BusinessDays - 1
    End If

    If Array.IndexOf(BankHolidays, NextDate) > 0 Then
        BusinessDays = BusinessDays - 1
    End If
Next

Return BusinessDays

End Function

 

 


If you have found this article helpful or useful please consider linking to it, emailing it to friends, or share it with others using social sites like del.icio.us, Stumble Upon or Twitter.

Paul Bradley

About the Author
Paul Bradley is a VB.NET software developer living and working in Cumbria. He has over 20 years programming experience. He also produces e-learning videos at Linux by Example

Other Popular Articles

Categories & Topics

Home · Apache · JavaScript · Perl · PDF · PHP · MySQL · MSSQL · TAR · Ubuntu Linux · Video · Visual Basic

Browse the complete article history, and if you like what you see; consider subscribing to the rss feed.