Date Calculation Code

[Shorty10]

I'm looking for a procedure that will calculate a future date from a specified starting date. The user would input the starting date and the number of days (possible adapted to be able to input weeks or months) to add to it to get the future date. The calculation must skip weekends and holidays.


I need to know where the code goes, etc.

Thanks, in advance, for any help offered.

 

[SkipVought]

Check out the WORKDAYS spreadsheet function.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[macropod]

Hi Shorty,

Which application?

Cheers

[MS MVP - Word]

 

[Shorty10]

In response to macropod and SkipVought replies:

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.

 

[macropod]

Hi Shorty10,

how do I adapt,modify or change the code so that weekends and specified holidays are excluded in the calculation?

I'm still not sure what you want:
1. What is supposed to happen if the calculated date falls on a weekend or a specified holiday? Do you want to retrieve the preceding Friday or the following Monday? And if the adjusted date is a specified holiday?
2. What is supposed to happen if a weekend falls between the start and end dates? Do you want to treat the input as a count of weekdays only (including or excluding specified holidays), or as days of any kind?

I've done this kind of thing with Word fields (see my Date Calc 'tutorial', at:

http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902

or

http://www.gmayor.com/downloads.htm#Third_party)

and I'm sure the algorithm could be turned into vba (perhaps you'd like to have a go at doing that), but you need to be clear with your requirements.

Cheers

[MS MVP - Word]