VBA 文本到列阅读日期格式为美国

VBA Text-To-Columns Reading Date Format As US

提问by DannyBland

When running text-to-columns in vba, it puls my dates as american dates, instead of uk dates, as long as it is possible for it to do so. So for example, 31-Aug stays as 31-Aug, but 01-Sep changes to 09-Jan!

在 vba 中运行 text-to-columns 时，它会将我的日期脉冲为美国日期，而不是英国日期，只要它有可能这样做。例如，31-Aug 保持为 31-Aug，但 01-Sep 更改为 09-Jan！

Is there any way to specify that all dates are in UK format BEFORE vba runs? As even just reformatting to US dates to show as US dates won't work because we have code that runs if date < Today, which of course will cause issues with the changing date format.

有什么方法可以在 vba 运行之前指定所有日期都是英国格式？因为即使只是重新格式化为美国日期以显示为美国日期也不起作用，因为我们有运行的代码if date < Today，这当然会导致更改日期格式的问题。

The data feeds in the format dd/mm/yyyy, but excel reads it as mm/dd/yyyy!

数据以 dd/mm/yyyy 格式输入，但 excel 将其读取为 mm/dd/yyyy！

回答by Rob Hymanson

pnuts comment is the easiest method to solve this problem.

pnuts 注释是解决这个问题最简单的方法。

Explanation:

解释：

Run the 'Record Macro', select your column and initiate text-to-columns wizard.

运行“记录宏”，选择您的列并启动文本到列向导。

On the 3rd page of the text-to-columns wizard, under 'Column data format', the 'Date' option is automatically set to 'MDY'. Change this to 'DMY'.

在文本转列向导的第三页上，在“列数据格式”下，“日期”选项自动设置为“MDY”。将其更改为“DMY”。

Stop the macro and you will find this code

停止宏，你会发现这个代码

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 4), TrailingMinusNumbers:=True

The "4" in =Array(1,4)is the part that determines DMY format.

中的“4”=Array(1,4)是决定 DMY 格式的部分。

回答by Raymond

I've got a better option. Define the column as text so:

我有更好的选择。将列定义为文本，以便：

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 2), TrailingMinusNumbers:=True

Using the 2 format creates a text field so there will be no formating.

使用 2 格式会创建一个文本字段，因此不会进行格式化。

回答by Tom Hooper

I was experiencing this same issue today, I too used the DMY in text to columns which worked when when doing it manually but in VBA as described above always changes to US data format where possible.

我今天遇到了同样的问题，我也使用了文本中的 DMY 列，这些列在手动执行时有效，但在如上所述的 VBA 中总是尽可能更改为美国数据格式。

I got around this (which may not be suitable for you) by inserting a new column to the right and using =TEXT(Cell, "DD/MM/YYYY") and then copying and pasting the values.

我通过在右侧插入一个新列并使用 =TEXT(Cell, "DD/MM/YYYY") 然后复制和粘贴值来解决这个问题（这可能不适合你）。

I deleted the original column afterwards, I also ensure this always works regardless of the amount of rows by working out my last row with data in each time.

之后我删除了原始列，我还通过每次计算出我的最后一行数据来确保无论行数如何，这始终有效。

VBA segments below..

下面的 VBA 段..

Sub Dateformat()

'sets i as last current row with data in

    Dim i As Integer

    Range("A2").Select

    i = ActiveCell.End(xlDown).Row

    Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""DD/MM/YYYY"")"
Selection.Copy
Range("D2:D" & i).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

End Sub

Hope this helps.

希望这可以帮助。

Thanks

谢谢

Tom

汤姆

回答by Tom

Maybe this observation is helpful (Excel365 64bit):

也许这个观察是有帮助的（Excel365 64bit）：

Conclusion: Be careful to apply TextToColumn ONLY to cells that actually contain text formatted dates and NOT to cells are already recognized as dates by Excel.

结论：注意仅将 TextToColumn 应用于实际包含文本格式日期的单元格，而不应用于已被 Excel 识别为日期的单元格。

