If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Business Days Between Two Dates?
"(PeteCresswell)" wrote in
: 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. |
Thread Tools | |
Display Modes | |
|
|