exclude holidays

[Bay0519]

Hi,

Could someone help me on how to create a function for exclude schedule date for holiday?

currently here is my code and it seems not working properly.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
  
On Error GoTo AddWorkDays_Error
  
If DaysToAdd <0 Then
    lngAdd = -1
Else
    lngAdd = 1
End If
  
AddWorkDays = OriginalDate
  
Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
    If IsNull(DLookup("[holidaydate]", "tblHolidays", "[holidaydate] = #" & _
        AddWorkDays & "#")) Then
        lngDayCount = lngDayCount + lngAdd
    End If
End If
Loop
  
AddWorkDays_Exit:
On Error GoTo 0
    Exit Function
  
AddWorkDays_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure AddWorkDays of Module modDateFunctions", vbExclamation, conMsgTitle
    GoTo AddWorkDays_Exit
  
End Function

and here is how I call the function

 Dim test As Date
test = rst2.Fields("FinalMfgOutDate")
outdate = AddWorkDays(test, 0)

 If days = -1 Then
  Indate = outdate
 Else
  Indate = AddWorkDays(outdate, -days)
 End If

basically this project is to calculate in and out date. Out date is when we're suppose to finish the job and using days, I need to calculate the in date.
Could someone help me please?
Thank you...

[NeoPa]

This is totally non-trivial.

You'll find out why, and even a workable solution, in How To: Calculate Business Days - Pure SQL Approach.

Good luck. I understand this is quite advanced work.