Inconsistent behavior of MS Word mail merge depending on open/closed state of data source MS Excel

Question

I am doing a mail merge in MS Word based on an Excel source.

I used the Wizard to set up the source and it successfully imports the Excel data to MS Word.

However, the formatting of the date is absolutely wrong and depends on whether Excel is open when the Word document is opened.

Description of the source:

where the dates are formatted according to the French dating system (Format Cells>Number>Dates>*dd-MM-yyyy). My Operating System has a French localisation, my MS Office is installed in French, so there is (read: should be) no conflit of regional localisation. Problem remains when selecting "dd-MM-yyyy".

Output in MS Word when Excel source is open:

• With Alt+F9 enabled

• When displaying the results

Output in MS Word when Excel source is closed:

• When displaying the results:

So, my question is: is it a normal behavior? Is there a work-around?

The issue is that I need to have the Excel source open when I open the Word document because it is part of a workflow (I created an hyperlink in Excel that, when clicked, opens the Word document - see below)

It gest worse.

When I use the same date-time switch on both Mergefields "Entrée" and "Sortie", one is in the European format and the other is in the US date format (Excel source closed):

Hypothesis 1: the date-time switch actually does not do anything and actually uses the US date format (since 25 cannot be a month, it must be a European format). 

Testing hypothesis 1: having the second date-time switch as MM-dd-yyyy:

It works (both display European format), but it does not make sense because, for the same date format in the Excel source between “Entrée” and “Sortie”, I have two different date-time switches. 

It gets even worse.

Let us now go to the second record which has the same date format as the first record (I applied the style to the whole column in Excel). Now, the date-time switch displays it in the American format (MM-dd-yyyy). So let us switch back to the European format for the second switch:

… and now I have the same switch, but two different outcomes… one in the US format (with a switch in US date format) and one in the European date format (with a switch in the European date format)!

So, to sum up:

With same source date format, same date-time switch: different date display between “Entrée” and “Sortie”

With same source date format, different date-time switch: same date display

Keeping the date-time switches as they are, changing the record changes the display of “Entrée” and “Sortie”

What am I missing??

Thank you for your help.

EDIT: I delete the security check for the SQL query in the registry (https://support.microsoft.com/en-au/help/825765/you-receive-the-opening-this-will-run-the-following-sql-command-messag) and the minimum working example (see below) now pulls the dates correctly. However, my previous problem remains. In conclusion, it seems that it is file-specific. How can I ensure that all the options are the same?

You can find the Minimum Working Example here. The password is "mail_merge".

Answer 1

What is the password for the zip file?

Answer 2

Oh stupid me ! The password is "mail_merge"