© 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.
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")
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
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