Date/time algorithm for holidays (brain teaser?)

Hi,

Both approachs are acceptable. The last one is iterative, you just loop on

every day, and count it as zero or one, accordingly, until the count reach

81. Since that is a small number, that can be relatively fast. The first

aproach may seems a little bit better, it looks like a special case of

finding a zero of a function (ie, you are short of 81 days, then short of

20, then short of 3, ....) and may be faster if your number of day is

"large" but then, it is more complex to program since you may end up to ask

"many" times if a given day is a working day or not... You probably need to

be able to compute the number of working day in the "new slice" only (ie, in

the next 20 day added on top of the first next 81, how many working days are

there in the interval?).

Definitively, having a database at hand, I will suggest a third solution, a

table. Get a table with the next 10 years dates, say from 2000 to 2010, and

"rank" each of those dates as the number of working day since 2000 Jan

First. You compute it once, using the first method, but keep the result in

the table. Once that is preliminary job is done, it is just a matter of

making a lookup (or inner join):

DMin("DateValue", "YourNewTableName" , "WorkingDaySince2000>=" & _

81 + DLookup("WorkingDaySince2000", "YourNewTableName",

"DateValue=Date( ) "))

ie, the lookup find that today is the xxx working day since 2000.01.01 and

the DMin returns the first day where 81 working day will have been spend

from now (you can change the >= by an = and use DMax to give the "benefit"

of a weekend or of an Holiday, see what fit best for you)

Since that alrogithm is based on a table, keep the algorithm that initialiy

filled the table at hand so you may expand the table past 2010.... I will

also index each or the two fields in that new table.

Hoping it may help,

Vanderghast, Access MVP

[Hope you don't mind, I can't even read it myself... HTML]

It may not be for you but it's got me thinking.... I am trying to forward

project a job finish date as follows:

Current date + 81 days (estimated time it takes to do job) = job finish

date.

It seems OK to do with the DateAdd function, yes. However, I have a holiday

table that has dates which need to be included:

ID Holiday/break Starts Stops Recurs Every

===============================================================

1 Saturday 25-Nov-2000 1 Week(s)

2 Sunday 26-Nov-2000 1 Week(s)

3 Christmas Day 25-Dec-2000 1 Year(s)

4 Company Holiday 01-Jul-2000 10-Jul-2000 1 Years(s)

5 New Years Day 01-Jan-2000 1 Year(s)

6 Company Special 15-Feb-2000 1 Month(s)

7 One Off Holiday 18-Jun-2000 0 Day(s)

7 One Off Holiday 21-Sep-2000 0 Day(s)

...

.

etc

I am thinking of ways to approach this. Possible ways I have thought of are:

Moving through the holiday table accumulating days that fall within the 81

days. E.g. say 20, then adding this to the 81 to give 101. As you can see

though, this would mean the excess 20 days would have to be checked for

holiday's and then those etc, so it would involve repeating iterations.

Going though the 81 days one by one, seeing if it was a holiday and if so

adding it to the future date, though again this would mean the added bit

would have to be checked for holidays, so iterations again.

I am hoping someone has done something like this before and can advise...

Any comments appreciated.

Thanks

Mark