vba 日期格式 dd/mm/yyyy 读作 mm/dd/yyyy

Date format dd/mm/yyyy read as mm/dd/yyyy

提问by Salim

I have a spreadsheet with a column formatted as:

我有一个电子表格，其中一列格式为：

Category: Date
Type: *dd/mm/yyyy
Location: UK

类别：日期
类型：*dd/mm/yyyy
位置：英国

When I read the data in this column via VBA, it reads in the format mm/dd/yyyy.

当我通过 VBA 读取此列中的数据时，它以 mm/dd/yyyy 格式读取。

For example, 10/06/2014 (10 June 2014) is reading 06/10/2014 (06 Oct 2014).

例如，10/06/2014（2014 年 6 月 10 日）正在读取 06/10/2014（2014 年 10 月 6 日）。

My code: sDate = SourceSheet.Range("AB" & CurRow.Row).Value

我的代码： sDate = SourceSheet.Range("AB" & CurRow.Row).Value

采纳答案by SilverShotBee

I have this issue with my forms too and the best method for me is to format the textbox like this:

我的表单也有这个问题，对我来说最好的方法是像这样设置文本框的格式：

sDate = format(SourceSheet.Range("AB" & CurRow.Row).Value, "mm/dd/yyyy")

Even though the date format is wrong in VBA, it seems to work the right way round in Excel. It's weird, I can't explain why it happens, but this fixes it for me. Whenever I go from VBA to Excel, I almost always find this issue if the value is stored as a date.

尽管 VBA 中的日期格式是错误的，但它在 Excel 中似乎是正确的。这很奇怪，我无法解释为什么会这样，但这对我来说解决了。每当我从 VBA 转到 Excel 时，如果将值存储为日期，我几乎总是会发现这个问题。

回答by Gary's Student

Consider:

考虑：

Sub luxation()
    Dim sDate As Date, CurRow As Range
    Set SourceSheet = ActiveSheet
    Set CurRow = Range("A1")

    ary = Split(SourceSheet.Range("AB" & CurRow.Row).Text, "/")
    sDate = DateSerial(ary(2), ary(1), ary(0))

    MsgBox Format(sDate, "dd mmmm yyyy")
End Sub

回答by Tony Dallimore

This question of mine - .NumberFormat sometimes returns the wrong value with dates and times- gives some background which may help.

我的这个问题 - .NumberFormat 有时会返回错误的日期和时间值- 提供了一些可能有帮助的背景。

I first encountered this VBA bug many years ago and it is worse than it seems. I noticed that many - but not all - dates in a worksheet that I had been updating for a year were wrong. It took me a long time to diagnose the problem. Those dates that could be interpreted as middle endian dates had been corrupted but those that could not be interpreted as middle endian dates were unchanged. So 12/06/2014 will become 6 December but 13/06/2014 will remain 13 June. If 13/06/2014 had been rejected as an invalid date or left as a string, I would have spotted the error immediately. The dual interpretation so every date was imported as a date - the wrong date but still a date - ensured I did not notice until much later maximising the cost of correcting for the bug.

多年前我第一次遇到这个 VBA 错误，它比看起来更糟。我注意到我已经更新了一年的工作表中的许多（但不是全部）日期是错误的。我花了很长时间才诊断出问题。那些可以解释为中端日期的日期已被破坏，但那些不能解释为中端日期的日期没有改变。因此，12/06/2014 将变为 12 月 6 日，但 13/06/2014 仍将是 6 月 13 日。如果 13/06/2014 作为无效日期被拒绝或保留为字符串，我会立即发现错误。双重解释因此每个日期都作为日期导入 - 错误的日期但仍然是一个日期 - 确保我直到很久以后才注意到，最大限度地提高了纠正错误的成本。

Excel holds dates and times as numbers. "17 June 2014" is held as 41807 and "1 January 1900" is held as 1. In both cases, the value is the number of days since 31 December 1899. Times as held as a fraction:

