In a mail merge switch when I insert a mail merge field that is a date like 05/22/2017 I want to convert it to its number equivalent. That is I want 05/22/2017 to convert to 42877. Can anyone help me with this? Thanks
In a mail merge switch when I insert a mail merge field that is a date like 05/22/2017 I want to convert it to its number equivalent. That is I want 05/22/2017 to convert to 42877. Can anyone help me with this? Thanks
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, at:
http://windowssecrets.com/forums/sho...ation-Tutorial
or:
http://www.gmayor.com/downloads.htm#Third_party
In particular, look at the item titled Convert a Gregorian Calendar Date to a Julian Day Number. Do read the document's introductory material.
For your purposes, you'll need to change the 32045 in the field code to 2447064
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Thank you Paul. I will take a look at this. Something tells me it's going to be very complicated.
For the most part, it's a copy/paste exercise. The field code in the tutorial is:
{QUOTE
{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}}
{jd \# ,0}}
You'd copy that from the tutorial into your mailmerge main document, then edit the field code to read:
{QUOTE
{SET a{=INT((14-{MERGEFIELD MyDate \@ M})/12)}}
{SET b{={MERGEFIELD MyDate \@ yyyy}+4800-a}}
{SET c{={MERGEFIELD MyDate \@ M}+12*a-3}}
{SET d{MERGEFIELD MyDate \@ d}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-2447064}}
{jd \# ,0}}
where 'MyDate' is the field name in your data source.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
Thank you again Paul. This looks exactly what I am looking for. Wow, wild calculation but I will do as you recommend and past this formula into my document and change where appropriate with my mail merge field. Really appreciate it.