Date format change

[Quartz]

I have a 2003 worksheet created by a second party. One column has dates eg 30/06/2014 which are formatted as Date. However when I use this worksheet with Word mail merge the date displays as 06/30/2014.

I have used the Tezt to Columns approach on the data data to no avail. I have also tried to reformat the mail merge field by right clicking etc.

i have also tried to reformat from Date to Custom, but when I do so and return to any cell the format is back at date.

my last try was to save the worksheet from 2003 to 2010 but no success.

woull appreciate any thoughts on how to get the dd-mm-yyyy format to display on my mail merge result

 

[Macropod]

There is no need to muck around with the Excel data. In any event, changing its format won't affect the mailmerge. To get the date format you want, you can add a formatting picture switch as follows:
• 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 dates will come out like '2 August 2008'.
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

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 can swap the d, M, y expressions around, but you must use uppercase 'M's for months - lowercase 'm's are for minutes.

 

[Baskar]

Hi,

This can also be done in excel, format the date column in excel by using =text(A1,"dd-mmm-yy") and try. It works @ my end.

 

[Macropod]

This can also be done in excel, format the date column in excel by using =text(A1,"dd-mmm-yy") and try. It works @ my end.

Changing the format in Excel does NOT change the mailmerge behaviour.

 

[Quartz]

There is no need to muck around with the Excel data. In any event, changing its format won't affect the mailmerge. To get the date format you want, you can add a formatting picture switch as follows:
• 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 dates will come out like '2 August 2008'.
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

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 can swap the d, M, y expressions around, but you must use uppercase 'M's for months - lowercase 'm's are for minutes.

thanks Macropod. My mistake was using lowercase m instead of M. Your footnote was the clincher.