Business Days Between Two Dates?

On Fri, 14 May 2010 19:14:23 -0400, "(PeteCresswell)"
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 he
http://support.microsoft.com/kb/200299

-Tom.
Microsoft Access MVP


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?

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Tom van Stiphout" wrote in message
...
On Fri, 14 May 2010 19:14:23 -0400, "(PeteCresswell)"
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 he
http://support.microsoft.com/kb/200299

-Tom.
Microsoft Access MVP


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?