格式化日期和区域设置 Excel VBA

Formatting Dates & regional setting Excel VBA

提问by logicOnAbstractions

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

这太令人沮丧了，对我来说没有意义。

This is in Excel 2010.

这是在 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.

我录制了一个宏来格式化一些包含日期（DD/MM/YYYY）的数据。我导入它，Excel 将其视为文本。所以我使用我记录的“文本到日期”宏，存储到 VBA 子文件中。

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

当我导入数据时，起初我有这个。您可以看到它被 Excel 视为文本，因为它是左对齐的（H 列，我确认它实际上是）：

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

然后我运行宏，并得到这个（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:

您可以看到 Excel 将其视为日期，因为它是右对齐的。如果我将其转换为“数字”，我会按预期看到底层序列。所以有人会觉得没问题。但事实并非如此：

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.

所以基本上它将格式从 DD/MM/YYYY （它应该是）更改为 MM/DD/YYYY （这是错误的）。如果我在该数据集上再次运行该宏，它会切换回 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.

但最糟糕的是，如果我手动执行完全相同的操作（例如，我不运行宏，而是手动转到“数据”、“文本到列”并选择完全相同的选项），则它不会改变。如果日期格式为 DD/MM/YYYY，它保持这种方式，如果它被格式化为 MM/DD/YYYY（因为这个愚蠢的怪癖），那么它也保持这种方式。我重复了很多次（甚至重新录制了几次宏）以确保我做完全相同的事情。

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?

• 格式化并被 Excel 识别为日期
• 独立于用户的本地区域设置？

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?

我知道我可以进入一个中间导入步骤（并在那里格式化数据，而不是在主文件中），或者然后调整代码，以便只有新导入的数据才应用宏......但后来我觉得它不是可靠，因为我怎么知道 Excel 不会弄乱格式？

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

哦，因为宏看着 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

我转到数据，文本到列，选择“分隔”（没关系，因为我实际上并没有将其拆分为列），然后“分隔符”作为默认值（再次无关紧要，我实际上并没有拆分文本列中），然后是“日期”和下拉菜单中的 DD/MM/YYYY 的“DYM”

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:

编辑：有关完整详细信息，请参阅 Ron Rosenfield 的回答。为了完整起见，这是我将运行的代码，用于导入数据并在导入时对其进行格式化，而不是先导入然后对其进行格式化：

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

回答by Ron Rosenfeld

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

从您写的内容来看，您似乎是从在 Excel 中打开的 TEXT 或 CSV 文件中获取数据，然后尝试处理日期。一个常见的问题。

Instead, what you can do is IMPORTthe 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.

我对您用于将数据从源获取到 Excel 工作表的确切过程有点模糊，但整个过程肯定可以使用 VBA 实现自动化。并且不需要在已经导入的数据上运行宏。

回答by Rob

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...

实际上，我们在工作中经常遇到这个问题。我们发现解决此问题的最佳方法是确保基本数据中的日期采用“YYYY/MM/DD”格式，这样它就会被识别为美国或普通日期格式中的相同日期，并且实际上会正确地按照您的区域设置在 Excel 中输入正确的日期...

回答by BobbitWormJoe

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.

此代码假定原始原始数据将始终作为 dd/mm/yyyy 格式的文本导入，包括前导零。

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

除了格式化它们之外，它不会触及任何实际日期（即单元格的实际值是序列）。

回答by Jean-Fran?ois Corbett

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

在这里，我将假设您的日期将始终以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