Mergefield date format

  steved 11:43 29 Nov 07
Locked

I am merging some excel dates into a word document. As I want them to appear in UK format I have added the switch \@ "dd/MM/yyyy". Bizarely, although this works if the source date is one which can't be expressed in MM/dd/yyyy format (eg 24th September 2007 will show as 24/09/2007, as 09/24/2007 isn't a valid UK date format), other dates are not being formatted correctly, eg 9th October appears as 10/09/2007.

Any thoughts? This is driving me mad!!

  silverous 13:36 29 Nov 07

I seem to recall something very similar I experienced a few years ago.

That said, I just did a simple test in Excel and Word and by default the dates were appearing correctly in DD/MM/YYYY format in the word document.

Can I ask how the source dates are formatted in Excel? (i.e. what you see in the format, cells dialog with one of the date cells selected).

On my machine it has defaulted to a custom format I have setup of dd/mm/yyyy.

I think this behaviour can depend on the regional settings and date formats set on your PC, and also a date setting somewhere within office.

Let me know re: excel format and we'll take it from there. I suspect in my case I ended up exporting the data to another format e.g. Access/Text File and merging that way to avoid the issue, but can't recall as it was some time ago.

  Taff™ 00:01 30 Nov 07

This sounds familiar as well. Bookmarking for tomorrow!

  Taff™ 08:57 30 Nov 07

Which version of Word & Excel are you using? When you select the data source in Word do you get an option to select the file conversion method?

  steved 10:00 30 Nov 07

I'm using Word & Excel 2002, and am merging into emails if that makes any difference. There's no option to choose the conversion format, I just select the data source (excel file). Dates in Excel are formatted dd/mm/yyyy.

I have now found a solution from the Microsoft website using DDE - select 'confirm conversion at open' in Options, then when prompted to select a data source you chose 'MS Excel Worksheets via DDE'. This works fine but I still don't understand why changing the format in the field codes didn't work?

  Taff™ 10:48 30 Nov 07

You`ve got the right answer and the one I use however I find that using the "MS Excel Worksheet via converter" works better in some cases. Particularly true if you have several sheets in the workbook because you select the specific sheet.

The root cause is that the formatting of the spreadsheets can be different as silverous points out. You can overome this by copying the column of dates and using paste special to put them back as values only, then format the column appropriately but it`s still hit and miss.

Different versions of Word use different default file conversions and manually choosing the option often sorts out the problem.

  steved 11:19 30 Nov 07

That's excellent - thanks for your help!

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now