Calculating Dates in an Access database

[catz]

I'm looking for a formula, expression or event procedure that will allow me to calculate a future date based on a given number of weeks. (The user will select the start date and the desired number of weeks). The weeks must be Monday through Friday only and exclude certain holidays that will be entered in the database.

Can someone help?

[Sidewinder]

Sometimes it's better to plow through this one step at a time. VBA has date handling functions which can be useful. The DateAdd function allows you to add weeks to a given date resulting in a date.

When you have a start date and a date a given number of weeks from the start date, you can iterate thru your holiday database and check if any holiday falls between the start date and the end date (inclusive). If so, add 1 to the end date (DateAdd again).

Once you finished checking the holidays, you can use the WeekDay function to determine if the end date is a weekend. If so, you can DateAdd either 1 or 2 to the end date to push it into the next week. Presumably, if the end date falls on a Saturday or a Sunday you want to push the result to Monday.

Good luck! 

[catz]

In response to the reply from Sidewinder:

The following is code I found that I've used in an Access database to calculate a future date from a specified starting date. The code is attached to an on-click event procedure of a command button.
The user selects a date from a calendar control, clicks on the command button and is then asked how many days from the starting date the future date should be. The result is given after the user clicks OK. (The code can also be used for weeks or months with some small modifications.)

Private Sub Command 18_Click()
Dim FirstDate As Date 'Declare variables
Dim IntervalType As String
Dim Number As Integer
Dim Msg
IntervalType = "d" '"d" specifies days as interval
FirstDate = Calendar1
Number = InputBox ("Enter number of days to add")
Msg = "New date: " & DateAdd(IntervalType, Number,FirstDate)
MsgBox Msg
Exit_Command18_Click
End Sub

The question is: how do I adapt, modify or change the code so that weekends and specified holidays are excluded in the calculation? I'm new at this so I need all the help I can get. Thanks.

[Sidewinder]

You're on the right track. Instead of producing a MsgBox literal with the future date, compute a Calendar2 date from the expression DateAdd(IntervalType, Number,FirstDate).

Where are the holidays? If they are in a database, you can select all the records, create a recordset and loop thru each one. If the holiday is greater than or equal to the Calendar1 date AND less than or equal to the Calendar2 date, use the DateAdd function to add 1 to the Calendar2 date, effectively skipping the holiday.

For the final calculation, use the WeekDay function to determine if Calendar2 falls on a weekend. If Saturday add 2 or if Sunday add 1 with the DateAdd function to push the Calendar2 date to a weekday (Monday).

 

PS. VBA (or any language for that matter) knows nothing about the real word and does not understand the concept of holidays or weekends. If you need to completely eliminate weekend dates from your calculations, it gets more complicated.