Date format in Word merge

[Jeff H]

I’m doing an address book with Word’s mail merge. I’ve used the same merge doc in the past and it works fine with the Excel spreadsheet which I export from Google Contacts as CSV. I apply minor reformatting with a macro and save the finished source as .xslm.

But I’m adding birthdays and anniversaries now and I can’t get the dates to format. Using the field code below it still displays as the date serial number. Am I doing something wrong or is there another setting I need to change? For example, in order to get leading zeros in zip codes, my macro sets the zip field's number format to "00000" in Excel before applying the field code switch \#"00000" in Word. But in this case the date fields are already formatted correctly.

I’m using this field code:

Code: Select all

{ MERGEFIELD “Birthday” \@ “M/d/yyyy” } 

[HansV]

What are you using as data source now? A .csv file, an Excel workbook, or Outlook contacts?

[Jeff H]

Excel xslm workbook. It's what I've used before for this address book, and everything is working except the date format.

[HansV]

That's strange. Could you attach a sample Excel workbook?

[Jeff H]

You must be a miracle worker! I made a copy and stripped away all the data except four cases where I had added dates. When I plugged that into the Word merge doc, the dates worked. Then I switched back to the original and they worked too!!

I hadn't changed anything about the original xlsm file and before posting this thread I had started the whole process twice.

Regardless, it works now. And I give you credit for it!

Thanks Hans!

[HansV]

It's called "The Power of Posting"

[Jeff H]

Indeed! Similar things have happened here in the past, and that doesn't even count the times I figure out my own answers while trying to explain a problem.

[Charles Kenyon]

Make sure that the first record in your worksheet has the data set the same way.

[Jeff H]

As it happens, I only added dates to records that were further along in the list.
But I think maybe you mean that since I had not used dates before, it's possible that the date columns in the csv and Excel were not specifically formatted for dates. Is that right?
Maybe in the macro that reformats the csv, I should add a line that specifies the date format I want in the columns that might need it. I'll try that.

Thanks Charles.

[Jeff H]

Ok, I just added those lines to pre-format the date columns and ran the whole thing again. The pre-formatting didn't work, but then I redid it by closing the Excel workbook after updating it and before running the Word merge. That worked.

Could it be that it's better to have the data source closed before linking it to the merge doc?

[HansV]

That's probably better in general.