Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date problem when merging
When I try to merge an excel table to word or Publisher, the date format
changes to US format. So I select the data source and open it to edit, the date format changes?? Any suggestion? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
date problem when merging
Hi Marie,
To get the date format you want, you can add a formatting picture switch in Word. To do this: .. select the mergefield; .. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name; .. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include: . \@ "dddd, d MMMM yyyy"; . \@ "ddd, d MMMM yyyy"; . \@ "d MMM yyyy"; . \@ "dd/MMM/yyyy"; . \@ "d-MM-yy"; Note: you must use uppercase 'M's for months. .. position the cursor anywhere in this field and press F9 to update it; .. run your mailmerge. Not familiar with Publisher, sorry. -- Cheers macropod [MVP - Microsoft Word] "Marie" wrote in message ... When I try to merge an excel table to word or Publisher, the date format changes to US format. So I select the data source and open it to edit, the date format changes?? Any suggestion? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
date problem when merging
Thanks, I'll try that, but it seems to me an excel problem as the merge field
itself shows as US date format when the excel table is formatted OZ. That is, when I look at the Excel table it is 26/12/2008 and when I edit the merge table it is 12/26/2008. I just can't work it out. "macropod" wrote: Hi Marie, To get the date format you want, you can add a formatting picture switch in Word. To do this: .. select the mergefield; .. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name; .. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include: . \@ "dddd, d MMMM yyyy"; . \@ "ddd, d MMMM yyyy"; . \@ "d MMM yyyy"; . \@ "dd/MMM/yyyy"; . \@ "d-MM-yy"; Note: you must use uppercase 'M's for months. .. position the cursor anywhere in this field and press F9 to update it; .. run your mailmerge. Not familiar with Publisher, sorry. -- Cheers macropod [MVP - Microsoft Word] "Marie" wrote in message ... When I try to merge an excel table to word or Publisher, the date format changes to US format. So I select the data source and open it to edit, the date format changes?? Any suggestion? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
date problem when merging
Hi,
when you look at the table you are seeing the Format, in this case DD/MM/YYYY. when you move into the cell you are seeing the way Excel is actually storing the data which is related to the operting system setting. If you want to get them both in sync either - 1. change window regional date settings or 2. change the cell format to MM/DD/YYYY. If this helps, please click the Yes button Cheers, Shane Devenshire "Marie" wrote: Thanks, I'll try that, but it seems to me an excel problem as the merge field itself shows as US date format when the excel table is formatted OZ. That is, when I look at the Excel table it is 26/12/2008 and when I edit the merge table it is 12/26/2008. I just can't work it out. "macropod" wrote: Hi Marie, To get the date format you want, you can add a formatting picture switch in Word. To do this: .. select the mergefield; .. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name; .. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include: . \@ "dddd, d MMMM yyyy"; . \@ "ddd, d MMMM yyyy"; . \@ "d MMM yyyy"; . \@ "dd/MMM/yyyy"; . \@ "d-MM-yy"; Note: you must use uppercase 'M's for months. .. position the cursor anywhere in this field and press F9 to update it; .. run your mailmerge. Not familiar with Publisher, sorry. -- Cheers macropod [MVP - Microsoft Word] "Marie" wrote in message ... When I try to merge an excel table to word or Publisher, the date format changes to US format. So I select the data source and open it to edit, the date format changes?? Any suggestion? Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
date problem when merging
This is weird and I can't work it out (but it works and if anyone has an
explaination -great). I have tried everything, from formatting at the excel level and at the Word mailmerge field. Nothing worked. The regional settings were correct and the excel cell formats were correct. I tried changing the dates in excel eg one column of dates I left formatted in OZ and changed the dates to eg 1/24/2008. left the other column 24/1/2008. In the mail merge edit participant window both columns showed as US format 1/24/2008. Then I noticed if I input just one date in the column to US ie 1/24/2008 (no change of formatting), the other dates all appear OZ. So now I add a dummy row (can be anywhere in the column) and all the dates look exactly as they do on the Excell sheet. Does the above explaination make sense the way I have written it? Anyway, it works, but how and why???????? "Shane Devenshire" wrote: Hi, when you look at the table you are seeing the Format, in this case DD/MM/YYYY. when you move into the cell you are seeing the way Excel is actually storing the data which is related to the operting system setting. If you want to get them both in sync either - 1. change window regional date settings or 2. change the cell format to MM/DD/YYYY. If this helps, please click the Yes button Cheers, Shane Devenshire "Marie" wrote: Thanks, I'll try that, but it seems to me an excel problem as the merge field itself shows as US date format when the excel table is formatted OZ. That is, when I look at the Excel table it is 26/12/2008 and when I edit the merge table it is 12/26/2008. I just can't work it out. "macropod" wrote: Hi Marie, To get the date format you want, you can add a formatting picture switch in Word. To do this: .. select the mergefield; .. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name; .. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include: . \@ "dddd, d MMMM yyyy"; . \@ "ddd, d MMMM yyyy"; . \@ "d MMM yyyy"; . \@ "dd/MMM/yyyy"; . \@ "d-MM-yy"; Note: you must use uppercase 'M's for months. .. position the cursor anywhere in this field and press F9 to update it; .. run your mailmerge. Not familiar with Publisher, sorry. -- Cheers macropod [MVP - Microsoft Word] "Marie" wrote in message ... When I try to merge an excel table to word or Publisher, the date format changes to US format. So I select the data source and open it to edit, the date format changes?? Any suggestion? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging Date Columns | Excel Worksheet Functions | |||
Merging data problem. | Excel Discussion (Misc queries) | |||
Mail Merging Dates from Excel - Word Simple Problem! | Excel Discussion (Misc queries) | |||
merging columns - one date, one time | Excel Worksheet Functions | |||
problem with date format when merging | Setting up and Configuration of Excel |