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 ALOT 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
Last Day of Month: 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 (ie; 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:
- A. Is the date a holiday, or a weekend
- B. Is the date bordering a weekend holiday?
Until none of these are true, we can not 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 FunctionOne 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.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin