title: MS ACCESS VBA, working days function incl. holiday when faling on weekend
body: <p>When deploying MSDN function for calculating working days, beside a problem with date formatting I found an issue with Holiday count.</p>
  
  <p>Calculation is correct, but only if Holiday is on working day. If it is on a saturday or sunday, it also substract it and produce a false result. 
  <a href="https://i.stack.imgur.com/RsypH.jpg" rel="nofollow noreferrer">illustration of a false reading</a></p>
  
  <p>A Function for Workdays:</p>
  
  <pre><code>Public Function Workdays(ByRef startDate As Date, ByRef endDate As Date, Optional ByRef strHolidays As String = "Holidays") As Integer
  On Error GoTo Workdays_Error
  Dim nWeekdays, nHolidays As Integer
  Dim strWhere As String
  
  startDate = DateValue(startDate)
  endDate = DateValue(endDate)
  nWeekdays = Weekdays(startDate, endDate)
  
  If nWeekdays = -1 Then
      Workdays = -1
      GoTo Workdays_Exit
  End If
  
  strWhere = "[Holiday] &gt;= #" &amp; Format(startDate, "yyyy\/mm\/dd") &amp; "# AND [Holiday] &lt;= #" &amp; Format(endDate, "yyyy\/mm\/dd") &amp; "#"
  nHolidays = DCount(Expr:="[Holiday]", Domain:=strHolidays, Criteria:=strWhere)
  Workdays = nWeekdays - nHolidays
  
  Workdays_Exit:
      Exit Function
      Resume Workdays_Exit
  
  End Function
  </code></pre>
  
  <p>And here is a function for calculating weekdays:</p>
  
  <pre><code>Public Function Weekdays(ByRef startDate As Date, ByRef endDate As Date) As Integer
  ' Returns the number of weekdays in the period from startDate
  ' to endDate inclusive. Returns -1 if an error occurs.
  
  On Error GoTo Weekdays_Error
  Const ncNumberOfWeekendDays As Integer = 2 'The number of weekend days per week.
  Dim varDays As Variant                  'The number of days inclusive.
  Dim varWeekendDays As Variant     'The number of weekend days.
  Dim dtmX As Date                          'Temporary storage for datetime.
  
  ' If the end date is earlier, swap the dates.
  If endDate &lt; startDate Then
      dtmX = startDate
      startDate = endDate
      endDate = dtmX
  End If
  
  ' Calculate the number of days inclusive (+ 1 is to add back startDate).
  varDays = DateDiff(Interval:="d", date1:=startDate, date2:=endDate) + 1
  
  ' Calculate the number of weekend days.
  varWeekendDays = (DateDiff(Interval:="ww", date1:=startDate, date2:=endDate) _
      * ncNumberOfWeekendDays) + IIf(DatePart(Interval:="w", _
      Date:=startDate) = vbSunday, 1, 0) + IIf(DatePart(Interval:="w", Date:=endDate) = vbSaturday, 1, 0)
  
  ' Calculate the number of weekdays.
  Weekdays = (varDays - varWeekendDays)
  
  Weekdays_Exit:
      Exit Function
  
  Weekdays_Error:
      Weekdays = -1
      MsgBox "Error " &amp; Err.Number &amp; ": " &amp; Err.Description, vbCritical, "Weekdays"
      Resume Weekdays_Exit
  </code></pre>
  
  <p>Please advise how to ignore holiday if holiday = 1 or holiday = 7.
  Plus, in extended period of time, there could be more than one holidays, falling or not on weekend.</p>