none
Code Use (Counting the Number of Working Days) RRS feed

  • Question

  • Hello,

    First, I am NOT a experienced user of code.  I have used Access quite a bit but not code.

    I am trying to calculate the Number of Work Days between two dates.  Following are pics of what I have at this point...

    Option Compare Database
    Option Explicit
    Public Function WeekdayCalc(ByRef PFAStart As Date, _
        ByRef PFAFinish As Date _
        ) As Integer
        ' Returns the number of weekdays in the period from PFAStart
        ' to PFAFinish 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 WeekdayCalc_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 PFAFinish < PFAStart Then
            dtmX = PFAStart
            PFAStart = PFAFinish
            PFAFinish = dtmX
        End If
        ' Calculate the number of days inclusive (+ 1 is to add back PFAStart).
        varDays = DateDiff(Interval:="d", _
            date1:=PFAStart, _
            date2:=PFAFinish) + 1
        ' Calculate the number of weekend days.
        varWeekendDays = (DateDiff(Interval:="ww", _
            date1:=PFAStart, _
            date2:=PFAFinish) _
            * ncNumberOfWeekendDays) _
            + IIf(DatePart(Interval:="w", _
            Date:=PFAStart) = vbSunday, 1, 0) _
            + IIf(DatePart(Interval:="w", _
            Date:=PFAFinish) = vbSaturday, 1, 0)
        ' Calculate the number of weekdays.
        WeekdayCalc = (varDays - varWeekendDays)
    WeekdayCalc_Exit:
        Exit Function
    WeekdayCalc_Error:
        WeekdayCalc = -1
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
            vbCritical, "WeekdayCalc"
        Resume WeekdayCalc_Exit
    End Function
    


    When I run the simple query above, I receive 'Undefined function "WeekdayCalc" in expression'.

    Any assistance would be appreciated.


    Thanks PU_RJF

    Saturday, February 29, 2020 5:15 PM

