Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Dec 2009
    Location
    Marseilles
    Posts
    47
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Field to show last day of next month

    Hi,

    I need to insert a field that will show the last day of the month after the current month. I have tried the "Calculate the day & date of the nth day of next month" field in the Word file in the Date Calculation Tutorial, but I just get the following syntax error "!Erreur de syntaxe, ,,-!Erreur de syntaxe, )-2014" (French MSOffice). Can't trace where it's coming from, the code seems identical. (Once I got it to work, I was going to substitute "31" for the "1")...

    I created an identical field but this time unchecked "Preserve formatting during updates". When I hit Alt+F9, this field shows "unexpected end to formula".

    Anyone know either how to fix this, or can give me the correct field code to do what I want to do?

    Thanks
    Last edited by Marp; 2013-12-16 at 15:48.

  2. #2
    Lounger
    Join Date
    Dec 2009
    Location
    Marseilles
    Posts
    47
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Update:

    The second (cloned) field now gives the same syntax error on Alt+F9, even though I haven't touched it

  3. #3
    Lounger
    Join Date
    Dec 2009
    Location
    Marseilles
    Posts
    47
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Ok, got it to work, but the date shows 1/1/2014 instead of 31/1/2014. Can't find where to change the code to make it show the last day of the month...

  4. #4
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Madison, Wisconsin, Wisconsin, USA
    Posts
    1,708
    Thanks
    57
    Thanked 65 Times in 63 Posts
    Try the field for
    Calculate the day & date of the last or nth-to-last day of a given month.

    The value of Offset changes the month. "1" would be next month.

    {QUOTE {SET Subtract 0} {SET Offset 0} {SET mm{=MOD({Date \@ MM}+MOD(Offset,12)+11,12)+1}} {SET yy{=INT({DATE \@ yyyy}+({DATE \@ M}+Offset-1)/12)}} {SET dd{=MAX(1,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)+({ DATE \@ d}>30)=1,30,31))-Subtract)}} "{dd}-{mm}-{yy}" \@ "dddd, d*MMMM*yyyy"}

    This one counts from the end of the month, rather than from the beginning. So, "0" for the Subtract gives you the last day of the month.
    Charles Kyle Kenyon
    Madison, Wisconsin

  5. #5
    Lounger
    Join Date
    Dec 2009
    Location
    Marseilles
    Posts
    47
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks for taking the time to reply, Charles.

    The code I have is exactly the same as yours. The only changes are that I have:

    {QUOTE{SET Subtract 0}{SET Offset 0}{SET mm{=MOD({Date \@ MM}+MOD(Offset,1)+11,12)+1}}

    and

    {SET yy{=INT({DATE \@ yyyy}+({DATE \@ M}+Offset+1)/12)}}

    This gives me:

    1/01/2014 whereas I am looking for 31/01/2014

    Last edited by Marp; 2013-12-17 at 05:56.

  6. #6
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Madison, Wisconsin, Wisconsin, USA
    Posts
    1,708
    Thanks
    57
    Thanked 65 Times in 63 Posts
    {QUOTE
    {SET Subtract 0}
    {SET Offset 0}
    {SET mm{=MOD({Date \@ MM}+MOD(Offset,12)+11,12)+1}}
    {SET yy{=INT({DATE \@ yyyy}+({DATE \@ M}+Offset-1)/12)}}
    {SET dd{=MAX(1,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)
    +({ DATE \@ d}>30)=1,30,31))-Subtract)}}"{dd}-{mm}-{yy}" \@ "d/MM/yy"}

    Original formula except for the display set in bold. Not sure of the reason you made the changes you did.

    Note, I do not pretend to understand these fields. I just know enough to use them and get a result. Paul is the one who does the brainy stuff.

    Note, when trying to show field codes like this online, I use Cindy Meister's macro FieldToString found here. I generally do not store code in my normal.dotm file but this is one of the utilities I keep on hand, with lots of backup. Because I have an "Option Explicit" setting, I added one statement at the beginning of her macro:
    Dim CurrChar as String

    Even though the macro is labeled for use in Word 97, it works fine in Word 2013.

    For using text codes from online, I manually add the field braces, working from the middle out. (The macro does NOT work in reverse.)
    Last edited by Charles Kenyon; 2013-12-17 at 08:13.
    Charles Kyle Kenyon
    Madison, Wisconsin

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,939
    Thanks
    0
    Thanked 203 Times in 184 Posts
    The field code for the 'Calculate the day & date of the last or nth-to-last day of a given month' example, as supplied in the tutorial, already calculates the last day of next month. You don't need to change either of the 'Subtract' or 'Offset' values. However, as explained in the tutorial's introduction, you may need to change the field code to accommodate difference in regional settings. The tutorial includes a macro for doing just that. In addition, of course, you'll need to change the final date switch in the field code to suit your desired output format.

    As for converting field codes to text and back again, see:
    To convert field codes to field text strings - http://www.gmayor.com/export_field.htm#FieldToText
    To convert field text strings to field codes - http://www.gmayor.com/export_field.htm#TextToField
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Lounger
    Join Date
    Dec 2009
    Location
    Marseilles
    Posts
    47
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi macropod,

    Thanks for the input. I decided to start from scratch again. I went back to the Word file and copied the field code under the heading "Calculate the day & date of the last or nth-to-last day of a given month".

    I pasted it in my document, updated the field, saved the document and hit Alt+F9. The text says "lundi, 1 décembre 2014" (Monday, 1st December 2014).

    In the tutorial it says, "Subtract = 0 gives the last day of the month". I have left Substract (in the [SET Subtract]) section alone (at 0) and it gives me the 1st of the month not the last day. Maybe I should be doing something else...? Is it anything to do with the year change coming up?

    It also says, "Changing the Offset from 0 to a positive or negative number ‘n’, returns the last day of the nth future or past month, respectively". The Offset in the example is set at 1, so I would have thought it should show January and not December... Again, perhaps I'm missing something.

    The date format here is the same as with you (ddmmyy not mmddyy). The only difference I can think of is that I'm using French Word. However, in the Insert Field menu item, the code seems to be in English.

    Thanks for any light you can shed on the subject.

    And thanks for not mentioning the Ashes...

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,939
    Thanks
    0
    Thanked 203 Times in 184 Posts
    If I simply copy & paste the field code into a new document, select it and press F9, it displays 'Friday, 31 January 2014', which I what I understand you want (language differences aside). However, simply changing the system regional settings to France causes the field to display 'Monday, 1 December 2014'. That's because a French regional setting requires semi-colon separators instead of comma separators. This is mentioned in the tutorial Introduction:
    Similarly, many of the fields in this document use comma separators for IF tests, MOD statements and the like. To use the fields in regions that use commas as decimal separators, the commas in the affected parts of the field codes need to be changed to semi-colons.
    If you replace all of comma separators in the filed with semi-colon separators, then update the field, it once again returns 'Friday, 31 January 2014'.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. The Following 2 Users Say Thank You to macropod For This Useful Post:

    Charles Kenyon (2013-12-19),Marp (2013-12-19)

  11. #10
    Lounger
    Join Date
    Dec 2009
    Location
    Marseilles
    Posts
    47
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks for the tip. I didn't know there was a separator difference.

Posting Permissions

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