Excel 将日期和时间保存为数字。“2014 年 6 月 17 日”保留为 41807，“1900 年 1 月 1 日”保留为 1。在这两种情况下，该值都是自 1899 年 12 月 31 日以来的天数。保留的时间为分数：

number of seconds since midnight
--------------------------------
      seconds in a day

So 06:00, 12:00 and 18:00 are held as 0.25, 0.5 and 0.75.

所以 06:00、12:00 和 18:00 被保持为 0.25、0.5 和 0.75。

This bug is encountered when the transfer of a date involves a conversion to and from string format. I have not discovered a single case in which the conversion from date to string has been wrong. It is the conversion from string to date that hits this bug.

当日期传输涉及到字符串格式和从字符串格式的转换时，会遇到此错误。我还没有发现从日期到字符串的转换是错误的。正是从字符串到日期的转换遇到了这个错误。

I can see that SilverShotBee's solution will avoid the bug but it would not appeal to me. I no longer use any ambiguous dates ever.

我可以看到 SilverShotBee 的解决方案将避免该错误，但它不会吸引我。我不再使用任何模棱两可的日期。

One choice is to transfer the value as a number. If cell A3 contains the date and time "17 June 2014 9:00" then CDbl(Range("A3").Value)returns 41807.375. When you store this number in a cell you will need to set the cell's NumberFormat to the date format of your choice but that might be a good thing.

一种选择是将值作为数字传输。如果单元格 A3 包含日期和时间“2014 年 6 月 17 日 9:00”，则CDbl(Range("A3").Value)返回 41807.375。当您将此数字存储在单元格中时，您需要将单元格的 NumberFormat 设置为您选择的日期格式，但这可能是一件好事。

If I were going to use middle endian dates, I would be explicit. #13/06/2014# is always interpreted as middle endian.

如果我要使用中间端日期，我会很明确。#13/06/2014# 总是被解释为中间字节序。

I prefer unambiguous strings. "2014-06-13" or "13 June 2014" are not misinterpreted by VBA or by a human reader.

我更喜欢明确的字符串。VBA 或人类读者不会误解“2014-06-13”或“2014 年 6 月 13 日”。

回答by Phil

Have just come up against this issue! Reading records from a .csv and storing in an .xls

刚遇到这个问题！从 .csv 读取记录并存储在 .xls 中

I found the following sequence works to overcome the misinterpreted dates:

我发现以下序列可以克服误解的日期：

1. Read the date field from the .csv file
2. Store it into a cell in the .xls file
3. Read it back into vba
4. Store into its required destination in the .xls

1. 从 .csv 文件中读取日期字段
2. 将其存储到 .xls 文件中的单元格中
3. 读回vba
4. 存储到 .xls 中所需的目的地

Date is in original format

日期为原始格式

回答by Phil

I found this issue to be incredibly complex and was trying to keep it as simple as possible but have indeed left a few vital details out! Apologies. Here is a fuller version of what I found:

我发现这个问题非常复杂，并试图让它尽可能简单，但确实遗漏了一些重要的细节！道歉。这是我发现的更完整版本：

First of all I should explain I was reading dates (and other fields) from a .csv and storing back into an .xls

首先，我应该解释一下我正在从 .csv 读取日期（和其他字段）并将其存储回 .xls

I am on Office 2002 running on Windows/7

我在 Windows/7 上运行的 Office 2002

Using 2 example dates: 27/4/2015 and 7/5/2015 in dd/mm/yyyy string format (from the csv)

使用 2 个示例日期：27/4/2015 和 7/5/2015，采用 dd/mm/yyyy 字符串格式（来自 csv）

What I found was:

我发现的是：

Reading the 27/4/2015 text date field from csv into a variable dimensioned as STRING and storing into an xls field in dd/mm/yyyy DATE format produces a cell that reads 27/4/2015 but converting it into a cell formatted as Number also produces 27/4/2015. 7/5/2015 on the other hand produces a string that reads 7/5/2015 and converting it into a cell formatted as Number produces 42131.