Answers

  • A simple, and more flexible, way to do this is to firstly create an auxiliary calendar table, named WorkdaysCalendar in the example below.  This is simply a table of all dates over a range of however many years into the future you wish.  The following simple function then returns the number of working days between two dates by calling the DCount function in code:

    Public Function WorkdaysDiffVariable(varFrom As Variant, varTo As Variant, blnExcludePubHols As Boolean, ParamArray varWorkedDays() As Variant)

        Dim strCriteria As String
        Dim strDaysWorked As String
        Dim strQuery As String
        Dim var As Variant
        
        If IsNull(varFrom) Or IsNull(varTo) Then
            Exit Function
        End If
        
        ' start count from day following start date
        varFrom = varFrom + 1
        
        ' get value list of working days of week from parameter array
        For Each var In varWorkedDays
            strDaysWorked = strDaysWorked & "," & var
        Next var
        ' remove leading comma and wrap in parentheses
        strDaysWorked = "(" & Mid(strDaysWorked, 2) & ")"
        
        strCriteria = "calDate BETWEEN #" & _
            Format(varFrom, "yyyy-mm-dd") & "# AND #" & _
            Format(varTo, "yyyy-mm-dd") & "# AND " & _
            "Weekday(caldate) IN" & strDaysWorked
            
        ' if public holidays excluded from count
        ' use query, else use calendar table
        If blnExcludePubHols Then
            strQuery = "qryWorkdays"
        Else
            strQuery = "WorkDaysCalendar"
        End If
            
        WorkdaysDiffVariable = DCount("*", strQuery, strCriteria)
        
    End Function

    The function allows you to govern the results returned not only between the two dates, but also optionally excluding public or concessionary holidays from the count, and on the basis of any working week defined by a comma separated list of integer numbers in which 1 represents Sunday, 2 Monday and so on.

    Public or concessionary holidays are stored in a PubHols table, which again is just a table of dates.  If public holidays are excluded from the count the function ignores any public holiday dates in this table when computing the number of days.

    To exclude public holidays from the count the following query, named qryWorkdays is included in the database:

    SELECT *
    FROM WorkdaysCalendar LEFT JOIN PubHols
    ON WorkdaysCalendar.calDate=PubHols.holDate
    WHERE PubHols.holDate IS NULL;

    So to return the number of working days over the next two weeks, excluding any public holidays from the count, and for a working week from Tuesday to Saturday, the function would be called like this:

        WorkdaysDiffVariable(#2020-3-01#,#2020-03-14#,True, 3,4,5,6,7)

    The above function etc are taken from Workdays.zip in my public databases folder at:

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

    This little demo file illustrates a number of functions for doing workdays arithmetic.  An interface in which the dates and workdays per week can be selected in a form illustrates the use of the function.

    A calendar table can be created using the Calendar.zip demo in the same OneDrive folder.  The option to create a 'daily calendar' should be used for the above, naming the table WorkdaysCalendar and selecting all days of the week.


    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Monday, March 2, 2020 12:53 PM
    Monday, March 2, 2020 12:31 AM
  • Modules and functions should not have the same name.

    -Tom. Microsoft Access MVP

    • Marked as answer by PU_RJF Monday, March 2, 2020 12:53 PM
    Saturday, February 29, 2020 6:38 PM
  • As you appear to have a standard Monday to Friday working week we can cater for all your requirements by means of a  very simple function.  First we need to make a small amendment to the WorkdaysCalendar table:

    1.  Add a Boolean (Yes/No) column named Unworked.  In reality this means weekend days in your case.

    2.  Next update the new column by executing the following UPDATE query:

    UPDATE WorkdaysCalendar
    SET Unworked = True
    WHERE WeekDay(calDate,2) > 5;

    We now need a new query named qryWorkedDays.  Assuming that you have a PubHols table with the dates of public and concessionary holidays the query will be:

    SELECT calDate
    FROM WorkdaysCalendar LEFT JOIN PubHols
    ON WorkdaysCalendar.calDate = PubHols.Holdate
    WHERE NOT Unworked
    AND HolDate IS NULL;

    If you don't want to take public and concessionary holidays into account when computing the number of days it will be:

    SELECT calDate
    FROM WorkdaysCalendar
    WHERE NOT Unworked;

    We can now create the function:

    Public Function WorkDaysInRange(varStartDate As Variant, varEndDate As Variant) As Variant

        Dim strCriteria As String
        
        ' ensure both start and end dates are not Null
        If Not IsNull(varStartDate + varEndDate) Then
            strCriteria = "caldate BETWEEN #" & Format(varStartDate, "yyyy-mm-dd") & "# " & _
                "AND #" & Format(varEndDate, "yyyy-mm-dd") & "#"
                
            ' get number of days in range
            WorkDaysInRange = DCount("*", "qryWorkedDays", strCriteria)
            
            ' reverse sign of return value if end date precedes start date
            If varStartDate > varEndDate Then
                WorkDaysInRange = WorkDaysInRange * -1
            End If
        End If
        
    End Function

    A BETWEEN…..AND operation is inclusive, and will return the same number of days whether the start date precedes the end date or is later than the end date, so the function simply reverses the sign if the latter is the case.  We can see how it works in the immediate window with a two week range.  First if there are no public or concessionary holidays in the range and the start date precedes the end date:

    ? WorkDaysInRange(#2020-03-01#,#2020-03-14#)
     10

    If the end date precedes the start date:

    ? WorkDaysInRange(#2020-03-14#,#2020-03-01#)
    -10

    If we now make 2nd March 2020 a holiday (it's Labour Day in Western Australia apparently) by adding it as a row in the PubHols table, we can see what happens with the same two examples:

    ? WorkDaysInRange(#2020-03-01#,#2020-03-14#)
     9
    ? WorkDaysInRange(#2020-03-14#,#2020-03-01#)
    -9

    You can still use my WorkdaysDiffVariable function where greater flexibility is needed of course, but for your specific requirements the above should provide a simple and very efficient solution with minimal procedural code.

    Now that you have an auxiliary calendar table you can of course extend its usefulness by adding other columns.to suit any other requirements you might have now or in the future.  Also, in many date-oriented tasks there is a need to supply missing dates, e.g. in a room reservation database where the start and end dates of each reservation are recorded.  By joining the reservations table to an auxiliary calendar table you can, for instance, find whether a room is available on any specific date or dates.

    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Wednesday, March 4, 2020 12:51 PM
    Tuesday, March 3, 2020 5:39 PM

All replies

  • Modules and functions should not have the same name.

    -Tom. Microsoft Access MVP

    • Marked as answer by PU_RJF Monday, March 2, 2020 12:53 PM
    Saturday, February 29, 2020 6:38 PM
  • A simple, and more flexible, way to do this is to firstly create an auxiliary calendar table, named WorkdaysCalendar in the example below.  This is simply a table of all dates over a range of however many years into the future you wish.  The following simple function then returns the number of working days between two dates by calling the DCount function in code:

    Public Function WorkdaysDiffVariable(varFrom As Variant, varTo As Variant, blnExcludePubHols As Boolean, ParamArray varWorkedDays() As Variant)

        Dim strCriteria As String
        Dim strDaysWorked As String
        Dim strQuery As String
        Dim var As Variant
        
        If IsNull(varFrom) Or IsNull(varTo) Then
            Exit Function
        End If
        
        ' start count from day following start date
        varFrom = varFrom + 1
        
        ' get value list of working days of week from parameter array
        For Each var In varWorkedDays
            strDaysWorked = strDaysWorked & "," & var
        Next var
        ' remove leading comma and wrap in parentheses
        strDaysWorked = "(" & Mid(strDaysWorked, 2) & ")"
        
        strCriteria = "calDate BETWEEN #" & _
            Format(varFrom, "yyyy-mm-dd") & "# AND #" & _
            Format(varTo, "yyyy-mm-dd") & "# AND " & _
            "Weekday(caldate) IN" & strDaysWorked
            
        ' if public holidays excluded from count
        ' use query, else use calendar table
        If blnExcludePubHols Then
            strQuery = "qryWorkdays"
        Else
            strQuery = "WorkDaysCalendar"
        End If
            
        WorkdaysDiffVariable = DCount("*", strQuery, strCriteria)
        
    End Function

    The function allows you to govern the results returned not only between the two dates, but also optionally excluding public or concessionary holidays from the count, and on the basis of any working week defined by a comma separated list of integer numbers in which 1 represents Sunday, 2 Monday and so on.

    Public or concessionary holidays are stored in a PubHols table, which again is just a table of dates.  If public holidays are excluded from the count the function ignores any public holiday dates in this table when computing the number of days.

    To exclude public holidays from the count the following query, named qryWorkdays is included in the database:

    SELECT *
    FROM WorkdaysCalendar LEFT JOIN PubHols
    ON WorkdaysCalendar.calDate=PubHols.holDate
    WHERE PubHols.holDate IS NULL;

    So to return the number of working days over the next two weeks, excluding any public holidays from the count, and for a working week from Tuesday to Saturday, the function would be called like this:

        WorkdaysDiffVariable(#2020-3-01#,#2020-03-14#,True, 3,4,5,6,7)

    The above function etc are taken from Workdays.zip in my public databases folder at:

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

    This little demo file illustrates a number of functions for doing workdays arithmetic.  An interface in which the dates and workdays per week can be selected in a form illustrates the use of the function.

    A calendar table can be created using the Calendar.zip demo in the same OneDrive folder.  The option to create a 'daily calendar' should be used for the above, naming the table WorkdaysCalendar and selecting all days of the week.


    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Monday, March 2, 2020 12:53 PM
    Monday, March 2, 2020 12:31 AM
  • Thank you for the information!  Worked fine once I modified.

    PU_RJF


    Thanks PU_RJF

    Monday, March 2, 2020 12:54 PM
  • Ken,

    Thanks for the response.  I'm trying to implement with my current info.  I have two dates in my table/form (i.e., PFAStart and PFAFinish).  How do I incorporate those into...

    strCriteria = "calDate BETWEEN #" & _
            Format(varFrom, "yyyy-mm-dd") & "# AND #" & _
            Format(varTo, "yyyy-mm-dd") & "# AND " & _
            "Weekday(caldate) IN" & strDaysWorked

    Thank You

    PU_RJF


    Thanks PU_RJF

    Monday, March 2, 2020 3:26 PM
  • Ken,

    Think I resolved my prior question; however, I am receiving the following errors.  Do you have suggestions?


    Thanks PU_RJF

    Monday, March 2, 2020 4:52 PM
  • At first sight that would suggest that either the query named qryWkDays does not return a column named calDate in its result table, and/or the base table named tblWkDaysCal does not include a column named calDate.

    Ken Sheridan, Stafford, England

    Monday, March 2, 2020 5:55 PM
  • Ken,

    Thanks for the help!  I was merely missing the info in the query.

    One additional question...I am using this module to also calculate available days from Now() to my PFAFinish Date.  Is there a way to make this value negative if the Now() > PFAFinish?


    Thanks PU_RJF

    Monday, March 2, 2020 7:18 PM
  • Is there a way to make this value negative if the Now() > PFAFinish?
    The way to do that is to reverse the order of the two operands when calling the function, multiplying the return value by -1, e.g. in the immediate window:

    FinishDate = #2020-02-01#
    ? WorkdaysDiffVariable(FinishDate,Date(),True,2,3,4,5,6)*-1
    -21

    Note that if  you don't reverse the order of the operands an incorrect result is returned:

    ? WorkdaysDiffVariable(Date(),FinishDate,True,2,3,4,5,6)*-1
    -22

    BTW to return the current Date call the Date() function not the Now() function.  The latter includes the current  time of day, which could cause problems.


    Ken Sheridan, Stafford, England

    Monday, March 2, 2020 7:40 PM
  • Ken,

    Sorry to continue to bother you.  A couple of final questions...

    1. As you suggested, I switched the order of the two operands and multiplied by -1.  For one of the records I have a PFAFinish date = 3/13/2020.  Based on today's date 3/2/2020, I receive -10.  I believe that should be 10.  

    2. For one of my records the PFAStart is 1/13/2020 and the PFAFInish is 3/13/2020.  That is 45 weekdays; however, the function returns 44.  I assume that is because of the...

    ' start count from day following start date
        varFrom = varFrom + 1

    Why is that?


    Thanks PU_RJF

    Monday, March 2, 2020 8:57 PM
  • The multiplication by -1 was directed solely at your earlier post in which you asked ' Is there a way to make this value negative if the Now() > PFAFinish?'   Otherwise you would not need to multiply by -1, which simply reverses the sign of the return value.  If you have rows where the finish date will be after the start date in some rows, and before it in others, to cater for both in a single query you'd need to either amend my code, or wrap the function in another function in which the order of the operands and the multiplication by -1 is conditional on the finish date preceding the start date.

    It's important to understand that the function is subtractive, not inclusive, so, with your first example:

    ? WorkdaysDiffVariable(#2020-03-02#,#2020-03-13#,True,2,3,4,5,6)
     9

    The value returned is not inclusive of the start and end dates, but subtractive.

    It is designed this way so that it accords with the behaviour of the built in DateDiff function, which, with a simple example, we can see is also subtractive:

    ? DateDiff("d",#2020-01-13#,#2020-01-20#)
     7

    or simple date arithmetic:

    ? #2020-01-20# - #2020-01-13#
     7

    You are correct that this is governed by the line varFrom = varFrom + 1, so if that line is remarked out it will be inclusive:

    ? WorkdaysDiffVariable(#2020-03-02#,#2020-03-13#,True,2,3,4,5,6)
     10

    As regards your second example:

    ? WorkdaysDiffVariable(#2020-01-13#,#2020-03-13#,True,2,3,4,5,6)
     44

    This is returning the correct result, again because the function is subtractive, not inclusive.

    BTW when including date literals in a post it is best to use an internationally unambiguous format such as the ISO standard for date notation of YYYY-MM-DD, or such as 2 March 2020, rather than short date format.  While it is possible to see which regional date format is being used if the day of the month exceeds 12, this is not always the case, e.g. to me, and my system, 07/04/2020 is  7th April, not 4th July.

    Ken Sheridan, Stafford, England

    Monday, March 2, 2020 11:54 PM
  • Ken,

    Thanks for all your assistance and recommendations.

    My database is for maintaining summary information for various Projects.  In my case, I need to make the dates inclusive because the Start and Finish Dates (PFAStart & PFAFInish) actually include the first and last days.  These are both considered "work days".

    Regarding the -1 process, my scenarios are as follows...

    The Start Date (PFAStart) will always be prior to the Finish Date (PFAFinish).  The function you provided works great for this scenario.  What I am trying to do is utilize that same function when comparing the Finish Date (PFAFinish) to the Date() value.  In these scenarios, it is possible the Date() value would be later than the Finish Date (PFAFinish) (i.e., PFAFinish = 2020-02-27 and Date() = 2020-03-02).  In this case, I'm looking for the value to be -3 (inclusive).  For a different scenario, if PFAFinish = 2020-03-06 and Date() = 2020-03-02, this would yield 5 (inclusive).


    Thanks PU_RJF

    Tuesday, March 3, 2020 1:13 AM
  • As you appear to have a standard Monday to Friday working week we can cater for all your requirements by means of a  very simple function.  First we need to make a small amendment to the WorkdaysCalendar table:

    1.  Add a Boolean (Yes/No) column named Unworked.  In reality this means weekend days in your case.

    2.  Next update the new column by executing the following UPDATE query:

    UPDATE WorkdaysCalendar
    SET Unworked = True
    WHERE WeekDay(calDate,2) > 5;

    We now need a new query named qryWorkedDays.  Assuming that you have a PubHols table with the dates of public and concessionary holidays the query will be:

    SELECT calDate
    FROM WorkdaysCalendar LEFT JOIN PubHols
    ON WorkdaysCalendar.calDate = PubHols.Holdate
    WHERE NOT Unworked
    AND HolDate IS NULL;

    If you don't want to take public and concessionary holidays into account when computing the number of days it will be:

    SELECT calDate
    FROM WorkdaysCalendar
    WHERE NOT Unworked;

    We can now create the function:

    Public Function WorkDaysInRange(varStartDate As Variant, varEndDate As Variant) As Variant

        Dim strCriteria As String
        
        ' ensure both start and end dates are not Null
        If Not IsNull(varStartDate + varEndDate) Then
            strCriteria = "caldate BETWEEN #" & Format(varStartDate, "yyyy-mm-dd") & "# " & _
                "AND #" & Format(varEndDate, "yyyy-mm-dd") & "#"
                
            ' get number of days in range
            WorkDaysInRange = DCount("*", "qryWorkedDays", strCriteria)
            
            ' reverse sign of return value if end date precedes start date
            If varStartDate > varEndDate Then
                WorkDaysInRange = WorkDaysInRange * -1
            End If
        End If
        
    End Function

    A BETWEEN…..AND operation is inclusive, and will return the same number of days whether the start date precedes the end date or is later than the end date, so the function simply reverses the sign if the latter is the case.  We can see how it works in the immediate window with a two week range.  First if there are no public or concessionary holidays in the range and the start date precedes the end date:

    ? WorkDaysInRange(#2020-03-01#,#2020-03-14#)
     10

    If the end date precedes the start date:

    ? WorkDaysInRange(#2020-03-14#,#2020-03-01#)
    -10

    If we now make 2nd March 2020 a holiday (it's Labour Day in Western Australia apparently) by adding it as a row in the PubHols table, we can see what happens with the same two examples:

    ? WorkDaysInRange(#2020-03-01#,#2020-03-14#)
     9
    ? WorkDaysInRange(#2020-03-14#,#2020-03-01#)
    -9

    You can still use my WorkdaysDiffVariable function where greater flexibility is needed of course, but for your specific requirements the above should provide a simple and very efficient solution with minimal procedural code.

    Now that you have an auxiliary calendar table you can of course extend its usefulness by adding other columns.to suit any other requirements you might have now or in the future.  Also, in many date-oriented tasks there is a need to supply missing dates, e.g. in a room reservation database where the start and end dates of each reservation are recorded.  By joining the reservations table to an auxiliary calendar table you can, for instance, find whether a room is available on any specific date or dates.

    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Wednesday, March 4, 2020 12:51 PM
    Tuesday, March 3, 2020 5:39 PM
  • Ken,

    This all worked great!  Thanks for all of your help.


    Thanks PU_RJF

    Wednesday, March 4, 2020 12:59 PM