Calculate Work Days (1 Viewer)

[LadyDi]

Is there a way in a query to calculate the number of workdays between two dates?

I have an order received date and an order released date. I would like to see the number of workdays between those two dates. For example, if an order was received on April 8 and released on April 11, it should show 1 day (right now, it is showing 3 days).

Any assistance you can provide would be greatly appreciated.

 

[Isskint]

You would need to build a custom function. have a look at this article for advice https://msdn.microsoft.com/en-us/library/dd327646%28v=office.12%29.aspx?f=255&MSPPError=-2147217396

 

[arnelgp]

paste the code in a new module:
if you do not have a holiday table, just call it:

workingDays = GetNetWorkDays3([fromDateField], [toDateField], "")

Public Function GetNetWorkDays3( _
          ByVal datDateFrom As Variant, _
            ByVal datDateTo As Variant, _
                ByVal strCountry As String, _
                    Optional ByVal booExcludeHolidays As Boolean) As Long
    Dim swp As Variant
    Dim I As Variant
    Dim lngDays As Long
    Dim strDateFrom As String
    Dim strDateTo As String
    Dim strFilter As String
    Dim strSQL As String
    Dim lngHolidays As Long
    Dim rs As DAO.Recordset
    Dim tempDate As Date
    
    ' Name of table with holidays.
    Const cstrTableHoliday    As String = "tbl_eDealHolidays"
    ' Name of date field in holiday table.
    Const cstrFieldHoliday    As String = "holDate"
    Const cstrCountryField    As String = "CountryCode"
    
    If IsNull(datDateFrom) Then datDateFrom = Date
    If IsNull(datDateTo) Then datDateTo = Date
    If datDateFrom > datDateTo Then
        swp = datDateFrom
        datDateFrom = datDateTo
        datDateTo = swp
    End If
    datDateFrom = CDate(Format(datDateFrom, "mm/dd/yyyy"))
    datDateTo = CDate(Format(datDateTo, "mm/dd/yyyy"))
    tempDate = datDateFrom
    While tempDate <= datDateTo
        If InStr("/Saturday/Sunday/", Format(tempDate, "dddd")) = 0 Then
            lngDays = lngDays + 1
        End If
        tempDate = DateAdd("d", 1, tempDate)
    Wend
    
  If booExcludeHolidays And lngDays > 0 Then
    strDateFrom = Format(datDateFrom, "mm/dd/yyyy")
    strDateTo = Format(datDateTo, "mm/dd/yyyy")
    strFilter = "[" & cstrCountryField & "] = " & Chr(34) & strCountry & Chr(34) & " "
    strFilter = strFilter & " And " & _
            "[" & cstrFieldHoliday & "] Between #" & strDateFrom & "# And #" & strDateTo & "#;"
    strSQL = "SELECT [" & cstrFieldHoliday & "] FROM [" & cstrTableHoliday & "] " & _
            "WHERE " & strFilter
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            If InStr("/Saturday/Sunday/", Format(rs(0).value, "dddd")) = 0 Then
                lngHolidays = lngHolidays + 1
            End If
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
  End If
    GetNetWorkDays3 = lngDays - lngHolidays
End Function

 

[LadyDi]

That works perfectly. Thank you very much for your help.