MAIL MERGE PROBLEM

[Greg]

Dear all,

I have all but finished a large mail merge (Excel 2010 to Word 2010) but one problem remains.

The Excel spreadsheet has a column containing the time of an event (i.e. hours and minutes) which I want to merge into a Word document in simple 12 hour format (e.g. 10:04 am). However, the merged information appears in the Word document as 1/1/1900 10:04:00 pm.

I want to suppress or remove the figures "1/1/1900". I have tried just about everything I can think without success.

I expect that this kind of merge is very common and yet I have been unable to find a solution so far. Can anyone help?

This is becoming urgent and all help will be gratefully received.

Regards,

Greg.

[macropod]

To get the time format you want, you can add a formatting picture switch as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name;
• delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "h:mm am/pm"}.
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

[Greg]

Dear Macropod,

I followed your suggestion albeit with slight changes: { MERGEFIELD OffenceTime \@ "[h]:mm am/pm" \*Lower }.

I had in fact tried this before but it made no difference.

The DDE method is one solution but it causes Word to crash each time I use it. Do you know another way?

[macropod]

That it 'made no difference' and using DDE results in a crash suggests a fault in your Word installation. Have you tried repairing the Office installation (via Programs & Features > Microsoft Office > Change in the Windows Control Panel)?

[Greg]

I have just tried repairing Office but it makes no difference. I even tried saving the spreadsheet as a 2003-2007 document but that made no difference either.

None of the other merge fields cause any problem at all. It's only "time" that causes a problem.

A Google search shows that many others are having the same problem.

[macropod]

The underlying cause of the issue is that your 'time' cells have a value greater than 1, giving them a date of 1/1/1900, plus the time of day. Without that, you'd get the correct result. Perhaps the simplest solution at this stage is to edit the data source. For example, if you type '1' into any empty cell then select the time range and use Paste Special>Values:Subtract, your times will remain the same but they'll lose the extraneous date component and the mailmerge will work correctly.

[Greg]

That seems like a simpler solution than the one I discovered.
I inserted a new column in the spreadsheet and gave it the name "Time 2".
Then using the Text to Columns Wizard in the Data tab, I eliminated the 1/1/1900 date from the Time 2 column.
I then deleted the original Time column and used only the new Time 2 column for merging. It works but I'll try your method when I get back to the office.