Search code examples
excelvbams-worddocxmailmerge

How to merge date as date format(18/06/2019) and not number(43682)

I am trying to mailmerge a .docx thanks to a vba excel macro.

It work perfectly for String but when I Want to merge a date as a readable date format it don't.

To fix it i tried to use the following line code but I Always have the date as a number :

I got : 43682 and not the excepted result : 18/06/2019

{ MERGEFIELD MyDate \@ "dddd, dd MMMM yyyy" }

{ MERGEFIELD MyDate \@ "d MMMM yyyy" }

{ MERGEFIELD MyDate \@ "d-MMMM-yyyy" }

{ MyDate \@ "d MMMM yyyy" }

{ MERGEFIELD MyDate \@ MM/dd/yyyy}

Into my excel file the merged data is set as Date format, am I doing something wrong ?

Solution

  • Since you haven't posted your VBA code, it's impossible to know for sure what the issue is. However, it's most likely because your mailmerge is connecting to an open workbook that contains the data. Another possibility is that you have mixed data types in the column concerned, so the mailmerge is treating dates as numbers. Field coding in the mailmerge main document can be used to work around such issues. For example, to convert 43682 to 18/06/2019 you can use a field coded as:

    {QUOTE{SET jd{=2415019+{MERGEFIELD MyDate}}}
{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}" \@ "DD/MM/YYYY"}

    Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac or, if you’re using a laptop, you might need to use Ctrl-Fn-F9); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. The line breaks aren't really needed, but they do make the structure easier to follow.

    For a macro to convert the above into a working field, see Convert Text Representations of Fields to Working Fields in the Mailmerge Tips and Tricks thread at: http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html