My date format scramble between italian to english VBA word excel

Posted on


I did a macro to extract a date from a word document, and put in excel.
I have a date picker content control in word.
My locale in my PC is set to italian DD/MM/YYYY, my date picker word locale is set to Italian DD/MM/YYYY. on my date picker I see the date as e.g. 08/06/2017.
Now I use:

Activeworkbook.Sheets("Sheet1").Range("A1") = objwdDoc.ContentControls(12).Range.Text

but i get 06/08/2017
I tried:

Activeworkbook.Sheets("Sheet1").Range("A1") = format(objwdDoc.ContentControls(12).Range.Text, "DD/MM/YYYY")

But i get the date like 06/08/2017

if I use:

Activeworkbook.Sheets("Sheet1").Range("A1") = format(objwdDoc.ContentControls(12).Range.Text, "MM/DD/YYYY")

I actually get the right date

I check the vba code using a variable like:

date1 = format(objwdDoc.ContentControls(12).Range.Text, "MM/DD/YYYY")

    Activeworkbook.Sheets("Sheet1").Range("A1") = date1

and in debug my date1 variable is correct: 08/06/2017

When I check in excel A1 tho, I get 06/08/2017 and that cell is formatted like “short date, dd/MM/YYYY”)



There are two ways I get around the issue with dates (irish date format is the same as Italian). This is due to the fact that American format is MM/DD/YYYYY and sometimes when it can be either it goes back to basics.
To prevent it happening I do the following (where I can)

  1. To prevent the excel sheet corrupting the format I convert it on read to yyyyMMdd and save as integer.
  2. To enforce the correct look when writing back to excel I use the german format, which people can read and understand but excel doesn’t recognise on non german setup as a date, i.e. DD.MM.YYYY

It is not so much of a solution as a work around.

Leave a Reply

Your email address will not be published. Required fields are marked *