DOB calculation in Table

[JudyK]

I have a [DOB] field with birth dates. I have another field [DOBExp] where I want to automatically calculate at date 15 days out, but exclude weekends and holidays. I have a separate table listing the holidays.

Would like a formula to use in my first table for this. Have no clue how to do this.

Any help you can provide will be greatly appreciated.

Judy

 

[shadow9449]

Judy:

You wouldn't store a calculated field as a field in the table. That would be redundant in this case. Rather, calculate it when you need it (report, form, etc).

To do the calculation, I would write a little function. First, if not for the stipulation that it has to exclude holidays, you can simply use the Dateadd function (Dateadd ("d", 15, [DOB]). But, since you want to exclude holidays, do something like this pseudo-code:

dim i as integer
dim mydate as date
    
mydate = dob

i = 0
do while i < 15
  mydate = Dateadd ("d", 1, mydate)
  if (Dateadd ("d", 1, mydate) is not in the holiday list and not a weekend then
    i = i + 1
  end if
loop

Essentially, all this does is take mydate, start it as equalling dob and then advance it by one repeatedly. If testing of the date after adding advancing it shows that it's not a weekend or holiday, then add 1 to i. Once we've added 1 to i 15 times, that means we've advanced by 15 non holiday/weekend days which is what you wanted to accomplish.

I hope this helps. Feel free to ask.

SHADOW