none
Convert Networkdays Formula to Access 2010 Query RRS feed

  • Question

  • Hello,

    I have a reporting database, which uses DateDiff to count the aging days of open invoices. The customer has asked me to exclude weekends from the count. I can do this in Excel using a Networkdays formula, but don't know how to do this in an Access select query.

    I've seen a few suggestions all of which involve complicated/lengthy code. Does anyone have a suggestion on how to accomplish this?


    Lorac1969

    Thursday, August 3, 2017 7:05 PM

Answers

  • Here is a custom function that returns the same values as the Excel function NETWORKDAYS, but returns Null if the start date or end date is Null or not a valid date:

    Public Function WorkingDays(ByVal StartDate As Variant, ByVal EndDate As Variant) As Variant
        Dim lngCount As Long
        Dim dtmCurr As Date
        Dim lngSign As Long

        On Error GoTo ExitHandler
        WorkingDays = Null

        If Not IsDate(StartDate) Or Not IsDate(EndDate) Then
            Exit Function
        End If

        If EndDate < StartDate Then
            dtmCurr = EndDate
            EndDate = StartDate
            lngSign = -1
        Else
            dtmCurr = StartDate
            lngSign = 1
        End If

        lngCount = 0

        Do While dtmCurr <= EndDate
            If Weekday(dtmCurr, vbMonday) < 6 Then
                lngCount = lngCount + 1
            End If
            dtmCurr = dtmCurr + 1
        Loop

        WorkingDays = lngCount * lngSign

    ExitHandler:
    End Function

    Use like this in a query:

    NetWorkDays: WorkingDays([InvoiceDate],Date())

    where InvoiceDate is the date of the invoice. Or in the Control Source of a text box on a form or report:

    =WorkingDays([InvoiceDate],Date())


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Lorac1969 Wednesday, August 9, 2017 5:27 PM
    Tuesday, August 8, 2017 7:13 PM

All replies

  • You do need VBA for this, but you can simply use it, you don't really have to understand it.

    Do you only need to exclude weekend days, or would you also like to exclude public holidays?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, August 3, 2017 7:16 PM
  • Hi,

    If you can do it in Excel, you should be able to call the Excel function from Access' VBA. So, if you want to use it in a query, you can create a simple function in Access to execute the Excel function and then return the result to your query.

    Hope it helps...

    Thursday, August 3, 2017 7:52 PM
  • Hi,

    I just gave it a quick try, and here's what I was able to use.

    Public Function NetWorkdays(StartDate As Date, EndDate As Date) As Long
    '8/3/2017
    'thedbguy@gmail.com
    'Uses Excel's NetWorkdays() function
    
    Dim xl As Object
    
    Set xl = CreateObject("Excel.Application")
    
    NetWorkdays = xl.WorksheetFunction.NetWorkdays(Format(StartDate, "yyyy-mm-dd"), _
        Format(EndDate, "yyyy-mm-dd"))
    
    Set xl = Nothing
    
    End Function
    
    

    Hope it helps...

    Thursday, August 3, 2017 8:23 PM
  • I'm not sure what you would consider lengthy. There is nothing built into Access to do this so you have to write code. Fortunately, someone has done for you in the below link:

    http://www.utteraccess.com/forum/index.php?showtopic=1947978


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, August 3, 2017 9:31 PM
  • Hi Hans,

    They're only concerned with excluding weekends.


    Lorac1969

    Tuesday, August 8, 2017 4:47 PM
  • Here is a custom function that returns the same values as the Excel function NETWORKDAYS, but returns Null if the start date or end date is Null or not a valid date:

    Public Function WorkingDays(ByVal StartDate As Variant, ByVal EndDate As Variant) As Variant
        Dim lngCount As Long
        Dim dtmCurr As Date
        Dim lngSign As Long

        On Error GoTo ExitHandler
        WorkingDays = Null

        If Not IsDate(StartDate) Or Not IsDate(EndDate) Then
            Exit Function
        End If

        If EndDate < StartDate Then
            dtmCurr = EndDate
            EndDate = StartDate
            lngSign = -1
        Else
            dtmCurr = StartDate
            lngSign = 1
        End If

        lngCount = 0

        Do While dtmCurr <= EndDate
            If Weekday(dtmCurr, vbMonday) < 6 Then
                lngCount = lngCount + 1
            End If
            dtmCurr = dtmCurr + 1
        Loop

        WorkingDays = lngCount * lngSign

    ExitHandler:
    End Function

    Use like this in a query:

    NetWorkDays: WorkingDays([InvoiceDate],Date())

    where InvoiceDate is the date of the invoice. Or in the Control Source of a text box on a form or report:

    =WorkingDays([InvoiceDate],Date())


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Lorac1969 Wednesday, August 9, 2017 5:27 PM
    Tuesday, August 8, 2017 7:13 PM
  • I've seen a few suggestions all of which involve complicated/lengthy code.
    If you are really averse to using VBA code then a simple and efficient means of doing this is to include an auxiliary calendar table of workdays only in your database, i.e. a table of all dates bar weekend dates over a period of time, e.g. the next 10 years or so.  To create such a table see Calendar.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little file enables you to create various calendar tables very easily.  The 'Daily Calendar' option would be appropriate in your case.  Having created the table you can then import it into your database.  To return the number of working days between two dates is then simply a matter of calling the DCount function with a criterion which defines the date range.


    Ken Sheridan, Stafford, England

    Tuesday, August 8, 2017 10:28 PM