[Ad] need a break from coding?
© copyright 05.May.2009 by Paul Bradley filed under Visual Basic
[Ad] need a break from coding?
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 provides PHP & MySQL bespoke development services via his software development company, Carlisle Software Limited.
He has over 20 years programming experience.
