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:
prevWeekDay([date]): Returns the closest weekday that occurs before the date entered.nextWeekDay([date]): Returns the closest weekday that occurs following the date entered.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.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.
Leave a Reply