Mail merge dates

Jeff68 · #1 03-01-2013, 07:58 AM

I can't get the proper date to show up in word after a mail merge. I know there are lots of post on this already but it does not solve my problem.

No matter what format the date is in for excel it comes out as a 5 digit number in word. Other words it changes the date to General.

If I enter the following date: 14 Apr 13

The following appears in word: 41378

How can I change that to the actual date?

The mergefield looks as follows

{MERGFIELD title of column}

It doesn't say date so I can't change it there.

macropod · #2 03-01-2013, 05:13 PM

If your data source contains empty cells (or cells with numbers) in that column, that could account for the behaviour. As a workaround, try changing the data connection method to DDE.

macropod · #3 03-02-2013, 09:59 PM

As a follow-up:

An alternative approach, which doesn't require a change in the data connection method, is to use a field coded as:

Code:

{QUOTE
{SET jd{=2415019+{=INT({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)}}
"{mm}-{dd}-{yy}" \@ "MMMM d, yyyy"}

where 'MyDate' is your mailmerge field's name.

Note: The field brace pairs (ie '{ }') for the above example are created in the body of the document via Ctrl-F9 (Cmd-F9 on a Mac) - you can't simply type them or copy & paste them from this message.

If you'd rather not encode the above by hand, you can download a macro to do it, from: http://www.gmayor.com/export_field.htm#TextToField

Jeff68 · #4 03-11-2013, 07:31 AM

The issue isn't empty cells. I'm running test trials and some cells in my word document are coming out as dates. Others as the 5 digit number that excel see's dates as. I can't find any differences to the ones that are working and the ones coming out as the series of 5 numbers. This problem is driving me crazy. It should be so simple yet just wont work. When it does work its simple to change the date format. Just can't seem to get a date to appear on word.

macropod · #5 03-11-2013, 01:52 PM

With the dates that display correctly, are they formatted correctly, and do any of the 'days' exceed 12?

If not, try a different method of connecting to your data - see the Excel data section of http://www.gmayor.com/mail_merge_lab...th_word_xp.htm

Jeff68 · #6 03-13-2013, 06:28 AM

hey. All dates are 2013 and after. I've tried all the basic connections and none seem to work properly. The only way I seem to get a straight answer is if I change all my cells to TEXT. but that means other users of this program will not be able to make mistakes. It would really be nice for excel to not use the number based code.

macropod · #7 03-13-2013, 01:48 PM

Hi Jeff,

Can you attach a zip file to a post containing the mailmerge document and some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail Merge Duplication of address on merge	RICKY	Mail Merge	1	09-26-2012 03:14 PM
Conditional merge fields in mail merge	Aude	Mail Merge	1	01-06-2012 07:38 PM
From a beginner: Mail Merge using Windows Mail	mhalpern705	Mail Merge	3	12-16-2011 04:58 AM
Saving INDV mail merges During the mail merge	sedain121	Mail Merge	2	10-04-2011 07:52 PM
Mail Merge and GMT/UTC Dates	dev98	Mail Merge	2	05-06-2011 05:02 AM