12,545,968 members (59,433 online)
alternative version

Stats

94.9K views
29 bookmarked
Posted

, 9 Dec 2003
 Rate this:
This project is aimed at easing the troubles of figuring out valid business days. 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.

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)
'Returns the last day of the month/year passed in "mm/dd/yyyy" format
dim tmpMonth, tmpYear
tmpMonth = sMonth
tmpYear = sYear

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)
'Checks to see if passed date is a holiday
Dim iDay, iTmpDay, i

IsHoliday = 0
iDay = Day(sDate)

'Check if valid date first
If IsDate(sDate) Then
Select Case Month(sDate)

Case 1  'Jan
If iDay = 1 Then  'New Years
IsHoliday = 1
Else
If iDay = 2 Then  'Make sure new years doesn't fall on sunday.
'If so, today is a holiday.
if Weekday(DateAdd("d", -1, sDate)) = 1 then
IsHoliday = 1
end if
Else
For i = 0 To 30     'Martin Luther King B-Day
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  'PG 1/28
End If
Next
End If
End If

Case 2  'Feb
For i = 0 To 27     'President's Day
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  'Mar
Case 4  'Apr

Case 5  'May
For i = 1 To 7  'Memorial Day
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  'Jun

Case 7  'Jul
If iDay = 4 Then  'Independence Day
IsHoliday = 1
Else
If iDay = 3 Then  'Make sure Independence Day doesn't
'fall on saturday. If so, Friday is a holiday.
if Weekday(DateAdd("d", 1, sDate)) = 7 then
IsHoliday = 1
end if
Else
If iDay = 5 Then  'Make sure Independence
'Day doesn't fall on sunday. If so, Monday is a holiday.
if Weekday(DateAdd("d", -1, sDate)) = 1 then
IsHoliday = 1
end if
End If
End If
End If

Case 8 'Aug

Case 9 'Sep
For i = 0 To 13  'Labor Day
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 'Oct
For i = 0 To 13  'Columbus Day
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 'Nov
If iDay = 11 Then  'Veteran's Day
IsHoliday = 1
Else
If iDay = 10 Then  'Make sure Veterans Day doesn't fall
'on saturday. If so, Friday is a holiday.
if Weekday(DateAdd("d", 1, sDate)) = 7 then
IsHoliday = 1
end if
Else
If iDay = 12 Then  'Make sure Veterans Day doesn't
'fall on sunday. If so, Monday is a holiday.
if Weekday(DateAdd("d", -1, sDate)) = 1 then
IsHoliday = 1
end if
Else
For i = 0 To 28     'Thanksgiving & the Day After
If Weekday(DateAdd("d", i, CDate("11/1/" & _
Year(sDate)))) = 5 Then 'this is the first
'thursday of the month
if datediff("d", sDate,  DateAdd("d", i + 21, _
CDate("11/1/" & Year(sDate)))) = 0 then 'add 3
'weeks to the first to get the 4th (thanksgiving)
IsHoliday = 1
Exit For
End If
End if
If Weekday(DateAdd("d", i, CDate("11/1/" & _
Year(sDate)))) = 6 Then 'this is the day
'after thanksgiving
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 'Dec
If iDay = 25 Then  'Christmas
IsHoliday = 1
Else
If iDay = 24 Then  'Make sure Christmas Day doesn't
'fall on saturday. If so, Friday is a holiday.
if Weekday(DateAdd("d", 1, sDate)) = 7 then
IsHoliday = 1
end if
Else
If iDay = 26 Then  'Make sure Christmas
'Day doesn't fall on sunday. If so, Monday is a holiday.
if Weekday(DateAdd("d", -1, sDate)) = 1 then
IsHoliday = 1
end if
Else
If iDay = 31 Then  'Make sure new years
'doesn't fall on saturday. If so, today is a holiday.
if Weekday(DateAdd("d", 1, sDate)) = 7 then
IsHoliday = 1
End if
End if
End if
End if
End If

Case Else
'Do nothing but return false

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)

iDate = sDate

x = 1

If Weekday(iDate) = 1 or Weekday(iDate) = 7 or _
isHoliday(iDate) <> 0 then
iDay = Weekday(iDate)
Select Case cint(iDay)
Case 1  'Sunday
Case 7  'Saturday
Case else    'this is a valid day
if isHoliday(iDate) > 0 then
else
end if
End Select
end if
Loop

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)

iDate = sDate

x = 1
If Weekday(iDate) = 1 or Weekday(iDate) = 7 or _
isHoliday(iDate) <> 0 then
iDay = Weekday(iDate)
Select Case cint(iDay)
Case 1  'Sunday
Case 7    'Saturday
Case else    'this is a valid day
if isHoliday(iDate) > 0 then
else
end if
End Select
else
end if
end if

'Error trap in case of infinite loop, good for testing,
'shouldn't be necessary, but nice just in case
if x > 100 then
response.End()
else
x = x + 1
end if
Loop

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
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
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.

A list of licenses authors might use can be found here

Share

 Systems Engineer United States
No Biography provided

You may also be interested in...

 Pro Pro

 First Prev Next
 Do you have it in C#? MEkm16-Jan-14 8:25 MEkm 16-Jan-14 8:25
 Has anyone converted this to a usable dot net language Brian G012823-Oct-06 5:06 Brian G0128 23-Oct-06 5:06
 Re: Has anyone converted this to a usable dot net language sempko10-Jun-09 8:39 sempko 10-Jun-09 8:39
 Alternative Method the master_17-Oct-05 5:22 the master_ 17-Oct-05 5:22
 Bug in the date code... lwoodsinc28-May-04 11:03 lwoodsinc 28-May-04 11:03
 Re: Bug in the date code... glitch177k1-Jun-04 4:01 glitch177k 1-Jun-04 4:01
 Re: Bug in the date code... glitch177k1-Jun-04 8:27 glitch177k 1-Jun-04 8:27
 Re: Bug in the date code... Anonymous1-Jun-04 9:28 Anonymous 1-Jun-04 9:28
 Re: Bug in the date code... glitch177k1-Jun-04 10:06 glitch177k 1-Jun-04 10:06
 Pretty useful but... Carl Mercier15-Dec-03 7:54 Carl Mercier 15-Dec-03 7:54
 Re: Pretty useful but... glitch177k15-Dec-03 7:59 glitch177k 15-Dec-03 7:59
 Re: Pretty useful but... johncronin15-Dec-03 11:21 johncronin 15-Dec-03 11:21
 Re: Pretty useful but... glitch177k15-Dec-03 11:50 glitch177k 15-Dec-03 11:50
 Last Visit: 31-Dec-99 18:00     Last Update: 20-Oct-16 8:06 Refresh 1