Word Formula 2

[mlbbud]

I MS-Word I tried inserted a field and have the previous month to be displayed. So if I opened the Word file today the date would be displayed like this: December 2009.

Can someone help?

 

[macropod]

Hi mlbbud,

You can use a field coded as:
{QUOTE
{SET Delay 9}
{SET mm{=MOD(ABS({DATE \@ M}+Delay+11),12)+1}}
{SET yy{=INT({DATE \@ yyyy}+(Delay+{DATE \@ M}-1)/12)}}
{SET dd{=IF(({DATE \@ d}>28)*(mm=2)=1,28+((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)),IF((mm=4)+(mm=6)+(mm=9)+(mm=11)+({DATE \@ d}>30)>1,30,{DATE \@ d}))}}
"{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}

For a coded example showing how to do this and just about everything else you might want to do with dates in Word, check out my Word Date Calculation Tutorial, at:

http://lounge.windowssecrets.com/index.php?showtopic=249902

or

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

In particular, look at the item titled 'Calculate a day, date, month and year, using n months delay'. Do read the document's introductory material.



Cheers
[MS MVP - Word]

 

[fumei]

You can use a field coded as:
{QUOTE
{SET Delay 9}
{SET mm{=MOD(ABS({DATE \@ M}+Delay+11),12)+1}}
{SET yy{=INT({DATE \@ yyyy}+(Delay+{DATE \@ M}-1)/12)}}
{SET dd{=IF(({DATE \@ d}>28)*(mm=2)=1,28+((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)),IF((mm=4)+(mm=6)+(mm=9)+(mm=11)+({DATE \@ d}>30

That just gives me the willies...

Gerry

 

[macropod]

Hi Gerry,

Didn't you forget a bit of the code??

Actually:
{SET Delay 9}
should have been:
{SET Delay -1}

That just gives me the willies...

It's just as well you don't need to code it from scratch, then - you simply copy & paste from the tutorial and adjust the 'Delay' value.


Cheers
[MS MVP - Word]

 

[fumei]

Hi Gerry,

Didn't you forget a bit of the code??

Actually:
{SET Delay 9}
should have been:
{SET Delay -1}

Ummmm, no.


While, yes, I did not copy ALL of the code, I did copy it right from your post. Your post has:

{SET Delay 9}

Or, are you saying you made a mistake in the original post?

Yes indeed it is a good thing I do not have to work out all that stuff from scratch.

Gerry

 

[macropod]

Hi Gerry,

yes, I did not copy ALL of the code

That's what I meant - you copied nearly all of it, but not quite ...


Cheers
[MS MVP - Word]

 

[fumei]

Just as an aside...while:

{QUOTE
{SET Delay 9}
{SET mm{=MOD(ABS({DATE \@ M}+Delay+11),12)+1}}
{SET yy{=INT({DATE \@ yyyy}+(Delay+{DATE \@ M}-1)/12)}}
{SET dd{=IF(({DATE \@ d}>28)*(mm=2)=1,28+((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)),IF((mm=4)+(mm=6)+(mm=9)+(mm=11)+({DATE \@ d}>30

undoubtedly works, all those IF and MOD and numbers gives me a headache. It seems so complicated. Here is, IMO, a simple alternative.

What is the issue? To get a string showing the previous month with the applicable year. Why not just DO that?

Function LastMonth() As String
Dim strMonthText As String
Dim strYearText As String

Select Case Format(Now, "m")
    Case 1  ' January
        strMonthText = "December, "
        strYearText = Format(Now, "yyyy") - 1
    Case 2  ' Feb
        strMonthText = "January, "
    Case 3  ' March
        strMonthText = "February, "
    Case 4
        strMonthText = "March, "
    Case 5
        strMonthText = "April, "
    Case 6
        strMonthText = "May, "
    Case 7
        strMonthText = "June, "
    Case 8
        strMonthText = "July, "
    Case 9
        strMonthText = "August, "
    Case 10
        strMonthText = "September, "
    Case 11
        strMonthText = "October, "
    Case 12
        strMonthText = "November, "
End Select
If strYearText <> Format(Now, "yyyy") - 1 Then
    strYearText = Format(Now, "yyyy")
End If

LastMonth = strMonthText & strYearText
End Function

This function does just that, and is simple to read and understand.

1. check current month numeric value
2. if it is 1 (January) make the string strMonthText = "December, " - December, comma, space. Subtract 1 from current year (2010 becomes 2009) and put that into strYearText
3. any other numeric month becomes the previous month text, with comma and space
4. if strYearText is NOT current year - 1, use current year (Duh.)
5. append month text and year text.

Done.

It is easily tested.

Sub TestLastMonth()
   MsgBox LastMonth
End Sub

Exceute that now (January, 2010), and you get "December, 2009".

Go into Control Panel and change it to any date you like, and test.

Change system date to:

March 2003 - result is "February, 2003"
January 2005 - result is "December, 2004"
etc. etc. etc.

Because it is a function returning a string, you can use it just like any string.

Insert at the Selection point:

Sub TestLastMonth()
Sub TestLastMonth()
   Selection.TypeText LastMonth
End Sub

Using a fill-in bookmark procedure, insert LastMonth into a bookmark, say a bookmark "LastMonth" in the header. You do not even need to go into the header to use it.

Sub FillBM(strBM As String, strText As String)
Dim r As Range
Set r = ActiveDocument.Bookmarks("LastMonth").Range
r.Text = strText
ActiveDocument.Bookmarks.Add Name:=strBM, Range:=r
End Sub

Sub TestLastMonth()
   Call FillBM("LastMonth", LastMonth)
End Sub

You could even use a REF field, put the value into a bookmark and then update the document. That way you could put LastMonth into as many places as you like.

Gerry

 

[macropod]

Actually, I did make a mistake in the field code I posted. It should have been from another example in the tutorial:
{QUOTE
{SET Delay -1}
{SET m{=MOD({DATE \@ MM}+Delay+11,12)+1}}
{SET y{=INT({DATE \@ yyyy}+(Delay+{DATE \@ M}-1)/12)}}
"{m}-{y}" \@ "MMMM yyyy"}
which could be reduced to:
{QUOTE{SET m{=MOD({DATE \@ MM}+10,12)+1}}{SET y{=INT({DATE \@ yyyy}+({DATE \@ M}-2)/12)}}"{m}-{y}" \@ "MMMM yyyy"}

The vba function could be reduced to:

Function LastMonth() As String
Dim Month As Integer
Dim Year As Integer
Month = (Format(Now, "m") - 1) Mod 12
If Month = 0 Then
  Year = Format(Now, "YYYY") - 1
Else
  Year = Format(Now, "YYYY")
End If
LastMonth = Format(Month, "mmmm") & ", " & Year
End Function

Cheers
[MS MVP - Word]

 

[fumei]

Darn you macropod! Much better!

Gerry

 

[strongm]

oh, it can be reduced further than that:

Public Function LastMonth() As String
LastMonth = Format(DateAdd("m", -1, Now), "mmmm, yyyy")
End Function

 

[fumei]

I feel like a child....

I am so glad to be here and continually learning/remembering that I have not a clue to what I am doing.

stars to both of you.

Gerry

 

[fumei]

Not to disparage the use of field codes, but.....really....

is not:

Public Function LastMonth() As String
    LastMonth = Format(DateAdd("m", -1, Now), "mmmm, yyyy")
End Function

much easier to use and understand than:

{QUOTE
{SET Delay -1}
{SET m{=MOD({DATE \@ MM}+Delay+11,12)+1}}
{SET y{=INT({DATE \@ yyyy}+(Delay+{DATE \@ M}-1)/12)}}
"{m}-{y}" \@ "MMMM yyyy"}
which could be reduced to:
{QUOTE{SET m{=MOD({DATE \@ MM}+10,12)+1}}{SET y{=INT({DATE \@ yyyy}+({DATE \@ M}-2)/12)}}"{m}-{y}" \@ "MMMM yyyy"}

No offence intended whatsoever, but...yeah, I think it is.

Gerry

 

[macropod]

Hi Gerry,

Hey, no problems with it being easier to understand. I doubt it's any easier to use though. Plus the field code has its uses, especially in establishments (including those to whom the document might be sent) where macro security is viewed as an issue.


Cheers
[MS MVP - Word]

 

[strongm]

>where macro security is viewed as an issue

So true

 

[fumei]

Agreed. IMO, this is the greatest benefit of using field codes versus procedural code.

Gerry