Date/time algorithm for holidays (brain teaser?)
Author Message
Date/time algorithm for holidays (brain teaser?)

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-July-2000            10-July-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:

1) 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.

2) 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...

Thanks

Mark

Fri, 16 May 2003 03:00:00 GMT
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...

Thanks

Mark

Fri, 16 May 2003 03:00:00 GMT
Date/time algorithm for holidays (brain teaser?)

[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:
1.. 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.
2.. 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...

Thanks

Mark

Fri, 16 May 2003 19:17:02 GMT

 Page 1 of 1 [ 3 post ]

Relevant Pages