Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Word mail merge calculation of date



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

  2. #2
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    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
    Last edited by Gary Frieder; 2011-07-29 at 11:28.

  3. #3
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    Quote Originally Posted by RAISH View Post

    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

  4. #4
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    Question: Are you using QUOTE fields to keep the underlying date field from updating automatically upon document opening?

  5. #5
    New Lounger
    Join Date
    Dec 2010
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.
    Last edited by RAISH; 2011-07-27 at 20:29.

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by Charles Kenyon View Post
    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.

  7. #7
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    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
    Last edited by Gary Frieder; 2011-07-29 at 11:28.

  8. #8
    New Lounger
    Join Date
    Dec 2010
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  9. #9
    New Lounger
    Join Date
    Dec 2010
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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"}

  10. #10
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    Glad to hear that you got it to work. Thanks for letting us know.

    Charles Kenyon
    Madison

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •