Advanced Dates?

[Hamoth]

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.

 

[Doug Robbins - Word MVP on news.microsoft.com]

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")

--
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

 

[Greg Maxey]

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

 

[Hamoth]

Oh weak, I just found out that my version of office doesn't support macros.

I want to thank you both for the amazing and informative help. I wish I
would have been able to try this out. I don't suppose there's anything
nearly this advanced that I can do with the { field } logic is there? I
notice that it allows some apparent date parsing and comparative logic:

" { IF { DATE \@ "MM" } < 07 { DATE \@ "yyyy" } { = { DATE \@ "yyyy" }+1
\# "0000" } } "

But that horse isn't gunna take me far as I need to go, right?

 

[Greg Maxey]

Hamoth,

I have never heard of a version of Office that doesn't support macros. That
is not to say that it isn't so. Have you looked at your macro security
settings?

 

[Doug Robbins - Word MVP on news.microsoft.com]

Hi Greg,

Guess I should have realized that there should have been an "of the month"
there. It did not make much sense without it.

--
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 Robbins - Word MVP on news.microsoft.com]

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

 

[macropod]

Hi Hamoth,

For a non-macro solution, you can use a field coded as:
{QUOTE
{SET Delay 90}
{SET Weekday 4}
{SET a{=INT((14-{DATE \@ M})/12)}}
{SET b{={DATE \@ yyyy}+4800-a}}
{SET c{={DATE \@ M}+12*a-3}}
{SET d{DATE \@ d}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}
{SET jd{=INT(jd/7)*7+Weekday}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET dd{=((dd<8)+(dd>14)*(dd<22))*7+dd}}
{SET mm{=i+3-12*INT(i/10)}}
{SET Limit{=IF((mm=2),28+((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)),IF((mm=4)+(mm=6)+(mm=9)+(mm=11)=1,30,31))}}
{SET yy{=100*e+g-4800+INT(i/10)}}
{IF{dd}> 28 "{SET dd{=dd+14-Limit}}{SET mm{=MOD(mm,12)+1}}{SET yy{=yy+(mm=1)}}"}
"{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}
With this coding I've assumed that, if the calculated date falls on the 5th Friday of the month, you want it to roll over to the 2nd
Friday of the following month.

The above field code is based on the item titled 'Calculate a day, date, month and year, using n days delay' in my Word Date
Calculation Tutorial, at:
http://www.wopr.com/index.php?showtopic=249902
or
http://www.gmayor.com/downloads.htm#Third_party
with the 'Delay' value changed to '90' and the addition of the following modifications to the field coding inserted where indicated:
{SET Weekday 4}
....
{SET jd{=INT(jd/7)*7+Weekday}}
....
{SET dd{=((dd<8)+(dd>14)*(dd<22))*7+dd}}
....
{SET Limit{=IF((mm=2),28+((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)),IF((mm=4)+(mm=6)+(mm=9)+(mm=11)=1,30,31))}}
{SET yy{=100*e+g-4800+INT(i/10)}}
{IF{dd}> 28 "{SET dd{=dd+14-Limit}}{SET mm{=MOD(mm,12)+1}}{SET yy{=yy+(mm=1)}}"}

The 'SET Limit' field was lifted from the item titled 'Calculate the day & date of a given day of the week next month', with 'Month'
changed to 'mm'.

 

[macropod]

Hi Greg,

I'm sure you've heard of Office 2008.

 

[Greg Maxey]

Doug,

I don't know. If tested with:

today = DateSerial(2009, 4, 28)

It returns Friday July 31, 2009 which is the fifth Friday (not the second or
fourth) of the month. The code I cobbled to together returns Friday August
14, 2009. Which is the second of "a month" but not of "the month" that the
first condition (> 90 days) is met.

 

[Greg Maxey]

macropod,

Only heard of. I certainly wasn't aware that it didn't support macros. Is
this some administrative feature that can be turned on or off according to a
corporate policy or are macros simply gone in the next version of Office?

 

[Greg Maxey]

macropod,

Your finesse in this area never ceases to astound.

 

[Doug Robbins - Word MVP on news.microsoft.com]

I think it a version for the Mac, but I also think that I heard that vba is
being reinstated in the next Mac Office version.

--
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 Robbins - Word MVP on news.microsoft.com]

OK, I need to include another

Case Is > 28
i = i + 14

in the second Select Case construction. Perhaps the OP could confirm that
it should then be the second Friday of the following month in such a case.

--
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

 

[Hamoth]

Office 2008 Mac = No macros.

Hamoth,

I have never heard of a version of Office that doesn't support macros. That
is not to say that it isn't so. Have you looked at your macro security
settings?

 

[Hamoth]

First - WOW.
Second - THANKS!
Third - Yes. If I correctly understand the question - Only second and
fourth Fridays. Never the 1st, 3rd, or 5th.

 

[Hamoth]

A M A Z I N G.

I had no idea word was so versatile, nor that such expertise was about! You
guys are kicking ass!

How would I insert something like that?
I've tried a few different ways, but am getting an error : Error! Digit
expected. when I try to run it.

I've tried "Insert -> field" and then toggled the field code, and pasted
this code between the brackets. Feels like I might be going at this wrong?

 

[macropod]

Hi Hamoth,

Without seeing your implementation of the field code, it's impossible for me to say where the error you reported is occurring. I
suggest you start by using a copy of the field from the item titled 'Calculate a day, date, month and year, using n days delay' in
my Word Date Calculation Tutorial and carefully making the exact changes suggested in my previous post. Keep in mind too that field
brace pairs (ie '{ }') are created via Ctrl-F9 - you can't simply type them or copy & paste them from the post - but the ordinary
brackets (ie '()') are just that.

 

[Greg Maxey]

Doug,

That works. You are so clever ;-)

 

[Gordon Bentley-Mix on news.microsoft.com]

~raises hand~

Can I go home now? My brain is full...
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!