locked
Word 2010-2011 and date calculations issue RRS feed

  • Question

  • I have a Word document used as a printable template for a task with follow-ups for several days. Document was created using Word for Mac 2011. To simplify things, the document had date code calculations to enter today's date, along with dates one and two days in the future for follow-up lines. Date calculations were based on DateCalc (from MVP Graham Mayor; code used is at end of this post). Up until very recently, this worked. Since the last Office for Mac service pack update, it is not working correctly. In testing this, I found the exact same behavior in Word 2010 on Windows.

    What SHOULD happen is that after updating fields manually, the dates should be (for example)

    01/01/2013 (today) 01/02/2013 (today + 1 day) 01/03/2013 (today + 2 days)

    This works, every time. However, as soon as I try to print the page, the fields update again - and this is where the error occurs. Rather than changing to today, tomorrow, and two days from now, the fields set for future dates are both changed to the same value, resulting in:

    01/01/2013 (standard date field, correct)
    01/03/2013 (future calculation - wrong!)
    01/03/2013 (future calculation - correct)

    Which date is set seems to vary - whichever field was updated second, I think (when testing this, in some cases both fields in the example above would be set to 01/02/2013). In Print options, Word is set NOT to update fields before printing - but they update anyway, and they update incorrectly. I cannot figure out why this is happening. I was using this document successfully for quite some time now, but it is broken in the current version of Word for Mac and in Word 2011 on Windows.

    Calculations in one field are apparently affecting other fields with similar calculations, and Word is not respecting the "do not update fields" option when printing. What is maddening is that the calculations work when updated manually - but they update again, incorrectly, as soon as I try to print.

    The code used in the date fields is below. "SET Delay" is the only portion that changes between the two future fields, either set to Delay 1 or 2 for one or two days in the future, respectively. Changing the other variable references (in case Word was keeping variables in memory) didn't seem to make any difference in the outcome.

    {QUOTE{SET Delay 1}
    {SET a{=INT((14-{DATE \@ M})/12)}}
    {SET b{={DATE \@ yyyy}+4800-a}}
    {SET c{={DATE \@ M}+12*a-3}}
    {SET d{DATE \@ d}}
    {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}
    {SET e{=INT((4*(jd+32044)+3)/146097)}}
    {SET f{=jd+32044-INT(146097*e/4)}}
    {SET g{=INT((4*f+3)/1461)}}
    {SET h{=f-INT(1461*g/4)}}
    {SET i{=INT((5*h+2)/153)}}
    {SET dd{=h-INT((153*i+2)/5)+1}}
    {SET mm{=i+3-12*INT(i/10)}}
    {SET yy{=100*e+g-4800+INT(i/10)}}
    "{MM}/{DD}/{YY}" \@ "MM/DD/YY"}
    
    Monday, May 13, 2013 2:47 PM

Answers

  • This is from memory, i.e. I haven't verified the details.

    When you select your document and update all the fields, the result of a REF field such as {DD} is set "as you go along", but the value of the bookmark changes. So after the update, both {DD} fields are as you expect, but the actual value of the DD bookmark is the last one calculated (03 in your example).

    Some fields (including REF fields) always update when you print, regardless of the "Update fields" printing option. But some fields do not - off the top of my head I think that includes both {=} and {SET} fields. So when you print without "Update fields" set, the result of the REF field {DD} will be set to the current value of the bookmark DD, whichis still 03. So both calculated dates appear with {DD} as 03. 

    To solve it, you can either set the fields to update at print, or in this case you can change {DD} to {=DD}, {MM} to {=MM} and {YY} to {=YY} - that means that none of the fields should update and as long as you updated them manually before printing, they should all be consistent.

    (Incidentally, I just checked this on Mac Word 2011 to be as sure as I can, but I haven't gone back to the pre-update version)

    Finally, when fields are used in a Mailmerge, they are always updated, i.e. the behaviour when mailmerging to print is different from the behaviour when you just print without mailmerging.


    Peter Jamieson

    • Marked as answer by dr.nixon Friday, May 17, 2013 2:28 PM
    Monday, May 13, 2013 5:44 PM

All replies

  • This is from memory, i.e. I haven't verified the details.

    When you select your document and update all the fields, the result of a REF field such as {DD} is set "as you go along", but the value of the bookmark changes. So after the update, both {DD} fields are as you expect, but the actual value of the DD bookmark is the last one calculated (03 in your example).

    Some fields (including REF fields) always update when you print, regardless of the "Update fields" printing option. But some fields do not - off the top of my head I think that includes both {=} and {SET} fields. So when you print without "Update fields" set, the result of the REF field {DD} will be set to the current value of the bookmark DD, whichis still 03. So both calculated dates appear with {DD} as 03. 

    To solve it, you can either set the fields to update at print, or in this case you can change {DD} to {=DD}, {MM} to {=MM} and {YY} to {=YY} - that means that none of the fields should update and as long as you updated them manually before printing, they should all be consistent.

    (Incidentally, I just checked this on Mac Word 2011 to be as sure as I can, but I haven't gone back to the pre-update version)

    Finally, when fields are used in a Mailmerge, they are always updated, i.e. the behaviour when mailmerging to print is different from the behaviour when you just print without mailmerging.


    Peter Jamieson

    • Marked as answer by dr.nixon Friday, May 17, 2013 2:28 PM
    Monday, May 13, 2013 5:44 PM
  • When you use multiple sets of these fields in a document, you should at least modify the mm, dd and yy bookmarks and their references, so that each set is unique to the field containing it. Thus, for your second field, you might change them to mmA, ddA and yyA, respectively. That should be sufficient to ensure the correct updating when the 'update before printing' option is off.

    Cheers
    Paul Edstein
    [MS MVP - Word]


    Tuesday, May 14, 2013 2:44 AM
  • As an alternative suggestion, unless you actually need to re-use the values of bookmarks dd, mm and yy, you would probably be better off doing this:

    {QUOTE{SET Delay 1}
    {SET a{=INT((14-{DATE \@ M})/12)}}
    {SET b{={DATE \@ yyyy}+4800-a}}
    {SET c{={DATE \@ M}+12*a-3}}
    {SET d{DATE \@ d}}
    {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}
    {SET e{=INT((4*(jd+32044)+3)/146097)}}
    {SET f{=jd+32044-INT(146097*e/4)}}
    {SET g{=INT((4*f+3)/1461)}}
    {SET h{=f-INT(1461*g/4)}}
    {SET i{=INT((5*h+2)/153)}}
    "{=100*e+g-4800+INT(i/10)}}-{=i+3-12*INT(i/10)}}-{=h-INT((153*i+2)/5)+1}}" \@ "MM/DD/YY"}

    It loses a bit of clarity because it's not so obvious that you are constructing a date, but as far as I know Word always recognises a valid YYYY-M-D format correctly (i.e. gets the month and date the right way around), regardless of regional settings.


    Peter Jamieson

    Tuesday, May 14, 2013 11:12 AM
  • I was trying to keep things as understandable as possible ...

    For the second field (and any subsequent fields incrementing the date by the Delay value), one can also replace all of:
    {SET Delay 1}
    {SET a{=INT((14-{DATE \@ M})/12)}}
    {SET b{={DATE \@ yyyy}+4800-a}}
    {SET c{={DATE \@ M}+12*a-3}}
    {SET d{DATE \@ d}}
    {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}
    with:
    {SET jd{=jd+Delay}}


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Tuesday, May 14, 2013 11:48 AM
  • > I was trying to keep things as understandable as possible ...

    IMO you succeeded.


    Peter Jamieson

    Tuesday, May 14, 2013 3:10 PM
  • I actually tried this prior to posting the question, but was unable to edit the question after posting to note the change. At any rate, it doesn't work by itself - the behavior is the same, all fields end up identical.
    Friday, May 17, 2013 2:29 PM
  • Changing to {=DD} etc. seems to fix the issue - thanks! What I still don't understand is why this was happening to begin with. I used the template for a long time with no issues, made no changes to the file itself but one day it just didn't work any more.

    Changing to force update fields at print works with the original code, IF the fields were not manually updated first. If they were, it breaks. But I can't rely on this across users/computers as "update at print" is not enabled by default in Word.

    Friday, May 17, 2013 2:56 PM
  • > What I still don't understand is why this was happening to begin with.

    Yes, that's always frustrating, but these days I try not to spend too much time looking for reasons why things used to work, particularly if nothing appears to have changed (other than the application of updates) and there are no good clues as to what you might have been doing that made it work before.

    In this case I did try the same fields on an older Mac Word 2011 installation (14.2.0) but there was no difference in behaviour as far as I could see. If you are in a position to post a copy of a Word document that "worked" (i.e. in its pre-update state) somewhere we could access it, I can certainly see what happens here on both old and new versions.

    That said, I don't get this problem, i.e. manual update prior to print with "update fields" does not appear to cause a problem here:
    <<
    Changing to force update fields at print works with the original code, IF the fields were not manually updated first.
    >>


    Peter Jamieson

    Tuesday, May 21, 2013 8:26 AM