MailMerge format as date not working

Question

I have a word MailMerge document that is linked to an Excel spreadsheet for the data. In there, I have two columns of dates (formatted as Short Date in Excel).

When trying to use these fields in my Word MailMerge document, I can't get the dates to appear as dates...instead it shows the "Excel" date, of say 42156 instead of 6/01/2015.

I've looked around and tried that, but it doesn't seem to be working. Here's the text in Word after pressing ALT+F9:

{ MERGEFIELD Start_Date \@"d,MMMM,YYYY" }

I've played with other versions \@"dd,MMM,yyyy" but nothing seems to catch.

What might I be overlooking or not doing?

(Screenshot of the text in Word):

@bibadia - Thanks for your comments! I re-read that thread, and was going to try your suggestion...then I made sure the Excel formats are correct, and when I opened my MailMerge document, the dates are showing correctly now! Thanks for your tips and thoughts, helped me out! — BruceWayne

Unknown Unknown · Accepted Answer · 2016-06-11T10:39:58

The question you reference was originally about a different issue, which is to do with the reversal of day and month numbers when an OLE DB provider is involved in getting the data.

The usual problem when you are seeing "numeric Excel dates" is that you have mixed data types in the first few rows of the relevant column in Excel and the OLE DB provider (or possibly the ODBC driver) has determined that the column is "Numeric". There is a fairly detailed description of this in an archived web page here.

So arguably the best aolution is to fix the data, if you have control over it.

Otherwise, one of the responses in the thread you reference has a set of field codes that will reconvert the Excel "date numbers" into a date that Word can work with. A problem with that is that if the column does have dates formatted as text, they will have been lost as the provider/driver will convert text values to the number 0.

The response is the one by "macropod" that has the following field coding, which I've altered to fit your stated requirement. All the {} have to be the special field code brace pairs that you can enter using ctrl-F9 in Windows versions of Word:

{QUOTE
{SET Delay{=INT({MERGEFIELD Start_Date})}}
{SET jd{=2415019+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,MMMM,yyyy"}

(Personally I think it is better to use {yy}-{mm}-{dd} in the last line as it appears to cope better with localisation issues)

MailMerge format as date not working

1 Answers