Date format problem in Mail Merge - Word 2010

Paul - I am doing a merge from my newly adopted business system Dear Inventory. I finally figured out all the switches but it seems that I am getting almost random switching of the month and day when it renders to pdf. Dear (overseas) support claims that the PDF engine is doing it based on Canadian format or something to that effect. Do you have any idea what they are talking about and/or ideas to work around this?

If you are seeing 33210, the problem is almost certainly a result of having "mixed data types in the column," as mentioned elsewhere in this conversation.

But something that may affect whether you see that problem is whether or not the source workbook is open in Excel when the Word mail merge main document connects to it (e.g. when you make the connection, or when you open a document that is connected to that source.

FWIW a few years ago I tried to pin down as many factors as I could that would result in the "33210" behaviour, but there are really a lot of possibilities. So all I can say is that if the workbook is open when you open the mail merge main document or attach the data source, you are more likely to see a date as "33210" in Word in the case where you have mixed data. If the workbook is closed, you are less likely to see that problem.

In this case, one thing that can trigger that is that there is *one* cell in the first 8 cells in the column (below the column header) that contains something that Excel considers to be text. So it is important to ensure that you are seeing those 8 cells, then to look carefully at what is in them.

For example, even if everything looks like a date, it is possible that one of them has been entered "as text". e.g. if someone typed a ' at the beginning of the value, then Word will consider the cell to be "text". You may be able to spot that situation because, by default, Excel will align text on the left and dates on the right.

(Even the "8 cells" thing is down to a default value - although it is probably unusual to do so, that value can be changed in the Windows registry).

I have the same problem, my Excel spreadsheet has two date fields "qualification date" and "expiry date". "qualification date" is a mandatory field, "expiry date" is an optional field and therefore some rows have null values.

All instances of "qualification date" display correctly and format such as '@ "dd-MMM-yyyy"' works correctly.

However for "expiry date",  Word assumes the format to be "MM/dd/yyyy" unless the value of the first two positions is greater than "12" in which case it uses "dd/MM/yyyy" as the format.  My workaround is:

{SET ED1 { MERGEFIELD expiry_date}} { IF { ED1 @ "dd" } > "12" . { ED1 @ "dd-MM-yyyy"} . {ED1 @ "MM-dd-yyyy"} }

To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:

1. select the mergefield;
2. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
3. edit the field so that you get {MERGEFIELD MyData # $,0.00} (or whatever other numeric format you prefer - see below);
4. position the cursor anywhere in this field and press F9 to update it.

Note 1: The '# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:

• # 0 for rounded whole numbers

• # ,0 for rounded whole numbers with a thousands separator

• # ,0.00 for numbers accurate to two decimal places, with a thousands separator

• # $,0 for rounded whole dollars with a thousands separator

• # "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values

Note 2: The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

If you use a final ';' in the formatting switch with nothing following, (eg # "$,0.00;($,0.00);") zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.

Note 3: If you use a decimal tab or right-aligned tab to align the values, wrap the switch in quotes (i.e. # "$,0.00") and insert a tab into the field code after the $ sign, you can have the values output with the decimal alignment occurring after the $ sign.