Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-23-2018, 11:46 AM
DanOnline DanOnline is offline Adding 9 months to month end date. Windows 10 Adding 9 months to month end date. Office 2016
Novice
Adding 9 months to month end date.
 
Join Date: Oct 2018
Posts: 7
DanOnline is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 10-23-2018, 12:27 PM
macropod's Avatar
macropod macropod is offline Adding 9 months to month end date. Windows 7 64bit Adding 9 months to month end date. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 10-23-2018, 01:00 PM
DanOnline DanOnline is offline Adding 9 months to month end date. Windows 10 Adding 9 months to month end date. Office 2016
Novice
Adding 9 months to month end date.
 
Join Date: Oct 2018
Posts: 7
DanOnline is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 10-23-2018, 01:09 PM
macropod's Avatar
macropod macropod is offline Adding 9 months to month end date. Windows 7 64bit Adding 9 months to month end date. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

As I said:
Quote:
Originally Posted by macropod View Post
look at the item titled Date and Time Calculations In A Mailmerge.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 10-24-2018, 02:08 AM
DanOnline DanOnline is offline Adding 9 months to month end date. Windows 10 Adding 9 months to month end date. Office 2016
Novice
Adding 9 months to month end date.
 
Join Date: Oct 2018
Posts: 7
DanOnline is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 10-24-2018, 03:07 AM
macropod's Avatar
macropod macropod is offline Adding 9 months to month end date. Windows 7 64bit Adding 9 months to month end date. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #7  
Old 10-24-2018, 04:33 AM
DanOnline DanOnline is offline Adding 9 months to month end date. Windows 10 Adding 9 months to month end date. Office 2016
Novice
Adding 9 months to month end date.
 
Join Date: Oct 2018
Posts: 7
DanOnline is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 10-24-2018, 05:07 AM
DanOnline DanOnline is offline Adding 9 months to month end date. Windows 10 Adding 9 months to month end date. Office 2016
Novice
Adding 9 months to month end date.
 
Join Date: Oct 2018
Posts: 7
DanOnline is on a distinguished road
Default

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 }...
Reply With Quote
  #9  
Old 10-24-2018, 01:46 PM
macropod's Avatar
macropod macropod is offline Adding 9 months to month end date. Windows 7 64bit Adding 9 months to month end date. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by DanOnline View Post
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.
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]
Reply With Quote
  #10  
Old 10-24-2018, 02:38 PM
DanOnline DanOnline is offline Adding 9 months to month end date. Windows 10 Adding 9 months to month end date. Office 2016
Novice
Adding 9 months to month end date.
 
Join Date: Oct 2018
Posts: 7
DanOnline is on a distinguished road
Default

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.
Reply With Quote
  #11  
Old 10-24-2018, 03:15 PM
macropod's Avatar
macropod macropod is offline Adding 9 months to month end date. Windows 7 64bit Adding 9 months to month end date. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #12  
Old 10-25-2018, 12:20 PM
DanOnline DanOnline is offline Adding 9 months to month end date. Windows 10 Adding 9 months to month end date. Office 2016
Novice
Adding 9 months to month end date.
 
Join Date: Oct 2018
Posts: 7
DanOnline is on a distinguished road
Default

Thank you, I tried this code and it worked as intended.
Reply With Quote
  #13  
Old 01-19-2021, 08:11 AM
lordtgii lordtgii is offline Adding 9 months to month end date. Windows 10 Adding 9 months to month end date. Office 2016
Novice
 
Join Date: Jan 2021
Posts: 1
lordtgii is on a distinguished road
Default

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?
Reply With Quote
  #14  
Old 01-19-2021, 01:57 PM
macropod's Avatar
macropod macropod is offline Adding 9 months to month end date. Windows 10 Adding 9 months to month end date. Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding 9 months to month end date. Find Date Month charlesdh Excel Programming 11 07-24-2018 06:11 PM
Adding 9 months to month end date. Adding Months TimmiAndrew Excel 2 09-16-2015 10:21 AM
Adding 9 months to month end date. Is it possible to format today's date minus 12 months sarabeck Word 1 07-09-2015 04:53 PM
Adding 9 months to month end date. Counting Number of Months from a static date bremen22 Excel 4 11-25-2013 11:57 AM
Adding 9 months to month end date. Adding months to a Date tsindos Word 4 10-04-2011 11:20 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:55 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft