Help with calculating date excluding holidays

A

Al

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
 
K

Klatuu

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
 
A

Al

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

Klatuu said:
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


Al said:
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
Click to expand...
Click to expand...
 
A

Al

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

Klatuu said:
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


Al said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help to create and Insert New Target Date record in Subform 0
count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Help with determining deadlines 8
Count the remaining weekdays in a data range excluding holidays and start/end dates 5
Reverse Index Match 2
Calculating interest due on late loan payments 1
Please help with datatype mismatch 8
Avoiding Leads, using SVT, and Linking Tasks correctly 0

Top