Solved: Using While & DatePart to move generated dates falling on weekend to previous Friday

[lwildernorva]

Both a solution and two questions. On searching this site, I did not find a solution to an issue I had about recalculating dates in a letter that are calculated from an original date gained from user input. Further information about the project so that you have context for this code. I created a letter that requests information through several input boxes, including one for strHearingDate, the final date in the process. There are a number of other dates that I wanted to include in the letter that are calculated by working back from strHearingDate, which is always a weekday. These dates are then inserted into the letter by using various entries in the Bookmarks Range.

Since I solved this problem, I thought submitting the portion of the code that solved that problem might be helpful to someone else. I tried to give the post a title that would bring it up if someone did a search on the forum. Snippet is the operative term here; the following code is not the entire procedure for the letter I generated, but I believe is the relevant code necessary to solve this particular issue.
Dim strHearingDate As String
Dim strAWWDate As String
Dim FirstDate As Date
Dim IntervalType As String
Dim Number As Integer

strHearingDate = InputBox("Enter Hearing Date in Full Date Format, i.e., 'November 1, 2007'")

IntervalType = "d"
FirstDate = strHearingDate
Number = -30
strAWWDate = DateAdd(IntervalType, Number, strHearingDate)
While DatePart("w", strAWWDate, vbMonday) > 5
strAWWDate = DateAdd(IntervalType, -1, strAWWDate)
Wend

The Interval argument in the DateAdd and DatePart functions will accept a number of different interval arguments, including days ("d"), weeks ("ww"), weekdays ("w"), months ("m"), quarters ("q"), and years ("yyyy"). If I had needed to calculate forward rather than backward from strHearingDate or wanted to move the date that fell on the weekend to the following Monday, I could have used positive rather than negative numbers in DateAdd. The term vbMonday is an optional constant that sets the first day of the week for the calculation period to Monday and makes the "> 5" operative when the result returned is vbSaturday (6) or vbSunday (7). The default value for the first day of the week is vbSunday; under the default values, vbSunday is 1 while vbSaturday is 7. The change allows the simpler statement "> 5" rather than having to write code that searches for dates returned that are 1 or 7.

Although many of the dates are calculated by going back 14, 21, 28, and 56 days, all calculations that mean that the generated date will also be a weekday, several of the entries required going back 10, 15, and 30 days, which could result in a generated date that would fall on a weekend. I wanted to clean that issue up by finding a method that would take the generated date that fell on a weekend to the previous Friday. The While. . .Wend statement used above accomplishes that task.

Two quick questions: First, anyone have any alternatives that accomplish this purpose more efficiently? Second, am I correct in understanding that the only way to accomplish the additional goal of moving a generated date off all holidays in a year is by reference to an external database of annual holidays? I have no doubt that I can take care of annual holidays that occur on the same date (Christmas, New Year's Day, U.S. Veterans' Day, and U.S. Independence Day) without reference to an external database but the variable holidays (Presidents' Day, Labor Day, Memorial Day, Thanksgiving Day) apparently must be covered by reference to an external database (this information was gleaned from an MSDN article: http://msdn2.microsoft.com/en-us/library/aa227592(VS.60).aspx) (http://msdn2.microsoft.com/en-us/library/aa227592%28VS.60%29.aspx%29).

[mdmackillop]

Just posted a bit of date fixing here (http://www.vbaexpress.com/forum/showthread.php?t=11653).

[mdmackillop]

BTW, once you solve your question, you might consider submitting it to the KB as a method for determining dates.

[lwildernorva]

Thanks. Didn't think of that because I wasn't certain it was significant enough. Although it was an irritating problem to me, it was not a big deal once I figured it out. I'll certainly mark this solved in a few days if I don't receive any other suggestions about approaching the problem because I have a workable solution.

[lwildernorva]

The more elegant solution that addresses weekends and holidays (here using the dates in 2007 for the American holidays: Memorial Day, July 4th, Labor Day, Columbus Day, Veterans Day, Thanksgiving and the Friday after, Christmas Eve, Christmas Day, and New Year's Eve). I can't take full credit for this one since I received some advice at another discussion group:
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
strFirstDate = InputBox("Enter Beginning Date in Full Date Format, i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
Do While DatePart("w", strSecondDate, vbMonday) > 5 Or Holiday(strSecondDate) = True
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #3/2/2007#, #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#, #11/12/2007#, #11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function I encountered a problem in my initial attempt to address holidays. I constructed logic in my macro that searched for weekends first and then added the holidays after that, using multiple Do While statements. The approach was admittedly bad but initially appeared to work. On further examination, however, I discovered that where the date entered would generate a date that might fall beyond one of the listed holidays, it would not take into account a weekend if it immediately followed the holiday (think Thanksgiving here).
I then entered multiple Do While statements after the code for these particular weekend holidays, but the truth is the Do While statements were a dead end, producing jumbled and confusing code. I was working on a Select Case solution, which with the assistance noted above, I reached.

Hope this helps!