Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jun 2008
    Location
    Baltimore, Maryland, USA
    Posts
    90
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Calculation (2003 / 2007)

    Good day.

    I have attempted to construct a formula that will calculate the final payment date of a loan. I found a formula that comes close to the stated goal in that fantastic "Date & Time Manipulation" tutorial. (Thank you to Jefferson, Paul, and Hans for direction to the appropriate resources.)

    The formula I selected calculates a future date for a designated number of months from the current date. I need to changed so that it calculates from a date represented by a field code (the first payment date). I first replaced the Delay (months) value with alternate integers, and then modified the date format of the formula to match that of my loan software. The formula still worked, up to that point.

    I then replaced the Delay value and DATE expression (current date) with field codes from the loan program. The formula now yields only Syntax error text on the printed document.

    Will this type of formula not function with field codes, or have I perhaps not added some necessary instructions?

    I have attached a document with both the functional and revised/nonfunctional formulas, with the corresponding modifications highlighted. Can anyone determine what could be causing the problem? Thank you.

    Kevin
    Attached Files Attached Files

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Date Calculation (2003 / 2007)

    Have you tried merging this? I don't know how to test whether this gives a valid result without conducting the merge.

  3. #3
    Star Lounger
    Join Date
    Jun 2008
    Location
    Baltimore, Maryland, USA
    Posts
    90
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation (2003 / 2007)

    Yes, through my loan program. The field codes (3190 - Loan Term months and 57 - First Payment Date) extract data from fields in the loan program screens. The formula produces syntax error text when the document is printed.

    For the purpose of testing the formula outside of the loan program, I suppose those two codes could be replaced with Word codes. I wasn't sure how to get a data source/codes comparable to the loan program data from within Word.

    Code # 57 would need to be replaced with a code that pulsl a date in MM dd yyyy format, but not the auto-updating date Word provides. Field #3190 would need to be replaced with one that simply pulls an integer (i,e, - 360).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Calculation (2003 / 2007)

    The field codes using PNT_57 should not look like

    {{ MERGEFIELD PNT_57 } @ M }

    but like

    { MERGEFIELD PNT_57 @ M }

    and similar for the other date format switches @ ...

  5. #5
    Star Lounger
    Join Date
    Jun 2008
    Location
    Baltimore, Maryland, USA
    Posts
    90
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation (2003 / 2007)

    Hans -

    Nineteen variations and many hours into this effort, eliminating the superfluous brackets enabled the formula to work. I was not able to isolate and delete the extra brackets - the entire field code would auto-select, and had to be deleted. So I had to manually retype the codes. But that was indeed the solution.

    4:15 am. I can finally put this particular problem (and myself) to bed. Obsessive/Compulsive + not that bright = long periods of trial and error. Once again, thank you so very much for your assistance. Greatly appreciated.

    KO

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Date Calculation (2003 / 2007)

    Hi Kevin,

    Here's a fairly simple way to implement what you're after; simply use a SET field to create a 'StartDate' bookmark and replace the 'Date' strings with 'StartDate'. Note that I changed your 'SET Delay' field too, as the syntax wasn't right.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Star Lounger
    Join Date
    Jun 2008
    Location
    Baltimore, Maryland, USA
    Posts
    90
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation (2003 / 2007)

    Very good. It works perfectly. As you noted, I did have a problem with the Delay field; my solution was unnecessarily convoluted, as evidenced by yours.

    Thank you very much for this efficient alternative. My head was starting to hurt.

    Kevin

Posting Permissions

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