title: Formatting Dates & regional setting Excel VBA
body: <p>This is super frustrating and it doesn't make sense to me.</p>
  
  <p>This is in Excel 2010.</p>
  
  <p>I've recorded a macro to format some data, containing date (DD/MM/YYYY). I import it, Excel sees it as text. So I use the "text to date" macro I have recorded, store into a VBA sub. </p>
  
  <p>Here's the recorded macro:</p>
  
  <pre><code>Sub DateFormatting()
      Sheets("Donnees").Select
      Columns("H:H").Select
      Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
          TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
          Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
          :="/", FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
  End Sub
  </code></pre>
  
  <p>When I import my data, at first I have this. You can see it's seen as TEXT by Excel because it is left-aligned (Column H and I confirm it actually is):</p>
  
  <p><img src="https://i.stack.imgur.com/NBzLw.png" alt="enter image description here"></p>
  
  <p>Then I run the macro, and get this (column H):</p>
  
  <p><img src="https://i.stack.imgur.com/yjXTZ.png" alt="enter image description here"></p>
  
  <p>You can see Excel sees it as Date, as it is right-aligned. If I convert it to "number" I see the underlying serial, as expected. So one would think it's fine. But it's really not:</p>
  
  <p>If I run the macro AGAIN (and I would, because more data will be added to it later, so I need to make sure the newly imported data at the bottom of these will be correctly formatted as well), I get this:</p>
  
  <p><img src="https://i.stack.imgur.com/2LN2G.png" alt="enter image description here"></p>
  
  <p>So basically it changed the format from DD/MM/YYYY (which it is SUPPOSED to be) to MM/DD/YYYY (which is wrong). If I run that macro again on that data set, it switches back to DD/MM/YYYY.</p>
  
  <p>But the WORST thing is that if I MANUALLY do the exact same thing (e.g. instead of running the macro, I manually go to "Data", "Text to columns" and select the EXACT same options), then it doesn't change. If the date is formatted as DD/MM/YYYY, it stays this way, and if it is formatted as MM/DD/YYYY (because of this stupid quirk), then it stays that way as well. I repeat it enough (and even re-recorded the macro a couple times) to be SURE I do the exact same things.</p>
  
  <p>I know this is because of regional settings, but the file won't always be use on my computer, and I have no way of ensuring that the end user will have any specific regional settings. I need this file to be region-settings independant, basically.</p>
  
  <p>MY QUESTION IS: How can I make sure that those dates are:</p>
  
  <ul>
  <li>Formatted &amp; recognized as date by Excel</li>
  <li>Independant of the local regional settings of the users?</li>
  </ul>
  
  <p>I know I could either to an intermediary import step (and format the data there, instead of in the main file), or then tweak the code so that only newly imported data have the macro applied.... But then I feel it's not reliable because how do I know Excel won't mess up the format?</p>
  
  <p>Oh, because the Macro is a bit cryptic looking at the VBA:</p>
  
  <p>I go to Data, Text to Column, choose "Delimited" (doesn't matter because I don't actually split it into columns), then "Delimiters" as default (doesn't matter again I'm not actually splitting the text into columns), then "Date" and in the dropdown "DYM" for DD/MM/YYYY</p>
  
  <p>EDIT: Please see Ron Rosenfield's answer for full details. For completedness, here's the code I'll be running to import the data &amp; format it at import, as opposed to importing it and then formatting it:</p>
  
  <pre><code>Sub importData()
  Dim myFile As String
  myFile = Sheet5.Cells(2, 5).Value 'My metadata sheet, containing name of file to import
  
  Sheet1.Select  'Setting target sheet as active worksheet
      With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\ChadTest\" &amp; myFile, _
      Destination:=Sheet1.Cells(Sheet5.Cells(2, 1).Value, 1))
          .Name = "ExternalData_1"
          .FieldNames = True
          .RowNumbers = False
          .FillAdjacentFormulas = False
          .PreserveFormatting = True
          .RefreshOnFileOpen = False
          .RefreshStyle = xlOverwriteCells
          .SavePassword = False
          .SaveData = True
          .AdjustColumnWidth = True
          .RefreshPeriod = 0
          .TextFilePromptOnRefresh = False
          .TextFilePlatform = 1252
  If Sheet1.Cells(1, 1).Value = "" Then
    .TextFileStartRow = 1
  Else
    .TextFileStartRow = 2
  End If
          .TextFileParseType = xlDelimited
          .TextFileTextQualifier = xlTextQualifierDoubleQuote
          .TextFileConsecutiveDelimiter = False
          .TextFileTabDelimiter = True
          .TextFileSemicolonDelimiter = False
          .TextFileCommaDelimiter = True
          .TextFileSpaceDelimiter = False
          .TextFileColumnDataTypes = Array(1, 1, 1, 4, 1, 1, 1, 4, 1, 1, 1, 1)
          .TextFileTrailingMinusNumbers = True
          .Refresh BackgroundQuery:=False
      End With
      Sheet1.Select
      Application.WindowState = xlMinimized
      Application.WindowState = xlNormal
  End Sub
  </code></pre>