Exclude Weekends

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

Answer 1

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

---

-Tom. Microsoft Access MVP

Answer 2

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.

Answer 3

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

Answer 4

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 [e-mail].