Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Date format alters when merged into word document.
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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Date format alters when merged into word document.
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 wrote: 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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Date format alters when merged into word document.
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 "Rhianne" wrote: 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 |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Date format alters when merged into word document.
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 Rhianne wrote: 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 "Rhianne" wrote: 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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The date in a merged word doc is US format source is UK format | Mailmerge | |||
Merged Date format switches not working in Word 2003 | Mailmerge | |||
how to keep date format in merged document same as in data source | Microsoft Word Help | |||
word changes merged access data 'date' format from d/m/y to m/d/y | Mailmerge | |||
Mailmerge in Word, date format in merged doc | Mailmerge |