 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Mailmerge and Fax / June 2006Insert a future date
This may be a dumb question, but I can't figure out how to get Word (2003) to insert a future date. For example, insert the date that is 30 days from the current date. Thanks for any help. I can't believe the time I've spent trying to figure this out. See http://www.gmayor.com/insert_a_date_other_than_today.htm  Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > This may be a dumb question, but I can't figure out how to get Word > (2003) to insert a future date. For example, insert the date that is > 30 days from the current date. > > Thanks for any help. I can't believe the time I've spent trying to > figure this out. Graham, I've used your sample on the linked page and it works great for future dates; however, I can't get it to work properly with negative numbers for past dates. For example, if I change the Delay to a negative number, the merged document gets a calculated date even further in the future than its positive equivalent. Has anyone else run across this and found a solution? I've looked at the code, but before I try a deep step-by-step analysis I want to make sure I'm not reinventing the wheel (not to mention I don't think I'm qualified to necessarily get it right!). Thanks, Bryan > See http://www.gmayor.com/insert_a_date_other_than_today.htm > [quoted text clipped - 4 lines] >> Thanks for any help. I can't believe the time I've spent trying to >> figure this out. I take it you are using the macro? mask = "d MMMM yyyy" ' Set Date format Default = "-30" ' Set default. Will insert the date 30 days in the past? ie 13 December 2005 What do you have for these two settings? Did you checkout the link that has examples of using fields for this? Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Graham, > [quoted text clipped - 27 lines] >>> Thanks for any help. I can't believe the time I've spent trying to >>> figure this out. Graham, No, I'm actually using the Merge Field code rather than the macro. As it turns out, it looks like I pulled my sample from MacroPod's "WORD Date & Time Manipulation; Tips & Techniques" document, from the section "Date Calculations In A Mailmerge". My dates are merged into the document from a SQL 2000 data source. Here's a sample from my document: The above policy will expire at 12:01am on <<Bpol_ExpirationDate>>. In order for the carrier to provide a renewal quote we will need the following items returned to our office no later than {QUOTE {SET Delay 14} {SET a{=INT((14-{MemoDate \@ M})/12)}} {SET b{={MemoDate \@ yyyy}+4800-a}} {SET c{={MemoDate \@ M }+12*a-3 }} {SET d{MemoDate \@ 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)+111 {SET mm{=i+3-12*INT(i/10)}} {SET yy{=100*e+g-4800+INT(i/10)}} {QUOTE{=dd*10^6+mm*10^4+yy \- "00'-'00'-'0000"} \@ dddd, MMMM d, yyyy"}}: I removed the MERGEFIELD entry (on line 3 in his original: {MERGEFIELD MergeDate \@ d/MM/yyyy}) because it resulted in the unprocessed date displaying immediately preceding the calculated date. I modified the display mask on the final line to the format I liked. I've changed the SET Delay line to the following: {SET Delay 14} {SET Delay 7} {SET Delay -7} {SET Delay -14} {SET Delay (-7)} Positive numbers work correctly. Negative numbers don't, with whatever syntax I've attempted. I haven't found any actual samples that show negative numbers being used in this (I was looking for one to check for any special syntax requirements). I hope this clarifies things, let me know if I can provide you with a copy of his doc or anything else. Thanks again, Bryan ______________________ >I take it you are using the macro? > [quoted text clipped - 37 lines] >>>> Thanks for any help. I can't believe the time I've spent trying to >>>> figure this out. Apart from the fact that it should be Mergefield MemoDate and not MemoDate, I can't see anything wrong with that. Does the unaltered code from Macropod's document work correctly? It does here. In case Macropod isn't watching this group, I'll copy your query to him for comment. Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Graham, > [quoted text clipped - 100 lines] >>>>> Thanks for any help. I can't believe the time I've spent trying >>>>> to figure this out. Ok, I've learned a bit more. *(Note that in all my examples, I'm substituting my merge field "MemoDate" for his merge field "MergeDate". MemoDate happens to always merge the current date. I'm using it in my merge templates instead of Word's built-in Date function because if I can get it to work properly I'll know I can perform date calculations on any other dates pulled from our DB). Here's what's happening: Using his unaltered* code produces the following result in my mailmerge document: no later than 13/01/2006Friday, January 27, 2006: I thought I'd found an "oops" in the code when I saw the two dates side-by-side, which is why you saw in yesterday's post I had omitted Line 3, {MERGEFIELD MergeDate \@ d/MM/yyyy}. Doing so corrected the problem of the 13/01/2006 preceding the calculated date, and the displayed calculated date still appeared to be correct: no later than Friday, January 27, 2006: I experimented with different positive delay values and they all worked. I then experimented with negative delays and got strange results. The list below shows my output with various negative delay values (with Line 3 still omitted as mentioned above): (various negative numbers) -1: no later than Friday, December 1, 2006: -2: no later than Wednesday, November 1, 2006: -3: no later than Sunday, October 1, 2006: -4: no later than Friday, September 1, 2006: -5: no later than Tuesday, August 1, 2006: -6: no later than Saturday, July 1, 2006 -7: no later than Thursday, June 1, 2006: -10: no later than Wednesday, March 1, 2006: -12: no later than Sunday, January 1, 2006: -13: no later than Saturday, December 31, 2005: -14: no later than Friday, December 30, 2005: -15: no later than Thursday, December 29, 2005: -16: no later than Wednesday, December 28, 2005: -20: no later than Saturday, December 24, 2005: -21: no later than Friday, December 23, 2005: -22: no later than Thursday, December 22, 2005: -23: no later than Wednesday, December 21, 2005 -24: no later than Tuesday, December 20, 2005: -25: no later than Monday, December 19, 2005: -26: no later than Sunday, December 18, 2005: -27: no later than Saturday, December 17, 2005: -28: no later than Friday, December 16, 2005: -29: no later than Thursday, December 15, 2005: -30: no later than Wednesday, December 14, 2005: -31: no later than Tuesday, December 13, 2005: -32: no later than Monday, December 12, 2005: -33: no later than Saturday, November 12, 2005: -34: no later than Wednesday, October 12, 2005: -35: no later than Monday, September 12, 2005: -36: no later than Friday, August 12, 2005: -37: no later than Tuesday, July 12, 2005: -38: no later than Sunday, June 12, 2005: -42: no later than Saturday, February 12, 2005: -49: no later than Friday, November 25, 2005: -56: no later than Friday, November 18, 2005: -63: no later than Friday, November 11, 2005: -70: no later than Monday, April 11, 2005: -77: no later than Friday, October 28, 2005: -84: no later than Friday, October 21, 2005: If I restore the MemoDate mergefield from Line 3 and try a sampling of dates from the list above I get the following: -1: no later than 13/01/2006Friday, December 1, 2006: -2: no later than 13/01/2006Wednesday, November 1, 2006: -15: no later than 13/01/2006Thursday, December 29, 2005: -28: no later than 13/01/2006Friday, December 16, 2005: -38: no later than 13/01/2006Sunday, June 12, 2005: As you can see, there is no change in the correctness of the date calculations. Good dates are still good and bad dates are still bad; they are all simply prepended with the result of the MemoDate merge field. In MacroPod's document beneath the example for Date Calculations In A Mailmerge, he includes the following note: In the above example, the <<Mergedate>> wouldn't normally display (it does here because this document doesn't use a true mailmerge) and, if the delay was being imported as part of the mailmerge, you could also replace {SET Delay nn} with {SET Delay{MERGEFIELD MergeDelay \# 0}}. I think the MergeDate (or in my case, the MemoDate) field isn't intended to be diplayed, but is somehow to be used in the calculations. Since in my document it's displaying for some reason, it's probably NOT being used in the calculations as intended, and in some cases this results in bad output when a negative delay is used. To rule it out as an issue, I've changed my customized diplay mask at the end of the last line to match his example. No change. Since I don't have a datasource that uses MergeDate as a valid merge field, how can I test his code completely unaltered? Would that fact that I'm using merge fields from a SQL datasource change anything? Should simply replacing his Mergefield with a valid one for my datasource "break" the code? Thanks again for all input and the time spent on this. Bryan ___________________________________________ > Apart from the fact that it should be Mergefield MemoDate and not > MemoDate, I can't see anything wrong with that. [quoted text clipped - 108 lines] >>>>>> Thanks for any help. I can't believe the time I've spent trying >>>>>> to figure this out. I found the solution in one of MacroPod's posted replies to a similarly-frustrated user on the Woody's Lounge forum. I'll repost for the benefit of readers here: "The clue to your problem is in the output format you've specified in [your post]. The fields in the document are region-dependent and are coded to work on systems that are configured to display dates formatted as d/MM/yyyy (short form) or dddd, d MMMM yyyy (long form). Something I'll need to note in the next update. Your 'issue' relates to a system that is configured to display dates formatted as MM/d/yyyy (short form) or dddd, MMMM d yyyy (long form). To obtain the correctly result is as simple as changing the expression 'dd*10^6+mm*10^4+yy' to 'mm*10^6+dd*10^4+yy'. Adding the appropriate date switch will then display the result accordingly." I changed my expression to the alternative he provided and it fixed my problem; all positive and negative delay values now calculate to the correct date. Many users in the USA may want to make a note of this. Thanks again to MacroPod for excellent work and his continuing assistance!! Bryan -------------------------------------------------- > Ok, I've learned a bit more. *(Note that in all my examples, I'm > substituting my merge field "MemoDate" for his merge field "MergeDate". [quoted text clipped - 111 lines] >> In case Macropod isn't watching this group, I'll copy your query to him >> for comment. <snip> Thanks for that. Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > I found the solution in one of MacroPod's posted replies to a > similarly-frustrated user on the Woody's Lounge forum. I'll repost [quoted text clipped - 136 lines] > > <snip> This is more complex than you might imagine, but it can be done. See http://addbalance.com/word/datefields2.htm for information on the different kinds of ways to make a date calculation work. It includes links to pages with fields and an explanation of different macros that can be used instead of fields. SignatureCharles Kenyon Word New User FAQ & Web Directory: http://addbalance.com/word Intermediate User's Guide to Microsoft Word (supplemented version of Microsoft's Legal Users' Guide) http://addbalance.com/usersguide See also the MVP FAQ: http://word.mvps.org/FAQs/ which is awesome! --------- --------- --------- --------- --------- --------- This message is posted to a newsgroup. Please post replies and questions to the newsgroup so that others can learn from my ignorance and your wisdom. > This may be a dumb question, but I can't figure out how to get Word (2003) > to [quoted text clipped - 4 lines] > Thanks for any help. I can't believe the time I've spent trying to figure > this out. Hello, I am also trying to do this (I need to add 14 days to todays date). I picked up a long string of code from one of the guys here which works well on its own however, when used in a mail merge document, the code interferes with the merge fields and stops them working, so no letters are produced at all. We are using Word 2000 v9.0. Does anyone have any experience of this? I should perhaps say that the source data is coming from an AS400 system rather than a created Excel or Access file. Thanks. Regards, Chris > This is more complex than you might imagine, but it can be done. See > http://addbalance.com/word/datefields2.htm for information on the different [quoted text clipped - 10 lines] > > Thanks for any help. I can't believe the time I've spent trying to figure > > this out. What do you mean by 'interferes'? You have to substitute your mergefields and switches as appropriate - see http://www.gmayor.com/insert_a_date_other_than_today.htm Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Hello, > [quoted text clipped - 39 lines] >>> Thanks for any help. I can't believe the time I've spent trying to >>> figure this out. It stopped the mailmerge working. When we deleted the future date code, the mailmerge worked with no problems or errors. The mergefields we are using are pre-determined by our database and although we can use the usual picture switches - caps/upper etc - we can't modify the data any further. I hope I haven't missed the point here! > What do you mean by 'interferes'? You have to substitute your mergefields > and switches as appropriate - see [quoted text clipped - 43 lines] > >>> Thanks for any help. I can't believe the time I've spent trying to > >>> figure this out. The field construction to add 14 days to a mergefield (here Datefield) is {QUOTE{SET Delay 14}{SET a{=INT((14-{MERGEFIELD DATEFIELD \@ M})/12)}}{SET b{={MERGEFIELD DATEFIELD \@ yyyy}+4800-a}}{SET c{={MERGEFIELD DATEFIELD \@ M}+12*a-3}}{SET d{MERGEFIELD DATEFIELD \@ 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)}}{=dd*10^6+mm*10^4+yy \# "00'-'00'-'0000"} \@ "dddd, d MMMM yyyy"} You are asking for trouble if you try to reconstruct that by hand, so download the sample document via the link on the quoted web page and edit the sample code Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > It stopped the mailmerge working. When we deleted the future date > code, the mailmerge worked with no problems or errors. The [quoted text clipped - 58 lines] >>>>> Thanks for any help. I can't believe the time I've spent trying >>>>> to figure this out. I get the impression that the problem could be that the date fields coming across from the AS400 are not treated as dates by Word. If Word sees the field "mydate" as a date, then Word's date switches should function, e.g. if mydate is 13 June 2006, then { MERGEFIELD mydate \@YYYY } should display as 2006, { MERGEFIELD mydate \@M } should display as 6, and so on. This is crucial to the functioning of the fields Graham describes. If Word is not treating your AS400 dates as dates, either another approach is needed or you need to try to get it to do so. I don't know AS400 and don't have one to experiment with here, but a. are the dates in something that is obviously a date field on AS400? b. how are you connecting to the AS400? Do you use an ODBC driver or an OLEDB provider? Do you know if the AS400 driver/provider implements a standard dialect of SQL? Peter Jamieson > Hello, > [quoted text clipped - 33 lines] >> > figure >> > this out. Dear Peter, I note that you are using a field called MYDATE. The date field that is being used is { TIME \@ “dd MMMM yyyy” }. Could that be making it all fall over do you think? The letter date isn't being driven from AS400. Does the 'mydate' function still populate today's date? Regards, Chris > I get the impression that the problem could be that the date fields coming > across from the AS400 are not treated as dates by Word. If Word sees the [quoted text clipped - 57 lines] > >> > figure > >> > this out. > I note that you are using a field called MYDATE I was just using "mydate" as a sample name for a date field, to be substituted by whatever date field name you might have. (cf. Graham's use of "DATEFIELD"). But you don't have one anyway. Using TIME instead of the MERGEFIELD DATEFIELD works fine here and allows the merge, at least on Word 2003 - I haven't been able to check with Word 2000. Peter Jamieson > Dear Peter, > [quoted text clipped - 75 lines] >> >> > figure >> >> > this out. Peter From private correspondence it may have been an issue involving smart quotes in the date switches, as I have sent the Chris a working copy of the field and that works for him. :) Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> >> I note that you are using a field called MYDATE > [quoted text clipped - 100 lines] >>>>>> to figure >>>>>> this out. Thanks Graham. > Peter > From private correspondence it may have been an issue involving smart [quoted text clipped - 105 lines] >>>>>>> to figure >>>>>>> this out. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.