business days calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Hi,

This sort of think is possible but it involes two processes( One after
another).

1) Take existing date add 15 days, if answer is sat, sub 1 if sun sub 2.

Then

2) In order to find out if this date is a hoilday you will have to look this
up on a table that you will have to write showing all holidays for the
comming years.

The code for the module for part 1 is as follows to call it

NewField = Days15addexPUBHols([CLDTREPORT])


Public Function Days15addexPUBHols(initialDate As Date) As Date

Dim datNewDate As Date

datNewDate = DateAdd("d", 15, initialDate) '(Part 1)

Select Case Format(datNewDate, "ddd") ' Evaluate Day of the week

Case "Sat"
datNewDate = DateAdd("d", -1, datNewDate) 'Gets friday
Case "Sun"
datNewDate = DateAdd("d", -2, datNewDate) 'Gets Friday
Case Else ' Other values.
' datnewdate is between mon to fri ok as is
End Select

Days15addexPUBHols = datNewDate

' Now do your lookup it see if your new date is a public holiday (Part
2)

End Function
 
this is nice and should do the trick. i appreciate your code!
Thanks

swas said:
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
Click to expand...
Click to expand...
 
Back
Top