#1
|
|||
|
|||
Adding 9 months to month end date.
Hello all,
My problem is relatively simple on paper. I have a word template that is populated by mail merge collected from a database, one of these fields generates a date. I need to add 9 months to this date. The date will always be the last date of that month. I have looked around on the internet and what not and seen Macropods workings of how to add a number of days to a field, but the number of days in a 9 month period will vary. Is there an alternate way in which I could generate this field. My experience with mail merge is quite limited as I don't use it with any great frequency. But when I looked at prior threads relating to adding day amounts it seemed rather complex. Instead, my mind gravitated towards a solution that took advantage of the fact that I would only have 12 possibilities per a year, 31 Jan, 28 Feb so on and so forth. (discounting leap year) would it be possible to use an IF function that takes advantage of the fact that there are only 12 possible inputs, and 12 outputs per a year to simply match them together? I.e. 31 January becomes 31 October. { IF {DATABASEDATE} = "31 January" "31 October" "" } where DATABASEDATE is a substitute for the field that populates from the database with the date. However, the mailmerge uses a single field which I guess would create issues due to the year being contained within, the use of an if formula would require a replication would it not. As the field 31 Jan 2017 would be read differently to 31 Jan 2018 and so on. Would be at most 24 combinations as the dates are from now onwards, and the template could be updated every few months as needed. Presumably, the more formulas included greater the period between each change. Is there an alternative way of doing this? Would the IF formula approach work? |
#2
|
||||
|
||||
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, in the 'Sticky' thread at the top of the Word forum:
https://www.msofficeforums.com/word/...-tutorial.html In particular, look at the item titled Date and Time Calculations In A Mailmerge. Do read the document's introductory material.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thank you for the link.
Would the code provide with "Calculate a lease expiry date, using n months delay" be what I am looking for? As I would effectively define the N amount of months as 9? I'm afraid I don't have access to word on this PC, nor the database to test. |
#4
|
||||
|
||||
As I said:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Hi,
I have tried using the field given under "Calculate the day & date of the last or nth-to-last day of a given month" With the ‘DATE’ expressions in the original fields to my date field. And the options set to subtract 0 offset 9. The result is the month 9 months in advance but the date is not always the last date of the month. I.e. 31 Jan 18 generates 30 Oct 18. 28/2/18 generates 30/11/18. 31/3/18 to 30/12/18 and so on. Is there a way around this? |
#6
|
||||
|
||||
The field code under the heading Calculate the day & date of the last or nth-to-last day of a given month is not applicable to what you said you were trying to do. Is there a reason for not adapting the field code found under the heading Calculate a day, date, month and year, using n months delay?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
I tried using Calculate a day, date, month and year, using n months delay however it does not give me the last day of that month. I.e. 31 January 18 became 24 October 2018. Whereas the Calculate the day & date of the last or nth-to-last day of a given month gives 30 October 2018.
An alternative that I thought of, was to use the Calculate a month and year, using n months delay code to add 9 months, the end result would read "October 2018" and so on. But the member of staff could add the date in themselves. Input => Desired output 31 January 2018 31 October 2018 28 February 2018 30 November 2018 31 March 2018 31 December 2018 30 April 2018 31 January 2019 31 May 2018 28 February 2019 30 June 2018 31 March 2019 31 July 2018 30 April 2019 31 August 2018 31 May 2019 30 September 2018 30 June 2019 31 October 2018 31 July 2019 30 November 2018 31 August 2019 31 December 2018 30 September 2019 |
#8
|
|||
|
|||
The IF alternative that I spoke of would be something like:
{IF {MERGEFIELD Date \@ MM} ="01" "31 October 2018" "" \*MERGEFORMAT }{IF {MERGEFIELD Date \@ MM} ="02" "30 November 2018" "" \*MERGEFORMAT }... |
#9
|
||||
|
||||
That's probably because you haven't addressed the internationalisation issues discussed in the tutorial's Introduction - which my first reply also directed your attention to.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
Sorry but I'm not quite sure I understand which section you mean specifically.
I've already read through the Date and Time Calculations in a mailmerge section and adapted the code given with 'MERGEFIELD Date' instead of DATE. I've also read through the introductory notes and date field basics. I refreshed the field as part of my testing and ran a mock mail merge using the month-end dates. It was during that testing that I encountered the aforementioned problems. We already use a DD MM YY system for example. From what I've read the only real change that is required is the above mail merge change, so my code is essentially the one in the DateCal V2_86 document with DATE replaced with MERGEFIELD Date. |
#11
|
||||
|
||||
All you need for the field code is:
{QUOTE {SET Offset 9} {SET mm{=MOD({MERGEFIELD Start_Date \@ MM}+MOD(Offset,12)+11,12)+1}} {SET yy{=INT({MERGEFIELD Start_Date \@ yyyy}+({MERGEFIELD Start_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)>0, 30,31)))}} "{dd}-{mm}-{yy}" \@ "d MMMM yyyy""} where 'Start_Date' is the field name in your data source.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
Thank you, I tried this code and it worked as intended.
|
#13
|
|||
|
|||
Hi Macropod,
I've been using your beautiful formulae witrhin your tutorial. POroblem i currently face is that using your Calculate a day, date, month and year, using n weeks delay i am unable to get the result to format into the format whic i require. I assume that this is beacuase the result is treated as text. Do you have a hack for that? |
#14
|
||||
|
||||
The output format is controlled by the formatting switches at the end of the field code. This is explained under the tutorial's heading DATE/TIME field syntax.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Find Date Month | charlesdh | Excel Programming | 11 | 07-24-2018 06:11 PM |
Adding Months | TimmiAndrew | Excel | 2 | 09-16-2015 10:21 AM |
Is it possible to format today's date minus 12 months | sarabeck | Word | 1 | 07-09-2015 04:53 PM |
Counting Number of Months from a static date | bremen22 | Excel | 4 | 11-25-2013 11:57 AM |
Adding months to a Date | tsindos | Word | 4 | 10-04-2011 11:20 PM |