I have a function that returns the date, X number of work days after a given
date.

3 work days after 2/5/2009 would be 2/10/2009

The part of the function that handles week ends works and most cases of
holidays. my problem is when the calculated date ends on the 2nd day of a 2
day holiday before a week end. For example we have a holiday on Dec 24 and
25th, if I need 2 workdays after 12/23/2009, the function should return
12/29/2009, it returns 12/28/2009. How can I quickly identify this
condition.

' Skip over starting on holidays (This Works)
If rsHoliday.RecordCount <> 0 Then
rsHoliday.FindFirst "[hDate]=#" & vDateStart & "#"
While rsHoliday.NoMatch = False
vDateStart = DateAdd("d", 1, vDateStart)
rsHoliday.FindFirst "[hDate]=#" & vDateStart & "#"
Wend
End If

(This Works)
' Add Full weeks + Remainging days + starting on Sunday adjustment + ending
Saturday adjustment

vEndDate = DateAdd("d", _
(iDaysToAdd \ 5) * 7 + (iDaysToAdd Mod 5) + _
IIf(WeekDay(vDateStart) = vbSunday, 1, 0) + _
IIf(WeekDay(vDateStart) - vbMonday + (iDaysToAdd Mod 5) >= 5, 2,
0), _
vDateStart)

' Count of holidays falling between start date and end date
If rsHoliday.RecordCount <> 0 Then
iHolidays = Nz(eCount("[hDate]", "tblMschHoliday", _
"[hDate] Between #" & vDateStart & "# And #" & vEndDate &
"#"), 0)
End If

' Check for ending on multi day holidays and holidays before weekends
If iHolidays <> 0 Then

' Add extra days for holiday
(Error if 2nd day of 2 day holiday puts date on Sunday)
(Looks like the ending on a Sunday adjustment, don't know how to detect)
vEndDate = DateAdd("d", iHolidays, vEndDate)

' Skip over ending on a holiday (This works)
rsHoliday.FindFirst "[hDate]=#" & vEndDate & "#"
While rsHoliday.NoMatch = False
vEndDate = DateAdd("d", 1, vEndDate)
rsHoliday.FindFirst "[hDate]=#" & vEndDate & "#"
Wend

' Weekend following holiday
If WeekDay(vEndDate) = vbSaturday Then
vEndDate = DateAdd("d", 2, vEndDate)
Else
If WeekDay(vEndDate) = vbSunday Then
vEndDate = DateAdd("d", 1, vEndDate)
End If
End If
End If

On Apr 16, 11:06*am, "paii, Ron" <n... (AT) no (DOT) com> wrote:
Aren't you going to need a "holidays" table with those holidays? I
assume these are company observed holidays, like Good Friday, the
Friday before July 4th (in years where that occurs), etc.

"Dave Moore" <david.moore (AT) charter (DOT) net> wrote

On Apr 16, 11:06 am, "paii, Ron" <n... (AT) no (DOT) com> wrote:
rsHoliday is the open recordset of holidays.

i had to write a function a couple years ago, to add "x" working days to a
given date, skipping weekend days and dates that are contained in a Holidays
table. it works for me, so maybe you'll find it useful:

Public Function calcDate(ByVal dat As Date, ByVal intAdd As Integer) As Date

Dim i As Integer, bump As Boolean, str As String

dat = CDate(Fix(dat))
i = 1

For i = 1 To intAdd
dat = dat + 1
Do
bump = False
If DCount(1, "tbl00Holidays", "hDate = #" & dat & "#") > 0 Then
dat = dat + 1
bump = True
End If

str = Format(dat, "ddd")
If str = "Sat" Then
dat = dat + 2
bump = True
ElseIf str = "sun" Then
dat = dat + 1
bump = True
End If
Loop Until bump = False
Next

calcDate = dat

End Function

fyi, i use the CDate(Fix(dat)) expression at the beginning of the procedure
because in my working database, the date provided in the argument includes a
specific time, but i needed to return a date with no time (that's a default
of midnight, of course, and strictly speaking).

hth


"paii, Ron" <none (AT) no (DOT) com> wrote

Thank you for your posting.
I have a similar function.
The new one is attempting to improve the performance by calculating instead
of looking at the holiday table with every date between start and count.

"tina" <nospam (AT) address (DOT) com> wrote