wrong value callculated

[awake2424]

I can not figure out why the below calculates TAT as 5 not 2.

Basically, I am trying to calculate a integer number difference from two dates (TAT = Due-Date - Result_Date). The number is calculated and excludes weekends and ideally holidays (for that I have a tblHoliday but not sure how to use it). The function below seems to calculate a number but doesn't exclude weekends.

For example, if Due_Date is 9/26/2014 and Result_Date is 9/30/2014, then TAT is calculated to be 5 (should be 2)

Since 9/26/2014 is a Friday only Friday and Monday are used in the calculation. Thanks.

 Option Compare Database

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long


Dim intCount As Long

intCount = 0

Do While StartDate <= EndDate

Select Case Weekday(StartDate)
   Case Is = 1, 7
      intCount = intCount
   Case Is = 2, 3, 4, 5, 6
      intCount = intCount + 1
End Select

StartDate = StartDate + 1

Loop

WorkingDays = intCount

End Function

 

[awake2424]

Re: wrong value calculated

Due_Date: 9/25/2014 and Result_Date: 9/29/2014 = TAT: 4 (should be 2)

 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.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2
    
    ' The number of days inclusive.
    Dim varDays As Variant
    
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    
    ' Temporary storage for datetime.
    Dim dtmX As Date
    
    ' If the end date is earlier, swap the dates.
    If endDate < 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 " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function

then I call this function:

 Private Sub Result_Date_AfterUpdate()
    Me.[TAT] = Weekdays([Due_Date] - [Result_Date], 0)
End Sub

Also, to exclude holidays, will something like: (have a Holidays table)

 ' Count the number of holidays.

Dim nHoliday As Intager

    nHolidays = DCount(Expr:="[Holiday]", _
        Domain:=strHolidays, _
        Criteria:=strWhere)
    
    Weekdays = nWeekdays - nHolidays
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit

Thanks .