Business Days Between Two Dates?

[(PeteCresswell)]

Business days between two dates has got to involve a tblHoliday.

Right now I've got such a table - mostly populated with bank
holidays and I compute biz days between two dates by iterating
through the days one-by-one: if it's a Saturday or Sunday it's
not a biz day.... otherwise I do a table lookup - no hit, then
it's a biz day.

But this is *really* slow for processing a record set of, say,
2000 records.

I'm thinking a faster approach would involve re-creating a table
of business days from some begin date to some end date each time
tblHolidays is updated and then doing some SQL magic to bang a
recordset up against that table and somehow bulk-calculate, for
instance, the number of business days between a bond's maturity
date and today's date... or whether or not a bond matures within
60 business days.

Has anybody come up with such an approach?

 

[Tom van Stiphout]

On Fri, 14 May 2010 19:14:23 -0400, "(PeteCresswell)" <[email protected]>
wrote:

How slow? FMS Inc has a BusinessDayAdd and BusinessDayDiff loosely
based on the same algorithm, and I have never had a problem. Probably
never had to run it over several years.

Do bonds really care about business days? That surprises me.

Yes, you could pre-populate a table overnight every night. Or perhaps
even populate a table with offsets on a more permanent basis. Go for
it! Maybe you even want to contribute it back to the greater good.
Just be careful about the oleaut32 bug reported here:
http://support.microsoft.com/kb/200299

-Tom.
Microsoft Access MVP

 

[Bob Quintal]

Business days between two dates has got to involve a tblHoliday.

Right now I've got such a table - mostly populated with bank
holidays and I compute biz days between two dates by iterating
through the days one-by-one: if it's a Saturday or Sunday it's
not a biz day.... otherwise I do a table lookup - no hit, then
it's a biz day.

But this is *really* slow for processing a record set of, say,
2000 records.

I'm thinking a faster approach would involve re-creating a table
of business days from some begin date to some end date each time
tblHolidays is updated and then doing some SQL magic to bang a
recordset up against that table and somehow bulk-calculate, for
instance, the number of business days between a bond's maturity
date and today's date... or whether or not a bond matures within
60 business days.

Has anybody come up with such an approach?

I calculate the business interval as follows.
weeks = datediff("ww",dtFrom,dtTo,vbmonday)
then add the days in the zeroeth week, subtract the days after dtTo
from the final week, and subtract the dCount() of the number of days
in tblHolidays where holiday between dtFrom and dtTo

this means one dCount per calculation, as opposed to, if I understand
you, a dLookup for each day in the interval.

 

[Arvin Meyer [MVP]]

The case of bond maturity can run almosrt instantly since it doesn't need to
run either of those functions or the one at:

http://www.datastrat.com/Code/GetBusinessDay.txt

for every row. For bond maturity in say 60 business days, use the above link
to add 60 days to the current day, then store that date in a global
variable. In a query, use a function to compare that variable to the Bond
Maturity Date. Since it does need to run the function thousands of times,
it's very fast that way.

 

[(PeteCresswell)]

Per KenSheridan via AccessMonster.com:

I use the following function

That's exactly what I was looking for.

With your permission, I will adapt it to my needs.

One question: Why are you using vbMonday with the Weekday() calls
and then using conSATURDAY and conSUNDAY instead of dropping the
vbMonday and just using vbSaturday/vbSunday?

Maybe I'll have it figured out by the time you read this... but
maybe not...