Results 1 to 8 of 8
  1. #1
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40

    Exclude Weekends and Holidays


    I have a database that acts like a task tracker, were certain processes are due daily, weekly, monthly, or on certain business days. I'm trying to find a code that can be executed through an append query which creates new records with their due dates pushed out. The problem I'm having is excluding weekends and holidays from the possible due dates.

    I know there may be code already out there on this but nothing clear enough for me to follow for my scenario.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you searched for Net Work Days yet?

  3. #3
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40
    The problem with newtworkdays is that it just counts the days between dates. I'm looking for a way to calculate what the next due date will be. (Excluding weekends and holidays of coarse)

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's a function I found years ago.
    Code:
    Public Function fAddWorkdays(dtStartDate As Date, _
                                 lngWorkDays As Long) _
                                 As Date
    'Adds the passed number of workdays to a passed date.  This code uses
    'fNetWorkdays(), so the assumptions of tblHoliday apply for this function
    'as well. Also note that if a ZERO is entered as the lngWorkDays parameter
    'the function will return the start date, if its a work day, or the first
    'workday PRIOR to the dtStartdate.
    '''''''''''''''''''''''''''''''''''''''''''
    'Author: Brent Spaulding
    'Version: 7
    'Date: Aug 8 2008
    '''''''''''''''''''''''''''''''''''''''''''
    'Revision History:
    'Ver    Description
    '?-4    Intial releases to UA in various threads and the Code Archive
    '5      Made the function cabable of handling negative work days to add
    '6      Corrected for a DIV by Zero error when 0 was entered as lngWorkdays
    '       as well as some buggy stuff with negative workdays
    '7      Formated date literals to corrected for possible errors with
    '       NON-US Regional Settings (Thanks to UA user fazered for notification of issue!).
    '..........................................
    
       Dim dtEndDate As Date
       Dim lngDays As Long
       Dim lngSaturdays As Long
       Dim lngOffset As Long
       Dim lngSundays As Long
    
       'First ... GUESS at the End Date you need to cover the workdays you are adding.
       'I ASSUME that the number of days that are added will always toss you into a
       'week end, then I add the number of work weeks to it the get the number of
       'saturdays and sundays.
       lngSaturdays = 1 + Abs(lngWorkDays) \ 5
       lngSundays = lngSaturdays
    
       dtEndDate = DateAdd("d", Sgn(lngWorkDays) * (Abs(lngWorkDays) + lngSaturdays + lngSundays), dtStartDate)
    
       'Next, as much as I hate to do it, loop until the fNetWorkdays equals the number
       'of days requested.
       Do Until lngWorkDays = lngDays
    
          'Count the number of work days between the ESTIMATED end date
          'and the start date
          lngDays = fNetWorkdays(dtStartDate, dtEndDate, False)
    
          'Make an adjustment to the end date
          If lngDays <> lngWorkDays Then
             lngOffset = lngWorkDays - lngDays
             dtEndDate = dtEndDate + lngOffset
          End If
    
       Loop
    
       'Determine the offset direction to adjust for weekends and holidays
       'the offset trys to bring the end date CLOSER to the start date.
       If lngWorkDays < 0 Then lngOffset = 1 Else lngOffset = -1
    
       'Make sure the end day is NOT a holiday and NOT a Saturday/Sunday
       Do Until DCount("*", "tblHolidays", "[HolidayDate]=#" & Format(dtEndDate, "yyyy-mm-dd") & "#" & _
                                           " And Weekday([HolidayDate],1) Not In (1,7)") = 0 _
                                           And Weekday(dtEndDate, vbMonday) < 6   '6th day of week if Mon is first day
          dtEndDate = dtEndDate + lngOffset
       Loop
    
       'Once we are out of the loop, the end date should be set to the correct date
       fAddWorkdays = dtEndDate
    
    End Function

  5. #5
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40
    Thanks RG

    I've seen this before and it will work for anything that is due daily.

    The problem now is won't work if something is due weekly (like every Friday) or on a specific business day each month.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I don't believe you mentioned that in your original post. There is code to convert a date to the Friday of that week. Are you trying to get one function to do all of those things?

  7. #7
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40
    Yes, I guess I'm looking for a way to accomplish each of those scenarios. Looks like I might need a different code for each type of task frequency.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You should be able to modify Brent Spaulding's code to accomplish that. Just add another argument and fix the date at the end.

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

Similar Threads

  1. Replies: 1
    Last Post: 04-20-2015, 04:17 PM
  2. Replies: 8
    Last Post: 10-20-2012, 11:25 AM
  3. quarterly, excludes weekends and holidays
    By madagaluna in forum Queries
    Replies: 2
    Last Post: 04-01-2011, 12:56 PM
  4. Replies: 0
    Last Post: 04-01-2011, 09:12 AM
  5. How to analyze holidays/weekends
    By Galio83 in forum Queries
    Replies: 0
    Last Post: 03-31-2011, 12:27 PM

Posting Permissions

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