Word mail merge calculation of date

Topic

New Reply

I hope you can help me out, the macropod guide was really helpful.

The computer system I’m using generates word documents ready for working. These letters do not always get actioned on the date they are generated so in order to automatically correct the date on the letter I using the following which works great:

20 June 2011

However I would still like to use the systems generated letterdate to calculate a set of Account dates.

The following below is a two part calculation which uses today’s date as the basis to work out what the account dates will be.

So when the computer system generates the JUNE letters, we know that the accounting dates for these letters will be: 01/07/2010 to 30/06/2011. However any cases from JUNE that are opened in the next Calendar month display as 01/08/2010 to 31/07/2011. I’ve been trying to use the generated date which will always give the correct calendar that it was generated in regardless when the letter is then opened.

However I know that the first line of each of the formula’s is wrong and I don’t know how to fix it, any help guys would be greatly appreciated.

Formula in Attachment.

01/08/2010

TO

30/06/2011

Reply | Quote

Replies

I’ve looked at it and am still trying to figure out what you are trying to accomplish and what is going wrong. Please give examples of the original date and the dates you are trying to produce for a couple of different months. Show what you want the field to produce and what it is actually producing.

I believe these fields work the same in all versions since Word 97 but just to be safe, which version of Word are you using.

For anyone else reading this, the macropod guide you refer to can be found at http://www.gmayor.com/downloads.htm#Third_party. It is what I look to for ideas when I’m confused. I’m not sure how to find it here in the lounge anymore.

Charles Kenyon
Madison

Calculated Dates in Microsoft Word
http://addbalance.com/word/datefields2.htm

Date Fields in Microsoft Word
http://addbalance.com/word/datefields.htm

Reply | Quote

Question: Are you using QUOTE fields to keep the underlying date field from updating automatically upon document opening?

Reply | Quote

I essentially want to embed the mergefield date (date generated by the system) into the formula and use that as the basis for the calculation. Todays date {QUOTE { DATE @ “dd MMMM yyyy” }} is purely used to set the todays date on the outgoing letter, which is in fairness is a seperate thing really, sorry. However, I did use the quote thing in the calculation formula and like you say its probably erroneous and causing confusion.

If I use {mergefield dateletter} it would put something like 27/06/2011 as apposed to 28/07/2011.

If I can get the formula to work with the generated date the accounting dates will always be correct.

Reply | Quote

I hope you can help me out, the macropod guide was really helpful.

The computer system I’m using generates word documents ready for working. These letters do not always get actioned on the date they are generated so in order to automatically correct the date on the letter I using the following which works great:

20 June 2011

However I would still like to use the systems generated letterdate to calculate a set of Account dates.

The following below is a two part calculation which uses today’s date as the basis to work out what the account dates will be.

So when the computer system generates the JUNE letters, we know that the accounting dates for these letters will be: 01/07/2010 to 30/06/2011. However any cases from JUNE that are opened in the next Calendar month display as 01/08/2010 to 31/07/2011. I’ve been trying to use the generated date which will always give the correct calendar that it was generated in regardless when the letter is then opened.

However I know that the first line of each of the formula’s is wrong and I don’t know how to fix it, any help guys would be greatly appreciated.

Formula in Attachment.

01/08/2010

TO

30/06/2011

The formulas in your document are as follows (for other readers):

The one that works is: {QUOTE { DATE @ “dd MMMM yyyy” }}

The ones that don’t work are:

{QUOTE MERGEFIELD dateletter
{SET Delay -1}
{SET od{DATE @ 1}}
{SET oy{DATE @ yyyy}}
{SET mm{=MOD({Date @ MM},12)+1}}
{SET ld{=(mm=2)*(od=28+((MOD(oy,4)=0)+(MOD(oy,400)=0)-(MOD(oy,100)=0)))}}
{SET yy{=oy+Delay}}
{SET dd{=ld*(28+(mm=2)*((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)))+od*(1-ld)}}
“{dd}-{mm}-{yy}” @ “dd/MM/yyyy”}

to

{QUOTE MERGEFIELD dateletter
{SET day 31}
{SET mm{=MOD({Date @ MM},12)+0}}
{SET yy{=INT({DATE @ yyyy}+({DATE @ M}-1)/12)}}
{SET dd{=MIN(day,IF((mm=2),28+({DATE @ M}=2)*((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,31)))}}
“{dd}-{mm}-{yy}” @ “dd/MM/yyyy”}

Note, these are text, not fields. For the actual fields go to the original attachment. This text is produced by a macro that I picked up a while back from Cindy Meister, I think.

Charles Kenyon

Reply | Quote

This text is produced by a macro that I picked up a while back from Cindy Meister, I think.

Related thread: Convert field codes to text for posting | Windows Secrets Lounge.

Reply | Quote

It turns out that the macro was from macropod, here. I like it because of the text box and that it puts the string into the clipboard. It works one field at a time.

Sub FieldCodeToString()
‘ Posted to the newsgroup on 15 April 2004 by macropod
‘ Converts a Word field code to a string (in the clipboard)
‘
Dim Fieldstring As String, NewString As String, CurrChar As String
Dim CurrSetting As Boolean, fcDisplay As Object
Dim MyData As DataObject, X As Integer
NewString = “”
Set fcDisplay = ActiveWindow.View
Application.ScreenUpdating = False
CurrSetting = fcDisplay.ShowFieldCodes
If CurrSetting True Then fcDisplay.ShowFieldCodes = True
Fieldstring = Selection.Text
For X = 1 To Len(Fieldstring)
CurrChar = Mid(Fieldstring, X, 1)
Select Case CurrChar
Case Chr(19)
CurrChar = “{”
Case Chr(21)
CurrChar = “}”
Case Else
End Select
NewString = NewString + CurrChar
Next X
Set MyData = New DataObject
MyData.SetText NewString
MyData.PutInClipboard
fcDisplay.ShowFieldCodes = CurrSetting
MsgBox NewString & ” is now in the Clipboard for pasting.”
End Sub

Charles Kenyon
Madison

Reply | Quote

I’m not sure what this does exactly? I’ve added this in as a macro, when I run it it creates a compiler error on “MyData As DataObject”.

What things should I have in place in order for this to work.

Reply | Quote

managed to get it working, thanks for the help though, just used

{QUOTE dateletter

and it seemed to pick it up okay, I thought I had to introduce the date with {MERGEFIELD dateletter

{QUOTE dateletter
{SET Delay -1}
{SET od{dateletter @ 1}}
{SET oy{dateletter @ yyyy}}
{SET mm{=MOD({dateletter @ MM},12)+1}}
{SET ld{=(mm=2)*(od=28+((MOD(oy,4)=0)+(MOD(oy,400)=0)-(MOD(oy,100)=0)))}}
{SET yy{=oy+Delay}}
{SET dd{=ld*(28+(mm=2)*((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)))+od*(1-ld)}}
“{dd}-{mm}-{yy}” @ “dd/MM/yyyy”}

Reply | Quote

Glad to hear that you got it to work. Thanks for letting us know.

Charles Kenyon
Madison

Reply | Quote