将 27/4/2015 文本日期字段从 csv 读取到尺寸为 STRING 的变量中并以 dd/mm/yyyy DATE 格式存储到 xls 字段中，生成一个读取 27/4/2015 的单元格，但将其转换为格式为数字也产生 27/4/2015。另一方面，7/5/2015 生成一个字符串，该字符串读取 7/5/2015 并将其转换为格式为 Number 的单元格，生成 42131。

Reading the 27/4/2015 text date field from csv into an undimensioned variable and storing into an xls field in dd/mm/yyyy DATE format produces a cell that reads 27/4/2015 but converting it into a cell formatted as Number also produces 27/4/2015 while 7/5/2015 reads 5/7/2015 and converting it into a cell formatted as Number produces 42190.

将 27/4/2015 文本日期字段从 csv 读取到无维度变量并以 dd/mm/yyyy DATE 格式存储到 xls 字段中，生成一个读取 27/4/2015 的单元格，但也将其转换为格式为 Number 的单元格产生 27/4/2015 而 7/5/2015 读取 5/7/2015 并将其转换为格式为 Number 的单元格产生 42190。

Reading the 27/4/2015 text date field from csv into a variable dimensioned as DATE and storing into an xls field in dd/mm/yyyy DATE format produces a cell that reads 27/4/2015 and converting it into a cell formatted as Number produces 42121. 7/5/2015 on the other hand produces a string that reads 5/7/2015 and converting it into a cell formatted as Number produces 42190.

将 27/4/2015 文本日期字段从 csv 读取到尺寸为 DATE 的变量中并以 dd/mm/yyyy DATE 格式存储到 xls 字段中，生成一个读取 27/4/2015 的单元格并将其转换为格式为Number 产生 42121。另一方面，7/5/2015 产生一个字符串，读取 5/7/2015 并将其转换为 Number 格式的单元格产生 42190。

The first 3 scenarios above therefore do not produce the desired results for all date specifications.

因此，上述前 3 种情况不会为所有日期规范产生预期的结果。

To fix this I do the following:

为了解决这个问题，我执行以下操作：

Input_Workbook.Activate
ilr = Range("A5000").End(xlDown).End(xlDown).End(xlDown).End(xlUp).Row
For i = 1 To ilr
  Input_Workbook.Activate
  If IsDate(Cells(i, 1).Value) Then
    d1 = Cells(i, 1).Value
    d1 = Replace(d1, "/", "-")
    ThisWorkbook.Activate
    Cells(14, 5).Value = d1
    d1 = Cells(14, 5).Value
    If VarType(d1) = vbString Then
     d1 = CDate(d1)
    End If
    Cells(i, 1).Value = d1
  End If
Next

The cell used to store the date initially is formatted GENERAL and the ultimate target cells is formatted as DATE (dd/mm/yyyy).

最初用于存储日期的单元格格式为 GENERAL，最终目标单元格格式为 DATE (dd/mm/yyyy)。

I don't have enough brain cells left to fully explain what happens to the dates during this process but it works for me and of course the choice of target cells is completely random in the above code block.

我没有足够的脑细胞来完全解释这个过程中日期会发生什么，但它对我有用，当然，在上面的代码块中，目标细胞的选择是完全随机的。

回答by Mark

The problem was VBA was opening the csv with the reverse dates for single digit days.

问题是 VBA 正在打开带有个位数天数的反向日期的 csv。

This way of opening the workbook worked the same as when I did it manually so had the correct dates in dd/mm/yyyy format. Then copied across correctly:

这种打开工作簿的方式与我手动打开工作簿时的工作方式相同，因此具有正确的 dd/mm/yyyy 格式日期。然后正确复制：

Workbooks.OpenText FileName:=fpathO, datatype:=xlDelimited, comma:=True, local:=True