Help with calculating date excluding holidays

[Guest]

Records BD TargetDate
1 10 10/22/07
2 5 10/29/07
3 1 10/30/07


I need to calculate first date from today plus the number of Business Days
(BD), in this case today plus 10. If any of the BD numbers changes I want to
recalculate all the dates from the changed BD number on. The calculation must
include business days only, i.e. weekends and holidays are excluded as
follows:

Record 1) TargetDate = Date()+10 = 10/22/07
Record 2) TargetDate = 10/22/07 (TargetDate for record 1) + 5 = 10/29/07
Record 3) TargetDate = 10/29/07 (TargetDate for record 2) + 1 = 10/30/07

The above dates are calculated excluding the weekends. In case there was
thanksgiving or xmas that would need to be excluded as well.

If the user wanted to change the BD for any reason, for example changed
record 2) from 5 to 6 then:
Record 2) TargetDate = 10/22/07 (TargetDate for record 1) + 6 = 10/30/07
Record 3) TargetDate = 10/30/07 (TargetDate for record 2) + 1 = 10/31/07
And so on. I am using a datasheet form.
Thanks
Al

 

[Guest]

Here is a function that does just that. You will need a holiday table that
has each holiday in it. You can create one with the names in the code or if
you choose to use different names, change the code:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

 

[Guest]

Thank you Klatuu, I will try it and let you know.
Al

Here is a function that does just that. You will need a holiday table that
has each holiday in it. You can create one with the names in the code or if
you choose to use different names, change the code:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP

Records BD TargetDate
1 10 10/22/07
2 5 10/29/07
3 1 10/30/07


I need to calculate first date from today plus the number of Business Days
(BD), in this case today plus 10. If any of the BD numbers changes I want to
recalculate all the dates from the changed BD number on. The calculation must
include business days only, i.e. weekends and holidays are excluded as
follows:

Record 1) TargetDate = Date()+10 = 10/22/07
Record 2) TargetDate = 10/22/07 (TargetDate for record 1) + 5 = 10/29/07
Record 3) TargetDate = 10/29/07 (TargetDate for record 2) + 1 = 10/30/07

The above dates are calculated excluding the weekends. In case there was
thanksgiving or xmas that would need to be excluded as well.

If the user wanted to change the BD for any reason, for example changed
record 2) from 5 to 6 then:
Record 2) TargetDate = 10/22/07 (TargetDate for record 1) + 6 = 10/30/07
Record 3) TargetDate = 10/30/07 (TargetDate for record 2) + 1 = 10/31/07
And so on. I am using a datasheet form.
Thanks
Al

 

[Guest]

Hi Klatuu,
I tried it and it works nice. However, I still need to recalculate all the
dates from the changed BD number on. any idea?
thanks
Al

Here is a function that does just that. You will need a holiday table that
has each holiday in it. You can create one with the names in the code or if
you choose to use different names, change the code:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP

Records BD TargetDate
1 10 10/22/07
2 5 10/29/07
3 1 10/30/07


I need to calculate first date from today plus the number of Business Days
(BD), in this case today plus 10. If any of the BD numbers changes I want to
recalculate all the dates from the changed BD number on. The calculation must
include business days only, i.e. weekends and holidays are excluded as
follows:

Record 1) TargetDate = Date()+10 = 10/22/07
Record 2) TargetDate = 10/22/07 (TargetDate for record 1) + 5 = 10/29/07
Record 3) TargetDate = 10/29/07 (TargetDate for record 2) + 1 = 10/30/07

The above dates are calculated excluding the weekends. In case there was
thanksgiving or xmas that would need to be excluded as well.

If the user wanted to change the BD for any reason, for example changed
record 2) from 5 to 6 then:
Record 2) TargetDate = 10/22/07 (TargetDate for record 1) + 6 = 10/30/07
Record 3) TargetDate = 10/30/07 (TargetDate for record 2) + 1 = 10/31/07
And so on. I am using a datasheet form.
Thanks
Al