Return dates excluding weekends and holidays

[puforee]

I am working on a teaching db for courses. Known information, week start day, start date of course, number of days in the course, and holidays.

Given the start date and number of days I would like the query to return all dates for the class excluding weekends and holidays.

The reason for knowing the week start date is so it can be used in any country.

Can anyone help. Also, is there a way to set the work week in MS Access 2010 or do I have to use the weekday function everywhere?

Thanks,

 

[dhookom]

I would recommend creating a table of dates with holidays. Check out faq705-6003.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Great...I found a little hint and code to calculate the end date and used the holiday table too. Now I have to calculate all the workdays, non-holidays, between the two dates...use a query right...but what function should go with that so I don't get any holidays or weekends?

 

[MajP]

Do you have a record for every day somewhere for each class? From what you describe I do not think you do. So you cannot do it in a query unless you make a table of dates. But if you are loading a big table of dates then just add the column for weekend, holiday. You can use VBA to load that table. The other alternative is to do it dynamically, but I would just make the big table of dates.

 

[MajP]

This is the code I use to create a table of dates. I also have a module to determine if that date is a holiday. Then you can edit the holidays to add your specific school/buisness holiday

Public Sub FillDates()
  'Edit this to fill table
  Const tableName = "tblDates"
  Const StartDate = #1/1/2015#
  Const EndDate = #12/31/2016#
  
  Dim IterativeDate As Date
  Dim strDate As String
  Dim strSql As String
  Dim DayOfWeek As String
  Dim blnHoliday As Boolean
  
  IterativeDate = StartDate
  Do While IterativeDate < EndDate
    strDate = SQLDate(IterativeDate)
    DayOfWeek = WeekdayName(Weekday(IterativeDate))
    DayOfWeek = sqlTxt(DayOfWeek)
    blnHoliday = IsHoliday(IterativeDate)
    'field names need to match your table
    strSql = "Insert into " & tableName & " (dtmDate, DayOfWeek, IsHoliday) values (" & strDate & ", " & DayOfWeek & ", " & blnHoliday & ")"
    IterativeDate = IterativeDate + 1
    Debug.Print strSql
    CurrentDb.Execute strSql
  Loop
  For i = 0 To numberOfDays
  Next i
End Sub

Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function
Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

module for holidays

Public Function IsHoliday(ByVal dtmDate As Date) As Boolean
   Dim intYear As Integer
   Dim intMonth As Integer
   Dim intDay As Integer
   Dim intWeekDay As Integer
   intYear = Year(dtmDate)
   intMonth = Month(dtmDate)
   intDay = Day(dtmDate)
   intWeekDay = Weekday(dtmDate)
   
   'If you do not care if a holiday falls on a weekend
   'If intWeekDay > 1 And intWeekDay < 7 Then exit Function
    'New Years Day
     If DatePart("y", dtmDate) = 1 Then
       IsHoliday = True
       Exit Function
     End If
    'ML King 3rd Monday of Jan
       If DayOfNthWeek(intYear, 1, 3, vbMonday) = dtmDate Then
          IsHoliday = True
          Exit Function
       End If
    'Presidents Day  3rd Monday of Feb
    If DayOfNthWeek(intYear, 2, 3, vbMonday) = dtmDate Then
          IsHoliday = True
          Exit Function
    End If
    'Memorial Day    Last Monday of May
      If LastMondayInMonth(intYear, 5) = dtmDate Then
         IsHoliday = True
         Exit Function
      End If
    'Independance Day
       If intMonth = 7 And intDay = 4 Then
          IsHoliday = True
          Exit Function
       End If
    'Labor Day   1st Monday of Sep
        If DayOfNthWeek(intYear, 9, 1, vbMonday) = dtmDate Then
          IsHoliday = True
          Exit Function
       End If
    'Columbus Day    2nd Monday of Oct
        If DayOfNthWeek(intYear, 10, 2, vbMonday) = dtmDate Then
          IsHoliday = True
          Exit Function
       End If
    'Thanksgiving Day  4th Thursday of Nov
       If DayOfNthWeek(intYear, 11, 4, vbThursday) = dtmDate Then
          IsHoliday = True
          Exit Function
       End If
    'CHRISTMAS
        If intMonth = 12 And intDay = 25 Then IsHoliday = True
End Function
Public Function DayOfNthWeek(intYear As Integer, intMonth As Integer, N As Integer, vbDayOfWeek As Integer) As Date
  'Thanksgiving is the 4th thursday in November(11)
  'dayOfNthWeek(theYear,11,4,vbThursday)
   DayOfNthWeek = DateSerial(intYear, intMonth, (8 - Weekday(DateSerial(intYear, intMonth, 1), _
 (vbDayOfWeek + 1) Mod 8)) + ((N - 1) * 7))
End Function
Function LastMondayInMonth(intYear As Integer, intMonth As Long) As Date
    'Used for memorial day
    Dim LastDay As Date
    'define last day of the month of interest:
    LastDay = DateSerial(intYear, intMonth + 1, 0)
    'use to get last monday:
    LastMondayInMonth = LastDay - Weekday(LastDay, vbMonday) + 1
End Function

So you query would be between start date and end date and day of week not Sat and Not Sun and not holiday.

My table of dates would look like

[tt]
ID	dtmDate	dayOfWeek     isHoliday
1	1/1/2015	Thursday	     True
2	1/2/2015	Friday	     False
3	1/3/2015	Saturday	     False
4	1/4/2015	Sunday        False
5	1/5/2015	Monday        False
6	1/6/2015	Tuesday       False
7	1/7/2015	Wednesday     False
8	1/8/2015	Thursday	     False
9	1/9/2015	Friday        False
[/tt]