none
DateDiff Function RRS feed

  • Question

  • Is there a way to exclude weekends and public holidays when using the DateDiff Function?

    Gordon

    Wednesday, July 26, 2017 8:48 PM

Answers

  • Here is a custom function that returns the number of working days between two dates. It expects that you have created a table tblHolidays with a Date/Time field HolidayDate that has been populated with public holidays.

    Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long
        '....................................................................
        ' Name: WorkingDays
        ' Inputs: StartDate As Date
        ' EndDate As Date
        ' Returns: Long
        ' Author: Arvin Meyer
        ' Date: May 5,2002
        ' Comment: Accepts two dates and returns the number of weekdays between them
        ' Note that this function has been modified to account for holidays.
        ' It requires a table named tblHolidays with a field named HolidayDate.
        '....................................................................
    
        Dim lngCount As Long
        Dim dtmCurr As Date
        Dim rst As DAO.Recordset
        Dim dbs As DAO.Database
    
        On Error GoTo ErrHandler
    
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("tblHolidays", dbOpenSnapshot)
    
        ' Use the following line to include StartDate
        ' dtmCurr = StartDate
        ' Use the following line to exclude StartDate
        dtmCurr = StartDate + 1
    
        lngCount = 0
    
        Do While dtmCurr <= EndDate
            If Weekday(dtmCurr, vbMonday) < 6 Then
                rst.FindFirst "[HolidayDate] = #" & Format(dtmCurr, "mm/dd/yyyy") & "#"
                If rst.NoMatch Then
                    lngCount = lngCount + 1
                End If
            End If
            dtmCurr = dtmCurr + 1
        Loop
    
        WorkingDays = lngCount
    
    ExitHandler:
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing
        Exit Function
    
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Function


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

    • Proposed as answer by Terry Xu - MSFT Friday, July 28, 2017 4:56 AM
    • Marked as answer by Gordon Swan Thursday, August 3, 2017 11:05 AM
    Wednesday, July 26, 2017 9:51 PM
  • Preparation:

    • Create the table tblHolidays and populate it, as described in my previous reply.
    • Activate the Visual Basic Editor, select Insert > Module, and copy the code from my previous reply into the module.
    • Switch back to Access.

    Let's say you have a table with - among others - fields StartDate and EndDate.

    In a query based on the table, you can define a calculated column

    WorkDays: WorkingDays([StartDate],[EndDate])

    You can then use this query as record source for forms and reports.

    Alternatively, you can create a text box on a form or report based on the table, and set its Control Source to

    =WorkingDays([StartDate],[EndDate])


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

    • Marked as answer by Gordon Swan Thursday, August 3, 2017 11:06 AM
    Thursday, July 27, 2017 5:48 AM

All replies

  • Here is a custom function that returns the number of working days between two dates. It expects that you have created a table tblHolidays with a Date/Time field HolidayDate that has been populated with public holidays.

    Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long
        '....................................................................
        ' Name: WorkingDays
        ' Inputs: StartDate As Date
        ' EndDate As Date
        ' Returns: Long
        ' Author: Arvin Meyer
        ' Date: May 5,2002
        ' Comment: Accepts two dates and returns the number of weekdays between them
        ' Note that this function has been modified to account for holidays.
        ' It requires a table named tblHolidays with a field named HolidayDate.
        '....................................................................
    
        Dim lngCount As Long
        Dim dtmCurr As Date
        Dim rst As DAO.Recordset
        Dim dbs As DAO.Database
    
        On Error GoTo ErrHandler
    
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("tblHolidays", dbOpenSnapshot)
    
        ' Use the following line to include StartDate
        ' dtmCurr = StartDate
        ' Use the following line to exclude StartDate
        dtmCurr = StartDate + 1
    
        lngCount = 0
    
        Do While dtmCurr <= EndDate
            If Weekday(dtmCurr, vbMonday) < 6 Then
                rst.FindFirst "[HolidayDate] = #" & Format(dtmCurr, "mm/dd/yyyy") & "#"
                If rst.NoMatch Then
                    lngCount = lngCount + 1
                End If
            End If
            dtmCurr = dtmCurr + 1
        Loop
    
        WorkingDays = lngCount
    
    ExitHandler:
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing
        Exit Function
    
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Function


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

    • Proposed as answer by Terry Xu - MSFT Friday, July 28, 2017 4:56 AM
    • Marked as answer by Gordon Swan Thursday, August 3, 2017 11:05 AM
    Wednesday, July 26, 2017 9:51 PM
  • How do I pass the dates to the Public Function? Could you give me an example of the code to use and could it be done from a form or a query?
    Wednesday, July 26, 2017 10:51 PM
  • Preparation:

    • Create the table tblHolidays and populate it, as described in my previous reply.
    • Activate the Visual Basic Editor, select Insert > Module, and copy the code from my previous reply into the module.
    • Switch back to Access.

    Let's say you have a table with - among others - fields StartDate and EndDate.

    In a query based on the table, you can define a calculated column

    WorkDays: WorkingDays([StartDate],[EndDate])

    You can then use this query as record source for forms and reports.

    Alternatively, you can create a text box on a form or report based on the table, and set its Control Source to

    =WorkingDays([StartDate],[EndDate])


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

    • Marked as answer by Gordon Swan Thursday, August 3, 2017 11:06 AM
    Thursday, July 27, 2017 5:48 AM
  • You might like to take a look at Workdays.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 demo file illustrates a number of functions for undertaking workdays arithmetic, either in the context of a single country with a single set of public/concessionary holidays, or in a multiple countries with differing sets of public/concessionary holidays

    Ken Sheridan, Stafford, England

    Thursday, July 27, 2017 11:31 AM
  • Hi Gordon,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply or provide your solution and then mark it as answer to close this thread. 
    If not, please feel free to let us know your current issue.

    Best Regards,

    Terry

    Thursday, August 3, 2017 8:27 AM
  • Thank you. It works perfect.
    Thursday, August 3, 2017 11:07 AM