Rob,
Here is a function I use to determine the next or previous working day for a
given date, depending on 5, 6 or 7 working days. If it is always 5 working
days, just check fot what day it is, if Sat subtract 1 day, if Sun subtract 2
days, all others are OK.
You would need some sort of calender to know if a public holiday exists to
add working days to a given date. Alternatively you can add calendar days
(Not working days) and then ensure on a working day with the function.
I use another function to add normal working days (5, 6 or 7 day weeks) but
this accesses tables for some other functionality.
Hope this helps.
swas
Public Function NextWorkingDay(FromThisDate As Date, WorkingDays As Integer,
ForwardBackward As Boolean) As Date
'Returns the NextWorkingDay FromThisDate depending on how many WorkingDays
per week worked (5, 6, 7).
'Forward (True) will supply the working day following FromThisDate, Backward
(False) for pre FromThisDate
'Brute crunch done against truth table logic
Dim ThisWeekday As String
On Error GoTo NextWorkingDay_Error
NextWorkingDay = FromThisDate 'Default all ok
If WorkingDays < 5 Or WorkingDays > 7 Then
'Out of range
GoTo NextWorkingDay_Exit
End If
ThisWeekday = Format(FromThisDate, "ddd")
If WorkingDays = 5 Then
'Start with 5 days per week
If ForwardBackward Then
'Forward
If ThisWeekday = "Sat" Then
NextWorkingDay = DateAdd("d", 2, FromThisDate)
ElseIf ThisWeekday = "Sun" Then
NextWorkingDay = DateAdd("d", 1, FromThisDate)
End If
Else
'Backward
If ThisWeekday = "Sat" Then
NextWorkingDay = DateAdd("d", -1, FromThisDate)
ElseIf ThisWeekday = "Sun" Then
NextWorkingDay = DateAdd("d", -2, FromThisDate)
End If
End If
ElseIf WorkingDays = 6 Then
'Now if 6 days per week
If ForwardBackward Then
'Forward
If ThisWeekday = "Sun" Then
NextWorkingDay = DateAdd("d", 1, FromThisDate)
End If
Else
'Backward
If ThisWeekday = "Sun" Then
NextWorkingDay = DateAdd("d", -1, FromThisDate)
End If
End If
End If
NextWorkingDay_Exit:
Exit Function
NextWorkingDay_Error:
MsgBox Err.Number & Err.Description, , "NextWorkingDay"
Resume NextWorkingDay_Exit
End Function
ERMAC said:
i have a user input field [CLDTREPORT] and i would like to calculate 15
business days from this date and display it in a new field. to complicate
things, if the resulting calc falls on a Sat or Sun or holiday, the
calculation needs to display the prior business day. Has anyone encountered
this before and is there "easy" code for this?
Thanks,
Rob