Observation: I used VBA to fill cells with data from an array containing dates using "range.value =". The result will be cells filled with the data in text format (e.g. 01.09.2019 is in the cell, but Excel doesn't know it is a date for 1. Sept. 2019 but only sees text). I then used VBA TextToColumns to turn those cells into actual dates specifying "FieldInfo:=Array(1, 4)". The format was indeed DMY.

观察：我使用 VBA 用包含日期的数组中的数据填充单元格，使用“range.value =”。结果将是用文本格式的数据填充的单元格（例如 01.09.2019 在单元格中，但 Excel 不知道它是 1. Sept. 2019 的日期，但只能看到文本）。然后我使用 VBA TextToColumns 将这些单元格转换为指定“FieldInfo:=Array(1, 4)”的实际日期。格式确实是DMY。

This worked well BUT all things got messed up when some cells in the range were already recognized as dates by Excel.

这很有效，但是当范围中的某些单元格已经被 Excel 识别为日期时，所有事情都变得一团糟。

Scenario in my case: Data was added using "value = (dates from an arry variable)" to the bottom of a column resulting in text formatted cells. I wanted to use TextToColumn to reformat the data into dates. But TextToColumns was every time applied to the entire column containng cells that had already been reformatted previously instead of only the newly added data.

在我的情况下的场景：使用“value =（来自 arry 变量的日期）”将数据添加到列的底部，从而生成文本格式的单元格。我想使用 TextToColumn 将数据重新格式化为日期。但是 TextToColumns 每次都应用于包含先前已重新格式化的单元格的整个列，而不仅仅是新添加的数据。

Here is what happened: For some reason VBA read the already date format cell correctly but then switched around month and day where possible to write them back as MDY. To see what I mean you can try applying TextToColumns to date format cells using "FieldInfo:=Array(1, 2)". 01.09.2019 for 1. Sept. 2019 will be turned into a text cell with "09/01/2019" for 9. January 2019.

这是发生的事情：由于某种原因，VBA 正确读取了已经日期格式的单元格，然后在可能的情况下切换月份和日期将它们写回 MDY。要了解我的意思，您可以尝试使用“FieldInfo:=Array(1, 2)”将 TextToColumns 应用于日期格式单元格。01.09.2019 for 1. Sept. 2019 将变成带有“09/01/2019”的文本单元格，用于 9. January 2019。

We therefore have to be careful to apply TextToColumn ONLY to really text formatted data when writing back in date format. A possible work around may be to somehow first read the entire data into an array and write it back to ensure that all data is of the same format to start with.

因此，当以日期格式回写时，我们必须小心地将 TextToColumn ONLY 应用于真正的文本格式数据。一种可能的解决方法可能是以某种方式首先将整个数据读入一个数组并将其写回以确保所有数据开始时具有相同的格式。

回答by Barry

Maybe you can set the language first with Application.International?

也许您可以先使用 Application.International 设置语言？

[http://msdn.microsoft.com/en-us/library/office/ff840213(v=office.15).aspx][1]

[ http://msdn.microsoft.com/en-us/library/office/ff840213(v=office.15).aspx][1]

回答by sam

There is a workarround for this case. First get the date part from the text before doing TexttoColumn. This date will be in text. Use Left, Mid and right functions to get the date, month and year and then use the function dateserial in vba with these arguments. Now you will get 2 type of dates. Date in text format which will be in UK format which you can store in the cells in excel. Other is in UK format which will be used on for if date < Todaycondition. U can now use text to columns and ignore the date that comes from texttocolumns and use date as text instead. The code for same in mentioned below for eg - "31/08/2014 part1 part2"

这种情况有一个解决方法。在执行 TexttoColumn 之前，首先从文本中获取日期部分。该日期将以文本形式显示。使用 Left、Mid 和 right 函数获取日期、月份和年份，然后在 vba 中使用带有这些参数的函数 dateserial。现在您将获得 2 种类型的日期。文本格式的日期将为英国格式，您可以将其存储在 excel 的单元格中。其他是英国格式，将用于if date < Today条件。你现在可以使用文本到列并忽略来自 texttocolumns 的日期并使用日期作为文本。下面提到的相同代码例如 - “31/08/2014 part1 part2”

Sub t() 

Dim myarr() As String

Dim cell As Range

  For Each cell In Selection ' selection contains cells which text you need to convert text to columns
      i = i + 1
      myarr = Split(cell.Value, " ")
      mydate = Val(Left(myarr(0), 2))
      mymonth = Val(Mid(myarr(0), InStr(1, myarr(0), "/") + 1, 2))
      myyear = Val(Right(myarr(0), 4))
      date_american = DateSerial(myyear, mymonth, mydate)
      date_uk = myarr(0)
  Next cell

End Sub