Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Working With Business Dates (Business Holidays/Weekends, Etc.)

0.00/5 (No votes)
9 Dec 2003 2  
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 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)
'Returns the last day of the month/year passed in "mm/dd/yyyy" format
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)
'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 (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  'Sunday
                      iDate = DateAdd("d", -1, iDate)
         Case 7  'Saturday
                      iDate = DateAdd("d", -1, iDate)
         Case else    'this is a valid day
                      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)
'Adds given number of business days to date.
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  'Sunday
                      iDate = dateadd("d", 1, iDate)
         Case 7    'Saturday
                      iDate = DateAdd("d", 1, iDate)
         Case else    'this is a valid day
                      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

   '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()
      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:

  1. Is the date a holiday, or a weekend
  2. 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.

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