Field code for last day of the month

eduzs · #1 09-02-2020, 08:42 AM

Hi there!
Please, does anyone have a field code that returns:
1) The last day of the last month prior to the current date?
2) The last day of the month of a given date.
I looked at the document DateCalc V2_86, but those field codes is a little complicated to modify.

Thanks.

eduzs · #2 09-02-2020, 09:18 AM

For the 1st question, this seems to work (need to do more tests):

{QUOTE {SET Delay 0}{SET a{=INT((14-{DATE \@ M})/12)}}{SET b{={DATE \@ yyyy}+4800-a}}{SET c{={DATE \@ M}+12*a-3}}{SET d = 1 }{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}-{mm}-{yy}" \@ "d/M/yyyy"}

For the 2nd question, if I change "DATE" to "DOCVARIABLE mydate" it not work, cause in this situation the delay should be count from the 1st day of the next month before mydate, and not the 1st day of the current month.

Charles Kenyon · #3 09-02-2020, 07:29 PM

From Paul's tutorial. Microsoft Word Date Calculation Tutorial
Question 1

Code:

{QUOTE
{SET Subtract 0}
{SET Offset -1}
{SET mm{=MOD({Date \@ MM}+MOD(Offset,12)+11,12)+1}}
{SET yy{=INT({DATE \@ yyyy}+({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)+({DATE \@ d}>30)=1,30,31))-Subtract)}}
"{mm}-{dd}-{yy}" \@ "dddd, d MMMM yyyy"}

Question 2

For the second question, use a Date Picker CC and bookmark it. Pick the date and substitute a reference to the bookmark in the above field for the DATE field. The Offset is set to 0 since it is the same month.

Here is with the bookmark being named "MyDate."

Code:

{QUOTE
{SET Subtract 0}
{SET Offset 0}
{SET mm{=MOD({MyDate \@ MM}+MOD(Offset,12)+11,12)+1}}
{SET yy{=INT({MYDATE \@ yyyy}+({MYDATE \@ 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)+({MYDATE \@ d}>30)=1,30,31))-Subtract)}}
 "{mm}-{dd}-{yy}" \@ "dddd, d MMMM yyyy"}

Here is a temporary link to a document containing the two fields and the DatePicker marked with a bookmark.
Dropbox - deleteme.docx - Simplify your life

Charles Kenyon · #4 09-03-2020, 08:35 PM

Are the fields I provided doing what you want? You may have to change the formatting at the end.

eduzs · #5 09-04-2020, 09:42 AM

I can't download files

.
I managed to reach the 2nd answer by calculating the 1st day of the following month and subtracting 1 day:
Thanks!

Charles Kenyon · #6 09-04-2020, 11:19 AM

Are you saying you can't download any files, or just the one I linked?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word field last month not working within year change	eduzs	Word VBA	4	07-25-2019 04:31 PM
VBA Code to search for field codes with certain text before the Field code and to change style	welcometocandyland	Word VBA	4	02-08-2017 06:53 PM
Word Field Code Displays Prior Month	jadenmyboy	Word	1	09-01-2015 12:29 PM
Formula to subtract one month from due date field in reminder field	ghumdinger	Outlook	1	10-01-2011 12:09 AM