Results 1 to 3 of 3

Determine the number of Working/Business days between 2 Dates

  1. #1
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,124

    Determine the number of Working/Business days between 2 Dates

    REVISION: This now allows for a choice of week end days (Default is Saturday and Sunday).


    This function determines the number of business/working days between 2 dates; it ignores weekends (Saturday and Sunday) and accommodates Holidays, if you:
    'Create a Table named tHoliday with a single DATE/TIME Field named [HolidayDate].
    'Populate this Table with any and all work related Holidays.

    'Inputs: StartDate, and EndDate, Optional WeekendDays
    '
    'DEFAULT WeekendDays is "1,7" which represent Saturday and Sunday
    ' based on 1 = sunday, 2 = monday, 3 = tuesday......7 = saturday
    '
    'Returns: an Integer representing the number of Workdays/Business Days



    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : fWorkingDays
    ' Author    : --adapted from a Bytes article  by A Dezii
    ' Date      : 16/7/2015
    ' REVISED:5/4/2016
    ' Purpose   :**UPDATED TO ALLOW CHOICE OF WEEKEND DAYS
    ' A  Function to count the number of Workdays between 2 dates, that allows user to select weekend days
    'and  respects holidays
    'Count of workings days returned does not include WeekEndDays nor Federal/Stat Holidays.
    '
    'Create a Table named tHoliday with a single DATE/TIME Field named [HolidayDate].
    'Populate this Table with any and all work related Holidays.
    '
    'Inputs: StartDate, and EndDate, Optional WeekendDays
    '
    'DEFAULT  WeekendDays is "1,7" which represent Saturday and Sunday
    '   based on 1 = sunday, 2 = monday, 3 = tuesday......7 = saturday
    '
    'Returns: an Integer representing the number of Workdays/Business Days
    '
    'There are debug.print statements (commented) that you can uncomment and follow the logic.
    '---------------------------------------------------------------------------------------
    '
    Public Function fWorkingDays(dteStartDate As Date, dteEndDate As Date, Optional WeekendDays As String = "1,7") As Integer
              Dim intCount As Integer
              Dim wkdays As String
    10    wkdays = "1234567"    'normal week days
    20    intCount = 0
    30    'Debug.Print "Weekend days " & WeekendDays
    40    On Error GoTo fWorkingDays_Error
    
              'process parameter
    50    If Not WeekendDays Like "[1-9,][1-9,][1-9,]" Then
    60     ' Debug.Print "**error in weekenddays   " & WeekendDays
            Err.Raise 2000, , "Bad value in WeekendDays - must be x,x  where x is number 1 thru 7" _
                & " representing the week end days      1 = sunday 2 = monday 3 = tuesday......7 = saturday"
    70    Else
    80      wkdays = Replace(wkdays, Left(WeekendDays, 1), "")
    90      wkdays = Replace(wkdays, Right(WeekendDays, 1), "")
    100     If Right(wkdays, 1) = "," Then wkdays = Mid(wkdays, 1, Len(wkdays) - 1)
    110     'Debug.Print "using weekdays " & wkdays
    120     End If
    130
    
    
    140   Do While dteStartDate <= dteEndDate
    
    150     If InStr(WeekendDays, WeekDay(dteStartDate)) > 0 Then  ' If a WeekEnd day do nothing
    160         'Debug.Print "Testing days " & dteStartDate & "  " & WeekDay(dteStartDate) & "  is a weekendday"
    170     Else
    
    180        ' Debug.Print "Testing days " & dteStartDate & "  " & WeekDay(dteStartDate) & "  is a weekday"
                '  is it a Holiday as posted in tblHolidays?
    190         If DCount("*", "tHoliday", "HolidayDate  = #" & dteStartDate & "#") < 1 Then     'NOT Holiday
    200             intCount = intCount + 1   ' so increment if weekday and not a holiday
    210         Else
    220             'Debug.Print "Testing weekdays " & WeekDay(dteStartDate) & "  is a weekendday and a holiday " & dteStartDate
    230         End If
    240     End If
    250     dteStartDate = dteStartDate + 1
    260   Loop
    270   fWorkingDays = intCount
    
    280   On Error GoTo 0
    290   Exit Function
    
    fWorkingDays_Error:
    
    300   MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure fWorkingDays of Module AWF_Related"
    End Function
    Here is a test routine showing a different set of Weekend days.

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : testfWorkingDays
    ' Author    : mellon
    ' Date      : 4/05/2016
    ' Purpose   :test routine for fWorkingDays
    'Uses table tHoliday with this structure
    'field_name    data_type
    '   HolidayDate       Date
    '   id                Long
    '
    ' For this test tHoliday is
    'HolidayDate id
    '25/03/2016  1 '...........Good Friday
    '28/03/2016  2 '...........Easter Monday
    '17/03/2016  3 '...........Made up Holidy for testing
    '---------------------------------------------------------------------------------------
    '
    Sub testfWorkingDays()
    10       On Error GoTo testfWorkingDays_Error
    
    20    Debug.Print "Number of Working days in March 2016 --> " & fWorkingDays(#3/1/2016#, #3/31/2016#, "5,7")
    
    30       On Error GoTo 0
    40       Exit Sub
    
    testfWorkingDays_Error:
    
    50        MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure testWorkdays"
    End Sub
    Result:
    Number of Working days in March 2016 --> 20
    Last edited by orange; 06-08-2016 at 02:14 PM. Reason: spelling

  2. #2
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    212
    I recommend people check out this code for calculating the total number of business days between two dates.

    https://www.accessforums.net/showthread.php?t=77948

    It is 9 lines of code. I also provided a view of the calculator used in addition to the remainder of code corresponding to each text box.

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,408
    Apples and oranges (no pun intended); your code doesn't account for holidays.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-29-2015, 12:40 PM
  2. difference in dates: Number of days
    By akapag22 in forum Queries
    Replies: 2
    Last Post: 06-23-2015, 08:39 PM
  3. Replies: 5
    Last Post: 04-29-2014, 03:49 AM
  4. Replies: 3
    Last Post: 04-03-2013, 05:53 PM
  5. Replies: 4
    Last Post: 08-27-2009, 01:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums