Date losing formatting after 2007 mail merge

mikec82 · Feb 7, 2011

In my .xls spreadsheet, run in Excel 2007, I have a column called "New Date". Each cell in this column has the formula =IF(E1<>E2,E2,"") and so on down the line. For an example of the results, a certain cell returns June 2, 2010, based on the custom formatting in E2. However, when I mail merge into Word 2007, it is coming back as 40331. That is how 06-02-10 would display if it did not have any formatting, so I know it's getting lost between two. I've seen online that you can go into Word Options, and under the Advanced Tab, the General section, you can check the "Confirm file format conversion on open". I checked that box. Next time, I was asked if asked how I wanted to access the data source, and I chose DDE, as directed. This just changed the form for all the other fields in my merge. Is there something I'm doing wrong or is there ANY light someone can shed on this? I'd be happy to provide any additional info.

Andrew Poulsom · Feb 7, 2011

Have you tried a MERGEFIELD switch?

http://support.microsoft.com/kb/304387

Smitty · Feb 7, 2011

Welcome to the Board!

Check out: http://support.microsoft.com/kb/320473

You can also use Field Switches.

HTH,

mikec82 · Feb 7, 2011

Andrew, thanks for the reply. According to the link:

Include a date-time picture switch (\@) in the Date field, so that the Date field looks like this: {MERGEFIELD date \@ "MMMM d, yyyy"}

I updated my New_Date (the column in question) field to look like this:

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CMichaelc%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CMichaelc%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CMichaelc%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style>****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CMichaelc%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CMichaelc%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CMichaelc%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style>[FONT=&quot][/FONT]
{MERGEFIELD New_Date \@ "MMMM d, yyyy"}

However, when I preview the results, it still returns 40331.

mikec82 · Feb 7, 2011

I'm sure it's that I'm doing this wrong, but I'm still getting the 40331. According to the second microsoft support link:
Word 2007 and Word 2010

******** type="text/javascript"> loadTOCNode(3, 'resolution'); *********>

In Excel, select the column that contains the ZIP Code/Postal Code field.
On the Home tab, go to the Cells group. Then, click Format, and then click Format Cells.
Click the Number tab.
Under Category, click Text, and then click OK.
Save the data source. Then, continue with the mail merge operation in Word.

When I change the formatting to Text, it changes it back to the 40331.

mikec82 · Feb 7, 2011

Ahh...I see where I was doing it wrong. I was supposed to use the field switch without changing the date to say New_Date. So this was what worked:

{MERGEFIELD date \@ "MMMM d, yyyy"}

Thank you both!

mikec82 · Feb 7, 2011

All right. Scratch my last post. That didn't get it. When I insert:

{MERGEFIELD date \@ "MMMM d, yyyy"}

it is looking for a column named "date" that doesn't exist. It SHOULD be looking at the column New_Date. When I switch "date" for "New_Date", it looks like this:

{MERGEFIELD New_Date \@ "MMMM d, yyyy"}

and update the field, it returns the same old 40331.

Andrew Poulsom · Feb 8, 2011

Try:

{MERGEFIELD "New_Date" \@ "MMMM d, yyyy"}

mikec82 · Feb 8, 2011

I tried putting "New_Date" in quotes, but still the same. Let me tell a little more about the "New_Date" column. The formula it is pulling in is: =IF(E1<>E2,E2,""). Both E2 and a sample from the New Date column (M2) are formatted as "Date->Type->March 14, 2001. I should also point out that the column header is actually "New Date" and not "New_Date". However, when I try

{MERGEFIELD "New Date" \@ "MMMM d, yyyy"}

it comes back with "the merge field is used in the main document, but it does not exist in the data source".

Andrew Poulsom · Feb 8, 2011

Have you tried changing your column header to New_Date? That worked for me with the merge switch I posted. Note that it also worked for the header New Date. The easiest thing to do is click Insert Merge Field and select from the list. Then press Alt+F9 to edit the field.

Date losing formatting after 2007 mail merge

mikec82

Board Regular

Excel Facts

Andrew Poulsom

MrExcel MVP

Smitty

Legend

mikec82

Board Regular

mikec82

Board Regular

mikec82

Board Regular

mikec82

Board Regular

Andrew Poulsom

MrExcel MVP

mikec82

Board Regular

Andrew Poulsom

MrExcel MVP

Similar threads

Forum statistics

Share this page

Date losing formatting after 2007 mail merge

Board Regular

Excel Facts

MrExcel MVP

Legend

Board Regular

Board Regular

Board Regular

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock