Creating a due date but exclude Bank holidays and weekends

[sdg8481]

Hi,

I'm trying to create a Due Date based on a date plus a certain number of
days, however i want to count only business days and also to exclude
pre-entered Bank Holidays.

For example, if;
[Start Date] = 01/04/2006 and [Days to Add] = 15, i would want to return a
date that are 15 working days (mon-Fri) ahead of the 01/04/06, now simply
this would be just start date +21 (because there are 3 weekends, therefore
15+6). However, i also want to exclude some pre-enter holidays. Ideally this
would be best placed in another table.

Hope this makes sense any ideas.

Thanks

 

[Douglas J. Steele]

Check http://www.mvps.org/access/datetime/date0012.htm at "The Access Web",
or my September, 2004 "Access Answers" column in Pinnacle Publication's
"Smart Access". You can download the column (and sample database) for free
at http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Ken Sheridan]

There are many VBA solutions to this sort of thing (I've actually written one
which accepts a country argument so that it can cater for the different
public holidays in the constituent countries of the UK and the Republic of
Ireland). But it can also be done with a query by having an auxiliary
Calendar table in the database. This is simply a table with a column CalDate
with all dates over a range (10 years say) and is easily created by serially
filling down a column in Excel and importing it into Access as a table. With
the public holidays in a table PubHols the query would go like this:

SELECT MAX(CalDate) AS DueDate
FROM
(SELECT TOP 15 *
FROM Calendar LEFT JOIN PubHols
ON Calendar.CalDate = PubHols.HolDate
WHERE HolDate IS NULL
AND CalDate > #04/01/06#
AND WEEKDAY(CalDate,2) < 6);

The SELECT TOP option doesn't accept parameters, however, so it would be
necessary to build the SQL statement in code to vary the number of days.

A Calendar table is not restricted to this of course, but can be used for
many date related operations. Where it comes in particularly useful is when
its necessary to return dates in a query's result set where those dates are
not present in the main tables, e.g. to find vacant rooms for particular
dates in a room booking database where the start and end dates of room
occupancies are recorded in the reservations table. By adding other columns
to the Calendar table very efficient set operations can be undertaken. A
Boolean PubHol column in the table for instance would remove the need for the
outer join with a PubHols table in the above query.

Ken Sheridan
Stafford, England