Introduction
This project is aimed at easing the troubles of figuring out valid business dates. It includes a few functions I have created to determine whether or not the date is a holiday or a weekend, and also to retrieve either the next or last business day. There is also a function to find the last day of a given month. I've included PseudoCode which should help to translate these functions to other languages.
Background
Often in business, you are prompted with interesting scenarios that just sorta sneak up on you. In my experience, Business Dates have given me the most trouble, and it just keeps coming. Just when you think you have the code perfect, a holiday falls on a weekend that just happened to be leap year too, while the sun moved just to the left of the planet mars my code explodes and 1200 bank transactions think they finished, but the bank has no idea what we are talking about because they were closed, Duh. :)
So, our goal here is to write the PERFECT set of routines to handle the potential issues of the calendar. And thanks to our beautiful planets rotation, we have A LOT of interesting issues we have to account for, such as leap year and the fact that there is no true pattern to how many days there are in a month. Luckily, it's the same every year, barring February, or we'd REALLY be in trouble. So, with that said, this is my attempt to create the perfect business dates routines to handle the majority of issues I've encountered.
When dealing with any business date function, it is important to note that there are two main types of dates that affect the way we handle the data.
Floating Holidays (Christmas, New Years, Veteran’s Day, etc.)
Floating Holidays are holidays that always occur at the same date, but the day of the week is always different. For example, Christmas is always the 25th of December but can fall on any day of the week. Therefore, it is a floating holiday.
Fixed Holidays (Memorial Day, Labor Day, Thanksgiving, etc.)
Fixed Holidays are holidays that are on different dates every year, but are always on the same day of the week. For example, Martin Luther King Jr. Day is always the 3rd Monday of the January.
Note - Please note that these functions are not intended to fix all of your problems as is. They will need some tweaking on the outside to conform to your location's holidays and standards. For example, my shop runs with a two business day transaction delay, so we pad all of our business days by 2 days, so it is important to note what your standards are.
Depending on how your company works, you may use different holidays than other people. For example, some banks have holidays that some states don’t honor, where states may have holidays the banks don’t honor. For my example, we are going to assume the world shuts down on both the state and bank holidays local to where I am (Washington State). You'll need to adjust accordingly.
Using the Code
LastDayOfMonth: Pass the Month and Year of the date you need to get the last day from. We break this into two parameters to help with some other issues we encounter when we use recurring payments, which I'll explain later.
Function LastDayOfMonth(sMonth, sYear)
dim tmpMonth, tmpYear
tmpMonth = sMonth
tmpYear = sYear
LastDayOfMonth = dateadd("d", -1, Dateadd("m", 1, tmpMonth & "/1/" & tmpYear))
End Function
IsHoliday: Pass the date you want to verify. A 1 will be returned if it is a holiday, a 0 if it is not. You then use DateAdd and add the returned value to the current date (that you passed it). As for holidays in this function, make sure that if your holiday is a floating holiday that you include the day before and day after in case the main holiday falls on a weekend. See 12/24,5,6 as an example.
Function IsHoliday(sDate)
Dim iDay, iTmpDay, i
IsHoliday = 0
iDay = Day(sDate)
If IsDate(sDate) Then
Select Case Month(sDate)
Case 1
If iDay = 1 Then
IsHoliday = 1
Else
If iDay = 2 Then
if Weekday(DateAdd("d", -1, sDate)) = 1 then
IsHoliday = 1
end if
Else
For i = 0 To 30
If Weekday(DateAdd("d", i, CDate("1/1/" & Year(sDate)))) _
= 2 Then
If CDate(sDate) = CDate(DateAdd("d", i + 14, _
CDate("1/1/" & Year(sDate)))) Then
IsHoliday = 1
End If
Exit For
End If
Next
End If
End If
Case 2
For i = 0 To 27
If Weekday(DateAdd("d", i, CDate("2/1/" & Year(sDate)))) = 2 _
Then
If CDate(sDate) = CDate(DateAdd("d", i + 14, _
CDate("2/1/" & Year(sDate)))) Then
IsHoliday = 1
End If
Exit For
End If
Next
Case 3
Case 4
Case 5
For i = 1 To 7
If Weekday(DateAdd("d", "-" & i, _
CDate("5/31/" & Year(sDate)))) = 2 Then
If CDate(sDate) = CDate(DateAdd("d", "-" & i, _
CDate("5/31/" & Year(sDate)))) Then
IsHoliday = 1
End If
Exit For
End If
Next
Case 6
Case 7
If iDay = 4 Then
IsHoliday = 1
Else
If iDay = 3 Then
if Weekday(DateAdd("d", 1, sDate)) = 7 then
IsHoliday = 1
end if
Else
If iDay = 5 Then
if Weekday(DateAdd("d", -1, sDate)) = 1 then
IsHoliday = 1
end if
End If
End If
End If
Case 8
Case 9
For i = 0 To 13
If Weekday(DateAdd("d", i, CDate("9/1/" & _
Year(sDate)))) = 2 Then
If CDate(sDate) = CDate(DateAdd("d", i, _
CDate("9/1/" & Year(sDate)))) Then
IsHoliday = 1
End If
Exit For
End If
Next
Case 10
For i = 0 To 13
If Weekday(DateAdd("d", i, CDate("10/1/" & _
Year(sDate)))) = 2 Then
If CDate(sDate) = CDate(DateAdd("d", i + 7, CDate("10/1/" & _
Year(sDate)))) Then
IsHoliday = 1
End If
Exit For
End If
Next
Case 11
If iDay = 11 Then
IsHoliday = 1
Else
If iDay = 10 Then
if Weekday(DateAdd("d", 1, sDate)) = 7 then
IsHoliday = 1
end if
Else
If iDay = 12 Then
if Weekday(DateAdd("d", -1, sDate)) = 1 then
IsHoliday = 1
end if
Else
For i = 0 To 28
If Weekday(DateAdd("d", i, CDate("11/1/" & _
Year(sDate)))) = 5 Then
if datediff("d", sDate, DateAdd("d", i + 21, _
CDate("11/1/" & Year(sDate)))) = 0 then
IsHoliday = 1
Exit For
End If
End if
If Weekday(DateAdd("d", i, CDate("11/1/" & _
Year(sDate)))) = 6 Then
if datediff("d", sDate, DateAdd("d", i + 21,_
CDate("11/1/" & Year(sDate)))) = 0 then
IsHoliday = 1
Exit For
End If
End if
Next
End If
End If
End If
Case 12
If iDay = 25 Then
IsHoliday = 1
Else
If iDay = 24 Then
if Weekday(DateAdd("d", 1, sDate)) = 7 then
IsHoliday = 1
end if
Else
If iDay = 26 Then
if Weekday(DateAdd("d", -1, sDate)) = 1 then
IsHoliday = 1
end if
Else
If iDay = 31 Then
if Weekday(DateAdd("d", 1, sDate)) = 7 then
IsHoliday = 1
End if
End if
End if
End if
End If
Case Else
End Select
End If
End Function
LastBusinessDay: This function will return the last business day based on the date passed (i.e., non-holiday, non-weekend).
Function LastBusinessDay(sDate)
Dim iDay, iDaysToAdd, iDate
iDaysToAdd = 0
iDate = sDate
x = 1
Do while iDaysToAdd >= 0
If Weekday(iDate) = 1 or Weekday(iDate) = 7 or _
isHoliday(iDate) <> 0 then
iDay = Weekday(iDate)
Select Case cint(iDay)
Case 1
iDate = DateAdd("d", -1, iDate)
Case 7
iDate = DateAdd("d", -1, iDate)
Case else
if isHoliday(iDate) > 0 then
iDate = dateadd("d", -(isHoliday(iDate)), iDate)
else
iDaysToAdd = iDaysToAdd - 1
end if
End Select
end if
Loop
LastBusinessDay = iDate
End Function
AddBusinessDay: Adds the passed number of business days to the date passed. If 0 is passed, this routine will verify that the passed day is a business day and if it's not, it will return the next business day.
Function AddBusinessDay(sDate, sAdd)
Dim iDay, iDaysToAdd, iDate
iDaysToAdd = sAdd
iDate = sDate
x = 1
Do while iDaysToAdd >= 0
If Weekday(iDate) = 1 or Weekday(iDate) = 7 or _
isHoliday(iDate) <> 0 then
iDay = Weekday(iDate)
Select Case cint(iDay)
Case 1
iDate = dateadd("d", 1, iDate)
Case 7
iDate = DateAdd("d", 1, iDate)
Case else
if isHoliday(iDate) > 0 then
iDate = dateadd("d", isHoliday(iDate), iDate)
else
iDaysToAdd = iDaysToAdd - 1
end if
End Select
else
if iDaysToAdd > 0 then
iDate = DateAdd("d", 1, iDate)
end if
iDaysToAdd = iDaysToAdd - 1
end if
if x > 100 then
response.End()
iDaysToAdd = -1
else
x = x + 1
end if
Loop
AddBusinessDay = iDate
end function
Points of Interest
An example of use:
In a payment system, you could use these functions to determine valid days to make a payment on. If a one time payment is submitted for 01/01/2005, we need to verify:
- Is the date a holiday, or a weekend
- Is the date bordering a weekend holiday?
Until none of these are true, we cannot move on. A common mistake is to check for a weekend then check for a holiday (or vice versa), but the problem with that is if you have a Friday holiday, after you bump up by 1 day, you’ll never check the new date to verify it’s a weekend again. So that is why we nest this in a loop until you get a valid date returned.
When we call the date routine we have to pass the date and how many days we want to pad the transaction. In most cases the transaction is not instant so you have to pass how many days until the transaction will truly take place, and this difference has to be business days, which shows why we need these routines. If you don’t want to pad it, just pass a 0 and it will return the next valid business day.
Pseudo Example
AddBusinessDay("01/01/2005", 0)
Date = "01/01/2005", DaysToAdd = 0
Loop while DaysToAdd parameter is equal to, or greater than, 0.
First time through
"01/01/2005" is a Weekend or Holiday then
"01/01/2005" is Saturday
Set Date = "01/02/2005"
Second time through
"01/02/2005" is a weekend or Holiday then
"01/02/2005" is Sunday
Set Date = "01/03/2005"
Third time through
"01/03/2005" is not a weekend or Holiday
There are no Days to Add
Subtract 1 from the DaysToAdd total
DateReturned = "01/03/2005", DaysToAdd = -1
Return New Date
End AddBusinessDay Function
One last note: If the transaction is issued after your cutoff time, add 1 extra day to the date. For example, if your transactions take place at 5pm, then any new payment after that time will truly be sent tomorrow. So we must add 1 extra day.
That's It!
Please let me know if I forgot any scenario or am missing any data. As I said before, date issues can be tough and I'd love to make these functions as strong as possible. So if you have an ideas to make them better, by all means, let me know.
License
This article has no explicit license attached to it, but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below.
A list of licenses authors might use can be found here.
