Merging dates from Excel into Word

Posted in: Digital Data & Technology Group, IT Literacy

If you mail merge Excel files into Word documents, you may find this blog very useful.  Someone recently raised the issue of the format of a date merged from an Excel file into a Word document. The problem is that Word displays the date in the American month/day/year format, even when it shows as day/month/year in Excel.

I have found two solutions to this issue.

Solution using Excel

The first is to split the date into its component parts in Excel and merge each part into the Word document.

To do this I create 3 new columns, with the headings Day, Month and Year (not very imaginative, I know!) and use the =DAY, =MONTH and =YEAR functions (e.g. if the date was in cell A2, write the 3 functions into separate columns: =DAY(A2), =MONTH(A2) and =YEAR(A2)).

Then in Word, to place a date, I simply insert each field and type in the separators, e.g. <Day>/<Month>/<Year>.

Solution using Word

Select the date field in the Word document, right click on it and select ‘Edit Field’ to see the ‘Field’ window. Click on “Field Codes” at the bottom-left of the window to see the advanced field properties.

Click at the end of the text in the ‘Field codes’ box (it probably says MERGEFIELD Date) and type \@ followed by the format you want within speechmarks, e.g.:

\@ “dd/MM/yyyy” to see a date appear like 01/02/2016

\@ “dddd d-MMM-yy” to see it appear like Monday 1-Feb-16

Use CAPITAL M for the month (m is used to show minutes). From the examples above you've probably already noticed/worked out that:

  • d or M would give numbers 1-9, then 10 upwards
  • dd or MM would give numbers 01, 02, etc.
  • ddd or MMM would give Sun to Sat or Jan to Dec
  • dddd or MMMM would give Sunday to Saturday or January to December
  • yy would show a 2-digit year and yyyy would show a 4-digit year.

 

Posted in: Digital Data & Technology Group, IT Literacy

Respond

  • (we won't publish this)

Write a response