How about the following in which the For j ... Next is just for testing
Dim today As Date, today1 As Date
Dim i As Long
Dim j As Long
today = Date
For j = 0 To 15
today1 = DateAdd("d", j, today)
Select Case Format(today1, "ddd")
Case "Sat"
i = 97
Case "Sun"
i = 96
Case "Mon"
i = 95
Case "Tue"
i = 94
Case "Wed"
i = 93
Case "Thu"
i = 92
Case Else
i = 91
End Select
Select Case Format(DateAdd("d", i, today1), "d")
Case Is < 8
i = i + 7
Case 15 To 20
i = i + 7
End Select
MsgBox "The Date is " & Format(DateAdd("d", i, today1), "dddd d MMMM yyyy")
Next j
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
Doug,
I think it is more involved than that. I think the OP set the condition that the future date be a second or fourth Friday of
the future month. This isn't fully tested, but works for the limited testing that I did:
Sub ScratchMacro()
Dim i As Long
Dim bConMet As Boolean
Dim myDate As Date
Dim anchorDate As Date
Dim today As String
Dim lngSecond As Long
Dim lngFourth As Long
bConMet = False
myDate = DateSerial(2009, 4, 6) '(April 6 2009 used for testing)
myDate = DateAdd("d", 90, myDate)
Do
anchorDate = DateSerial(Year(myDate), Month(myDate), 1)
Select Case Format(anchorDate, "ddd")
Case "Fri"
lngSecond = 8
lngFourth = 22
Case "Sat"
lngSecond = 14
lngFourth = 28
Case "Sun"
lngSecond = 13
lngFourth = 27
Case "Mon"
lngSecond = 12
lngFourth = 26
Case "Tue"
lngSecond = 11
lngFourth = 25
Case "Wed"
lngSecond = 10
lngFourth = 24
Case "Thur"
lngSecond = 9
lngFourth = 23
End Select
Select Case Day(myDate)
Case Is > lngFourth
myDate = Format(DateAdd("d", 1, myDate), "d MMMM yyyy")
Case Is <= lngSecond
MsgBox DateSerial(Year(myDate), Month(myDate), lngSecond)
bConMet = True
Case Is <= lngFourth
MsgBox DateSerial(Year(myDate), Month(myDate), lngFourth)
bConMet = True
End Select
Loop Until bConMet
End Sub
Doug Robbins - Word MVP on news.microsoft.com wrote:
Use
Dim today As String
Dim i As Long
today = Format(my_date, "ddd")
Select Case today
Case "Sat"
i = 104
Case "Sun"
i = 103
Case "Mon"
i = 102
Case "Tue"
i = 101
Case "Wed"
i = 100
Case Thur
i = 99
Case Else
i = 98
End Select
MsgBox "The Date is " & Format(DateAdd("d", i, my_date), "dddd d MMMM
yyyy")
I'm trying to see if I can take a date from a custom field "my_date"
and calculate from that the soonest 2nd or 4th Friday that is at
least 90 days after "my_date".
I've seen a few examples of some hefty / sophisticated ways to
manipulate dates in Office / Word...but nothing that leads me to
understand it enough to
do what I'm describing above.
