none
Exclude Weekends RRS feed

  • Question

  • Hi all,

    I have an Access 2013 database. In a table is training courses with a field that holds a field "Duration_Days". The user enters for example, 10 days in this field.

    When the training is scheduled, the start date is entered, e.g. 12-01-2018. The code retieves the "Duration_Days" from the table [10 in this case].

    The schedule form now needs to calculate the end date, BUT weekends must be excluded.

    For example:

    Start Date: 12-01-2018

    Duration: 10 days

    End Date: 25-01-2018 (Excluding Weekends)

    Is there a quick or short way of doing this?

    Much appreciated

    Deon

    Monday, January 15, 2018 2:04 PM

Answers

  • 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 the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the us of functions for adding/subtracting working days, amongst other things, allowing for weekends and public/concessionary holidays.  It caters both for use in a single country with a single set of public holidays, or for use in multiple countries with differing public holidays, as in the constituent countries of the UK.

    Ken Sheridan, Stafford, England

    • Marked as answer by Deon SA Thursday, January 25, 2018 1:55 PM
    Monday, January 15, 2018 3:54 PM

All replies

  • Check this out: http://www.utteraccess.com/forum/fNetWorkdays-fAddWorkDay-t1349593.html&hl=workdays


    -Tom. Microsoft Access MVP

    Monday, January 15, 2018 2:10 PM
  • Is there a quick or short way of doing this?

    Hi Deon,

    In a couple of applications I am quite succesful using a table with two fields: Calenderday and Day_seqnr.

    Calenderday lists all days of the year (and next years). Day_seqnr is a sequencenumber of the "working" days. Non-working days (weekend, holidays, vacation) get the same sequencenumber as the day before. Calenderday is PK, (Day_seqnr, Calenderday) has an alternate key.

    This table can easily be modified to account for exceptional 'working' or 'not-working' days.

    Searching the end_date:

    Search the record with the StartDate, pick up the Day_seqnr.
    Search the record with Day_seqnr + Duration, pick up the Calenderday.

    This Calenderder is the "until to" day, Calenderday - 1 is then "until included" day.

    This table can easily be extended to far more functionality or purposes.

    Imb.

    Monday, January 15, 2018 2:46 PM
  • 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 the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the us of functions for adding/subtracting working days, amongst other things, allowing for weekends and public/concessionary holidays.  It caters both for use in a single country with a single set of public holidays, or for use in multiple countries with differing public holidays, as in the constituent countries of the UK.

    Ken Sheridan, Stafford, England

    • Marked as answer by Deon SA Thursday, January 25, 2018 1:55 PM
    Monday, January 15, 2018 3:54 PM
  • Hello Deon,

    I would suggest you custom a function to return the end date. Then you could use the function instead of built-in DateAdd button to return the date.

    You could create a standard module and use below code in the module.

    Option Compare Database
    
    Public Function CustomDateAdd(startDate As Date, days As Integer)
    endDate = startDate
    For i = 1 To days
    'check if end date is weekends before adding
    'if you could make sure start Date is not weekends
    'you could cancel checking before adding
    If Weekday(endDate, 2) >= 6 Then
    endDate = DateAdd("d", 7 - Weekday(endDate, 2) + 1, endDate)
    End If
    
    endDate = DateAdd("d", 1, endDate)
    
    'check if end date is weekends after adding
    If Weekday(endDate, 2) >= 6 Then
    endDate = DateAdd("d", 7 - Weekday(endDate, 2) + 1, endDate)
    End If
    
    Next i
    CustomDateAdd = endDate
    End Function

    After saving the module, you could try to use query like below to return the end date.

    SELECT Start_Date,Duration_Days,CustomDateAdd(Start_Date,Duration_Days) as End_Date from WeekTable

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 16, 2018 5:13 AM