Formatting Dates & regional setting Excel VBA

Question

This is super frustrating and it doesn't make sense to me.

This is in Excel 2010.

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.

Here's the recorded macro:

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

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):

Then I run the macro, and get this (column H):

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:

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:

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.

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.

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.

MY QUESTION IS: How can I make sure that those dates are:

• Formatted & recognized as date by Excel
• Independant of the local regional settings of the users?

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?

Oh, because the Macro is a bit cryptic looking at the VBA:

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

EDIT: Please see Ron Rosenfield's answer for full details. For completedness, here's the code I'll be running to import the data & format it at import, as opposed to importing it and then formatting it:

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\" & 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

Answer 1

From what you write, it seems you are getting your data from a TEXT or CSV file which you OPEN in Excel, and then try to process the date. A common problem.

Instead, what you can do is IMPORT the file. If you do this, the Text Import Wizard (same as the text-to-columns wizard), will open BEFORE the data gets written to the worksheet, and give you the opportunity to specify the format of the dates being imported.

That option should be on the Get External Data tab of the Data Ribbon:

I'm a bit hazy on the exact process you are using to get data from the source to the Excel worksheet, but the whole process could surely be automated with VBA. And there should be no need to run the macro on the already imported data.

Answer 2

This is what I came up with, using what I mentioned in my comment above:

Sub DateFormatting()

    On Error Resume Next

    Application.ScreenUpdating = False

    Dim wbCurrent As Workbook
    Dim wsCurrent As Worksheet
    Dim nLastRow, i, nDay, nMonth, nYear As Integer
    Dim lNewDate As Long

    Set wbCurrent = ActiveWorkbook
    Set wsCurrent = wbCurrent.ActiveSheet
    nLastRow = wsCurrent.Cells.Find("*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    For i = nLastRow To 2 Step -1
        If InStr(1, wsCurrent.Cells(i, 8), "/", vbBinaryCompare) > 0 Then
            nYear = Right(wsCurrent.Cells(i, 8), 4)
            nMonth = Mid(wsCurrent.Cells(i, 8), InStr(1, wsCurrent.Cells(i, 8), "/", vbBinaryCompare) + 1, 2)
            nDay = Left(wsCurrent.Cells(i, 8), 2)
            lNewDate = CLng(DateSerial(nYear, nMonth, nDay))
            wsCurrent.Cells(i, 8).Value = lNewDate
        End If
    Next i

    wsCurrent.Range("H:H").NumberFormat = "dd/mm/yyyy"

    Application.ScreenUpdating = True

End Sub

This code assumes that the original raw data will always be imported as text in the format dd/mm/yyyy, leading zeros included.

It won't touch any real dates (i.e. where the actual value of the cell is a serial) except to format them.

Answer 3

Here I will assume that your dates will always be written in the format dd/mm/yyyy.

If I only had one cell to check, I would do this:

Dim s() As String
With Range("A1")
    If .NumberFormat = "@" Then
        'It's formatted as text.
        .NumberFormat = "dd/mm/yyyy"
        s = Split(.Value, "/")
        .Value = DateSerial(CInt(s(2)), CInt(s(1)), CInt(s(0)))
    Else
        'It isn't formatted as text. Do nothing.
    End If
End With

However, this does not scale well if you have many cells to check; looping through cells is slow. It's much quicker to loop through an array, like this:

Dim r As Range
Dim v As Variant
Dim i As Long
Dim s() As String
Set r = Range("H:H").Resize(GetLastNonblankRowNumber(Range("H:H"))) 'or wherever 
v = r.Value ' read all values into an array (could be strings, could be real dates)
For i = 1 To UBound(v, 1)
    If VarType(v(i, 1)) = vbString Then
        s = Split(v(i, 1), "/")
        v(i, 1) = DateSerial(CInt(s(2)), CInt(s(1)), CInt(s(0)))
    End If
Next i
With r
    .NumberFormat = "dd/mm/yyyy"
    .Value = v 'write dates back to sheet
End With

where I make use of:

Function GetLastNonblankRowNumber(r As Range) As Long
    GetLastNonblankRowNumber = r.Find("*", r.Cells(1, 1), xlFormulas, xlPart, _
       xlByRows, xlPrevious).Row
End Function

Answer 4

Actually we had this problem quite a lot at work. we figured out that the best way to work around this problem is to make sure the dates in the base data are in format "YYYY/MM/DD", this way it's recognized as the same date in either American or normal date format and it will actually correctly follow your regional settings to put a correct date in Excel...