locked
Future Date Calculation Field in Word 2010 RRS feed

  • Question

  • Dear all,

    My organization is confronted with a challenge to which I cannot find any solution. I have a Word Document (created with MS Word 2010) in which a Calculated Date Field is to be added. The date field needs to do the following:

    Display the date of today +21 days and if the resulting date is a Saturday or Sunday then Monday's date is to be shown.

    Limitations:
    - Makros or VBS cannot be used in our organization. 
    - Users have different OS locale settings. The Calculated Date Filed within the word document should however alyways show the following date format 'dd MMM yyyy' (e.g. 5 Nov 2015).

    Would anyone be able to compile a field formula that works?

    Tuesday, November 17, 2015 1:53 PM

Answers

  • I think the issue there is that some non-English languages require a semi-colon separator for the MOD tests in the field code instead of the comma separator. Since there's no region-independent way of coding a MOD statement, we need to work around that, which can be done by changing:
    {SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}
    to:
    {SET jd{=jd+((jd-INT(jd/7)*7)>4)*(7-(jd-INT(jd/7)*7))}}


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by AndyChTh Sunday, November 29, 2015 12:45 PM
    Sunday, November 29, 2015 8:21 AM

All replies

  • To see how to do this and just about everything else you might want to do with dates in Word, check out my Microsoft Word Date Calculation Tutorial, at:
    http://windowssecrets.com/forums/showthread.php/154368-Microsoft-Word-Date-Calculation-Tutorial
    or:
    http://www.gmayor.com/downloads.htm#Third_party
    In particular, look at the item titled 'Handling Weekends and Holidays in Calculated Dates'. Do read the document's introductory material.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by Charles Kenyon Wednesday, November 18, 2015 4:27 AM
    Wednesday, November 18, 2015 2:40 AM
  • The response from macropod is the best answer you will ever get. Be sure to actually read all of the introductory material; it will save you time and frustration.

    Charles Kenyon Madison, WI

    Wednesday, November 18, 2015 4:26 AM
  • Wednesday, November 18, 2015 5:32 AM
  • That works but requires a macro. Fields can be put in a template and give a dynamic result without having to depend on being able to run a macro.

    Charles Kenyon Madison, WI

    Thursday, November 19, 2015 5:33 AM
  • Besides which, the macro there: doesn't input anything that updates as the date changes; doesn't take account of weekends; and ignores the OP's categoric statement -
    Makros or VBS cannot be used in our organization.

    The only other option is to follow the link from that page to my tutorial...


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by Charles Kenyon Thursday, November 19, 2015 6:13 PM
    • Marked as answer by George123345 Tuesday, November 24, 2015 1:52 AM
    • Unmarked as answer by AndyChTh Friday, November 27, 2015 2:15 PM
    Thursday, November 19, 2015 5:41 AM
  • Thanks a lot for your support so far.

    As you suggested, I was able to find a suitable field formula in Paul Edelstein's tutorial "DateCalc.zip" available at http://www.gmayor.com/downloads.htm  Unfortunately, the field formula does not work as expected.

    Since the .dotx template containing the formula was made on a PC with locale setting "English (UK)" and the template's property is set to "English" furthermore, it works well on my PC. My colleagues however have PC's setup with various different locales. If for e.g. the template is used on a PC with locale setting "German (Germany)" the calculation is faulty and the month is no longer shown in English

    Since I cannot understand the logic in the first place, all my attempts to fix the matter have failed. What is needed is simply a formula that always calculates correctly and displaying the result in a persistant English format no matter the locale settings of the PC it is used on.

    Would you have any idea how to achieve that?

    formula

    Friday, November 27, 2015 2:57 PM
  • On the last line of the field code, change:
    "{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"
    to:
    "{dd} {IF{=INT(mm/4)}= 0 {=mm-2 \# "'March';'January';'February'"} {IF{=INT(mm/7)}= 0 {=mm-5 \# "'June';'April';'May'"} {IF{=INT(mm/10)}= 0 {=mm-8 \# "'September';'July';'August'"} {=mm-11 \# "'December';'October';'November'"}}}} {yy}"

    Note: The field brace pairs (i.e. '{ }') for the above example
    are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. The spaces represented in the field construction are all required. For a macro to turn the above text string into a working field code, see Convert Text Representations of Fields to Working Fields in the Mailmerge Tips and Tricks threads at:
    http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
    or:
    http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks


    Cheers
    Paul Edstein
    [MS MVP - Word]


    • Edited by macropodMVP Friday, November 27, 2015 8:38 PM
    Friday, November 27, 2015 8:38 PM
  • Thank you so much for your help so far.

    My template has now the following field formula in use and is finally displaying the months in English no matter what locale the PC is set to:

    Unfortunately, the calculation does still not work properly.
    The formula is supposed to add 21 days to today's date and if the resulting day is on a weekend, display the next working day. This works fine if I have set my PC's locale to "English (UK)" but results in a wrongly calculated date when done with another locale, e.g. "French (France)", "Italian (Italy)", "German (Germany)".

    Today's date is 29/11/2015, add 21 day, results in Sunday 20/12/2015, so the date displayed should be: 21 December 2015.

    Correct result when PC's locale is set to English (UK):  21 December 2015

    Wrong result when PC's locale is set to non-English locale: 24 November -4713

    Why is this caclculation failing on other locales and what can be done to prevent such?

     

    Sunday, November 29, 2015 6:12 AM
  • I think the issue there is that some non-English languages require a semi-colon separator for the MOD tests in the field code instead of the comma separator. Since there's no region-independent way of coding a MOD statement, we need to work around that, which can be done by changing:
    {SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}
    to:
    {SET jd{=jd+((jd-INT(jd/7)*7)>4)*(7-(jd-INT(jd/7)*7))}}


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by AndyChTh Sunday, November 29, 2015 12:45 PM
    Sunday, November 29, 2015 8:21 AM
  • That last modification suggested made my day. It's working perfectly now.

    Thank you very much for all your great support!

    Sunday, November 29, 2015 12:49 PM