Date format alters when merged into word document.

[Rhianne]

In the data source (excel) my date column is formatted to show dates as: (e.g)
12 February 2009. However the merge in the word document shows the date as
02/12/09 - which is the way in which Amercians write the date; nothing wrong
with that, but this document is going out in the UK so needs to be understood.

Why is this occurring? How can I rectify it.

Thanks

Rhianne

 

[Peter Jamieson]

It's because by default, Word uses a method to get the data from Excel
that (a) ignores any formatting you imposed in Excel and (b) favours US
format, regardless of your regional settings. And even that is probably
either a simplification or incorrect.

For an explanation of sorts, you may find http://tips.pjmsn.me.uk/t0003.htm
useful

Typically you can fix the formatting problem by applying "date format
switches" to the relevant fields in your mailmerge main document. e.g.,
if your field is called mydate, use Alt-F9 to display the field codes,
look for

{ MERGEFIELD mydate }

and change it to

{ MERGEFIELD mydate \@"D MMMM YYYY" }
or
{ MERGEFIELD mydate \@"DD MMMM YYYY" }

if you want "09" rather than "9" for 9 Feb 2009

Then make sure it works with dates where the day number could be a month
number (i.e. 1 to 12) and the month number could be a day number (i.e. 1
to 12) to ensure they are not reversed.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Rhianne]

Many thanks - this was very helpful.

However it would appear the same problem is occurring with the "salary"
merge. The format is changing from excel e.g £34,000 to £34000.09. I want the
first format in the merge, without the decimal point and a seperation between
the numbers so that is it clear what the figure is. I have had a play about
with merge field but cannot seem to hit upon the correct instruction - I've
tried \@ "£0000", but nothing....I am also having an issue with another date
in the word document. Orginally I had it as a merge field but then altered it
within the document to an insert "date/time" so that it could be updated
automatcially. When I click alt + f9, the merge field is displayed - but when
I merge the document the merge field does not disappear and leave the date
behind....

This is probably really simple, but have never had these issues with merging
before, so have no idea what to do!

Thanks

 

[Peter Jamieson]

Hi Rhianne,

\@ "£0000" is not far off, but \@ is for /date/ formats. For /numeric/
formats, you need \#

In this case, \#£0 would be enough if you want to round the value, e.g.
£12345.67 -> £12346, or

{ =INT({ MERGEFIELD mynumber }) \#£0 }

if the values are positive (all the {} need to be the special field
braces you can enter using ctrl-F9 )

Orginally I had it as a merge field but then altered it
within the document to an insert "date/time" so that it could be updated
automatcially. When I click alt + f9, the merge field is displayed - but when
I merge the document the merge field does not disappear and leave the date
behind....

I suspect the simplest way to solve this is to put the following nested
field code at the beginning of your mailmerge main document (or befoe
the first point where you need the date

{ SET today { DATE } }

then use

{ REF today }
or, as long as you don't have a clash with a field in the data source,
simply
{ today }

wherever you need the date.

If you need a certain format, but always use the same one, put it in the
{ DATE } field, e.g.

{ SET today { DATE \@YYYY-MM-DD } }

If you need different date formats at different points, You'll probably
be better off using the YYYY-MM-DD format in the SET (regardless of the
normal format for your locale)

{ SET today { DATE \@YYYY-MM-DD } }

then use the format you need in each REF:

{ REF today \@"DD MMMM YYYY" }

etc.


Peter Jamieson

http://tips.pjmsn.me.uk