Calculating Dates based off Business Days (1 Viewer)

mikeh1992 · Mar 9, 2017

Hello,

I am looking to construct a query that will allow me to update a table based off calculated delivery date. I do not want to include weekends since no delivery will occur. Is there a way to do this in Access? I noticed there was not a "Networkdays" function like there is in Excel.

Below is an example. I want to be able to calculate the Delivery date based off the Ship date and expected transit days.
Need to add the Ship date and Expected Transit Days together, get the date then convert it to not include weekend days.

below are the fields in the table. Table entitled [tbl_current_tracking_nbrs]

SHIP_DATE Expected_Transit_Days Delivery_Date

Thank you!

CJ_London · Mar 9, 2017

a common question with many answers - look to the bottom of this thread and you will see a number of solutions.

theoldhopbarn · Mar 19, 2019

Hello Everyone,

I'm new here, but thought this might help one or two of you out there. It is the function for NETWORKDAYS and works the same as in Excel.

It requires a table called BankHolidays which has just one field called BankHoliday, which is data type Short Date.

Code:

Public Function NetWorkDays(useStart As Date, useEnd As Date, Optional useTable As String = "DoNotCheckBankHolidays") As Integer
'returns the number of days (M-F) between two dates inclusive with option to check for bank holidays

Dim foundIt As Variant
Dim useDate As Date
Dim numDays As Integer

On Error GoTo Err_NetWorkDays

numDays = 0

For useDate = useStart To useEnd Step 1

    If Weekday(useDate) = 7 Or Weekday(useDate) = 1 Then
        'do nothing
    Else
        If useTable = "DoNotCheckBankHolidays" Then
            'no table of bank holidays
            numDays = numDays + 1
        Else
            foundIt = DLookup("[BankHoliday]", useTable, "[BankHoliday]=#" & useDate & "#")
            
            If IsNull(foundIt) Then
                'not a bank holiday
                numDays = numDays + 1
            Else
                'is a bank holiday
            End If
        End If
    End If
Next useDate

NetWorkDays = numDays

Exit_NetWorkDays:
    Exit Function

Err_NetWorkDays:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "NetWorkDays()"
    Resume Exit_NetWorkDays

End Function

Calculating Dates based off Business Days (1 Viewer)

mikeh1992

New member

CJ_London

Super Moderator

theoldhopbarn

New member

Similar threads

Users who are viewing this thread