Date format problem in Mail Merge - Word 2010

Very helpful answer. 

Question: is there a similar fix for merging Currency amounts? When I merge a currency that ends in Zero, for example $5,355.40, it omits the final zero on the merge, Showing as $5355.4 (It also ommits the comma, which I would love to include as well.)

Thanks!

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.

Mark - this was posted a couple of years ago... but I feel your pain.

My issue was (I think) caused by some of the data fields in the underlying data range being blank - this was deliberate as I wanted to display a blank in the merged document where the field was empty.

For some reason this resulted in the mergefield switches for date formats not functioning as they should - exactly as you describe.

My solution was to change the underlying merge data in the Excel spreadsheet to a text string via the =TEXT(value, format_text) formula.

The merge field switches were still able to reformat the data even though, ostensibly, the data now consisted of a text string and not a date value.

Hope this helps you or someone else.

Changing the cell format in Excel is not a reliable way of handling mixed data types. An empty cell in a column recognised as having a text or date format will be output as a blank, regardless of any formatting switches that may or may not be used in the mergefield. It's especially when you start mixing numbers and dates in a column that you start having trouble with the output.

If you have a column with mixed data types in which the only 'numeric' types are dates, but they're appearing in the mailmerge output as numbers, you can turn them back into dates using a field coded as:

{QUOTE

{SET Delay{=INT({MERGEFIELD MyDate})}}

{SET jd{=2415019+Delay}}

{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)}}

"{dd}-{mm}-{yy}" @ "dddd, d MMMM yyyy"}

Note1: The field brace pairs (i.e. '{ }') for the above example are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. The spaces represented in the field construction are all required. The line breaks shown in the code aren't necessary - they're just being used to make the code structure easier to follow.

Note2: As presented, the field code assumes Windows UK/Aus Regional Settings. For US Regional Settings you need to swap the {dd}-{mm} order on the last line. The final date format switches can be any of those indicated in my original 'Answer' post.

Note3: The above field code is derived from the 'Importing Date and Time Values From Excel and Access' example in my Microsoft Word Date Calculation Tutorial, at:

http://windowssecrets.com/forums/showthread.php/154368-Microsoft-Word-Date-Calculation-Tutorial

or:

http://www.gmayor.com/downloads.htm#Third_party