ACC: How to Determine If a Date Falls on a Weekend or Holiday

MORE INFORMATION

The following example uses the WeekDay() function to determine if a specific date falls on a Saturday or Sunday. Then, it uses a DLookup() function to determine if it falls on a date stored in a user-created Holidays table.

Creating a Holidays Table

The following example requires a table with a particular structure for storing Holiday dates. You can follow these steps for creating the table and sample records:

1. Create a new table in Design view and add the following fields:

         Table: Holidays
         -----------------------
         Field Name: Description
            Data Type: Text
         Field Name: HoliDate
            Date Type: Date/Time
   						

2. Save the table as Holidays and switch the table to Datasheet view. Add the following records:

         Description                         HoliDate
         --------------------------------------------
         New Year's Day                      1/1/96
         Martin Luther King, Jr. Day (USA)   1/15/96
         Memorial Day (observed-USA)         5/27/96
         Labor Day (USA)                     9/2/96
   						

3. Close and save the Holidays table.

Creating the Custom Function

To create a function that determines if a date falls on a weekend or holiday, follow these steps:

1. Create a new module in Design view.
2. Add the following function:

        Function OfficeClosed(TheDate) As Integer
         OfficeClosed = False
   
            ' Test for Saturday or Sunday.
            If WeekDay(TheDate) = 1 Or WeekDay(TheDate) = 7 Then
               OfficeClosed = True
            ' Test for Holiday.
            ElseIf Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" _
               & TheDate & "#")) Then
               OfficeClosed = True
            End If
   
         End Function
   						

3. To test this function, type the following line into the Debug window, and then press ENTER:

   ? OfficeClosed(#9/2/96#)

   Note that this returns a True value (-1) because 9/2/96 is listed in the Holidays table.

Usage Example

You can use the custom OfficeClosed() function to calculate due dates. For example, if your office or business is closed for a 3-day weekend, you may want to extend your customers' grace period for their outstanding bills. Here's sample code for adding one more day to a grace period:

   DueDate=OrderDate+30
   Do While OfficeClosed(DueDate)
      DueDate=DateDue+1
   Loop