Creating a due date but exclude Bank holidays and weekends

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

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"sdg8481" wrote in message
...
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

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

"sdg8481" wrote:

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

On Feb 18, 6:00 pm, Ken Sheridan
wrote:
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);

Minor points:

It is normal to store the value for WEEKDAY(CalDate, 2) in the
Calendar table rather than calculate it on the fly.

It is posible for the above to be written using a correlated subquery,
allowing the 15 to be a parameter, however Jet is not well optimized
for such a query.

Jamie.

--