Hi,
i´m from Portugal and sorry my english.
I saw you examples from public database
and it´s really good stuff and thanks for that!
i ´m tryng to get a date and this date if it´s holiday or weekend moves to the next day. i ´m tryng to adjust your functions from your examples and never get the right result.
Example (1/jan is holiday)
Colum A(date Original) | Column B (date 1st Working day)
31 Dez | 31 Dez
1/jan | 4/jan (because is holiday)
4/Jan | 4/Jan
.....
i ´ve try this one. but give always a diferente result!!
Public Function AddWorkDays(StartDate As Date, NumDays As Long) As Date
'....................................................................
' Name: AddWorkDays
' Inputs: StartDate As Date
' NumDays As Long
' Returns: Date
' Note that this function has been modified to account for holidays.
' It requires a table named tblHolidays with a field named HolidayDate.
'....................................................................
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim dtmCurr As Date
Dim lngCount As Long
On Error GoTo ErrHandler
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblHolidays", dbOpenSnapshot)
lngCount = 0
dtmCurr = StartDate
Do While lngCount < NumDays
dtmCurr = dtmCurr + 1
If Weekday(dtmCurr, vbMonday) < 6 Then
rst.FindFirst "[HolidayDate] = #" & Format(dtmCurr, "mm/dd/yyyy") & "#"
If rst.NoMatch Then
lngCount = lngCount + 1
End If
End If
Loop
AddWorkDays = dtmCurr
ExitHandler:
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Function
ErrHandler:
' Optionally display error message
' MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Function
Thanks.