vba .NumberFormat 有时会返回错误的日期和时间值

.NumberFormat sometimes returns the wrong value with dates and times

提问by Tony Dallimore

It seems that every week or so someone posts a question about dates being converted (corrupted?) to American format. Like many others, I have attempted to help but the problem is elusive. I now wonder if I have discovered the cause.

似乎每周都会有人发布一个关于日期被转换（损坏？）到美国格式的问题。像许多其他人一样，我试图提供帮助，但问题是难以捉摸的。我现在想知道我是否发现了原因。

I am working on an application in which I need to extract data from an Excel worksheet and output it as strings formatted to match the value the Excel user can see. So if the value is “1” formatted to display as “1.00” then I want the string to be “1.00”.

我正在开发一个应用程序，在该应用程序中我需要从 Excel 工作表中提取数据并将其输出为字符串格式以匹配 Excel 用户可以看到的值。因此，如果将值设置为“1”格式以显示为“1.00”，那么我希望字符串为“1.00”。

I achieve this effect by testing the cell value to be a number, date or time. If it is, I retrieve the number format and use it to format the cell value so:

我通过将单元格值测试为数字、日期或时间来实现此效果。如果是，我检索数字格式并使用它来格式化单元格值，以便：

With .Cells(Row, Column) 
  Output string = Format(.Value, .NumberFormat)
End With

In most cases this gives me exactly the output I require. However, sometimes I get American dates and times when the source is formatted as a UK date or time.

在大多数情况下，这正是我需要的输出。但是，有时当源格式为英国日期或时间时，我会得到美国日期和时间。

After much experimentation with Excel 2003 and Excel 2007, I have discovered the cause. (I do not have access to Excel 2010 but from questions I deduce it has the same problem.) This question is in part intended to reveal this problem to the world because I can discover nothing on the internet to indicate that anyone else has noticed it. (No doubt someone will reply that they googled “xyz” and got the answer immediately.) However, the main purpose of this question is to seek suggestions for obtaining the result I need in all situations.

在对 Excel 2003 和 Excel 2007 进行大量实验后，我发现了原因。（我无权访问 Excel 2010，但从问题中我推断它有同样的问题。）这个问题的部分目的是向全世界揭示这个问题，因为我在互联网上找不到任何东西表明其他人已经注意到它. （毫无疑问，有人会回答说他们在 google 上搜索了“xyz”并立即得到了答案。）但是，这个问题的主要目的是寻求建议以在所有情况下获得我需要的结果。

Typically I enter dates as, for example, “23mar12”. Excel recognises this as a date and formats it as “23-Mar-12”. I can select Format Cellsand enter or select a custom format or select one of the date formats so I can have any format I can imagine wanting including non-English names for days and months.

通常，我输入日期为“23mar12”。Excel 将其识别为日期并将其格式化为“23-Mar-12”。我可以选择Format Cells并输入或选择自定义格式，或者选择其中一种日期格式，这样我就可以拥有我能想象到的任何格式，包括日期和月份的非英语名称。

However, in one case the format I select is not the format that is recorded: Custom format “dd/mm/yyyy” is recorded as Date format “* 14/03/2001”. This is not obviously a problem until further down the line.

但是，在一种情况下，我选择的格式不是记录的格式：自定义格式“dd/mm/yyyy”被记录为日期格式“* 14/03/2001”。在进一步深入之前，这显然不是问题。

I created a column of dates and times and formatted each with a different custom or standard format. I wrote a macro to extract the NumberFormat for each of these dates and times and write it as a string to an adjacent column. I also formatted the value using the number format and wrote that string to a third column.

我创建了一列日期和时间，并使用不同的自定义或标准格式对每个进行格式化。我编写了一个宏来提取每个日期和时间的 NumberFormat 并将其作为字符串写入相邻列。我还使用数字格式格式化该值并将该字符串写入第三列。

In a number of cases the format selected and recorded by Excel was not the format returned by NumberFormat:

在许多情况下，Excel 选择和记录的格式不是 NumberFormat 返回的格式：

Excel format            NumberFormat
Date: * 14/03/2001       m/d/yyyy
Date: * 14 March 2001   [$-F800]dddd, mmmm dd, yyyy
Date: 14/03/2001        dd/mm/yyyy;@
Date: 14/03/01          dd/mm/yy;@
Date: 14/3/01           d/m/yy;@
Date: 14.3.01           d.m.yy;@
Date: 2001-03-14        yyyy-mm-dd;@
Date: 14 March 2001 (1) [$-809]dd mmmm yyyy;@
Date: 14 March 2001 (2) [$-809]d mmmm yyyy;@
Custom: hh:mm:ss        h:mm:ss
Time: * 13:30:55        [$-F400]h:mm:ss AM/PM
Time: 13:30:55 (1)      hh:mm:ss;@
Time: 13:30:55 (2)      h:mm:ss;@
Time: 01:30:55 PM       [$-409]hh:mm:ss AM/PM;@
Time: 1:30:55 PM        [$-409]h:mm:ss AM/PM;@

The values (1) and (2) in the Excel format column were added by me to indicate that there are two apparently identical formats. As can be seen from the NumberFormat column, in each case the second version suppresses a leading zero.

Excel 格式列中的值 (1) 和 (2) 是我添加的，以表明存在两种明显相同的格式。从 NumberFormat 列中可以看出，在每种情况下，第二个版本都取消了前导零。

Most changes have no important effect. “[$-F800]” and so on are apparently dummy values with no effect. Apparently you can replace “F800” with an Microsoft country code to have the names of days and months translated to the language of that country.

大多数更改没有重要影响。“[$-F800]”等显然是无效值。显然，您可以用 Microsoft 国家/地区代码替换“F800”，将日期和月份的名称翻译成该国家/地区的语言。

However, the three standard formats that Microsoft marks with an asterisk are changed unacceptably. The dates are changed from little endian to middle endian; the time is changed from 24 hour to 12 hour and the day of the week has been added to “* 14 March 2001”.

但是，Microsoft 用星号标记的三种标准格式发生了令人无法接受的更改。日期从小端更改为中端；时间从 24 小时更改为 12 小时，并且在“* 14 March 2001”中添加了星期几。

The asterisk against the dates, references the comment: “Except for items that have an asterisk () in the Type list (Number tab, Format Cells dialog box), date formats that you apply do not switch date orders with the operating system.” The asterisk against the time, references the comment: “Except for items that have an asterisk () in the Type list (Number tab, Format Cells dialog box), time formats that you apply do not switch time orders with the operating system.”

针对日期的星号引用了以下评论：“除了在类型列表（数字选项卡，设置单元格格式对话框）中带有星号 ( ) 的项目，您应用的日期格式不会与操作系统切换日期顺序。” 与时间相对的星号引用了以下评论：“除了类型列表（数字选项卡，设置单元格格式对话框）中带有星号 () 的项目外，您应用的时间格式不会与操作系统切换时间顺序。”

If I have to, I can warn my users that standard date and time formats may not give the result desired. However, if they want the popular format “dd/mm/yyyy”, they cannot have it. “dd-mm-yyyy”, for example, is OK but custom format “dd/mm/yyyy” becomes date format “* 14/03/2001” becomes “m/d/yyyy”.

如果必须，我可以警告我的用户标准日期和时间格式可能无法提供所需的结果。但是，如果他们想要流行的格式“dd/mm/yyyy”，他们就不能拥有。例如，“dd-mm-yyyy”是可以的，但是自定义格式“dd/mm/yyyy”变成了日期格式“* 14/03/2001”变成了“m/d/yyyy”。

Returning to my opening point: is this strange handling of one particular date format the reason so many people claim their dates are sometimes being converted to American format and is this why the problem is so elusive? I have come across this type of problem elsewhere of one group of Microsoft programmers not knowing what another group are doing. Is this why some functions always work and other sometimes don't? Some Microsoft programmers know where to look for the correct format and others don't?

回到我的开篇点：对一种特定日期格式的这种奇怪处理是否是许多人声称他们的日期有时被转换为美国格式的原因，这就是问题如此难以捉摸的原因吗？我在其他地方遇到过这种类型的问题，其中一组 Microsoft 程序员不知道另一组在做什么。这就是为什么某些功能始终有效而其他功能有时不起作用的原因吗？一些 Microsoft 程序员知道在哪里寻找正确的格式，而其他人不知道？

More importantly, for me, can anyone suggest:

更重要的是，对我来说，谁能建议：

• How I obtain the true date or time format?
• Some other way of determining the user's chosen display format for a date or time?

• 我如何获得真实的日期或时间格式？
• 确定用户选择的日期或时间显示格式的其他方法？

BTW 1: I recall that thirty or so years ago I was told that the American military do not use month/day/year format; only American civilians use this format. Can anyone tell me if this is true?

BTW 1：我记得三十多年前有人告诉我美国军队不使用月/日/年格式；只有美国平民使用这种格式。谁能告诉我这是不是真的？

BTW 2: The similar problem is with Excel colours. Excel holds its colours as "ggbbrr" while everybody else holds them as "rrggbb". The programmers for the .Net Excel inter-op were not told and and did not reverse the Excel colour number before using it to control the screen.

BTW 2：类似的问题是 Excel 颜色。Excel 将其颜色保存为“ggbbrr”，而其他人将其保存为“rrggbb”。.Net Excel 互操作的程序员没有被告知，也没有在使用它来控制屏幕之前反转 Excel 颜色编号。

采纳答案by lori_m

I have mainly come up against formatting and date issues when opening text files which have been saved with different regional settings. Two useful cell properties for dealing with this are:

在打开以不同区域设置保存的文本文件时，我主要遇到格式和日期问题。用于处理此问题的两个有用的单元格属性是：

• .Textreturns the cell value as it is displayed
• .Value2returns the unformatted cell value or date serial number.

• .Text返回显示的单元格值
• .Value2返回未格式化的单元格值或日期序列号。

As you say, standard date and number formats depend on windows regional settings and this may not be desired behavior as the same workbook can display differently in different regions. MS introduced the regional code prefixes in number formats (circa Excel 2000?) which enforce consistent display if needed but they need to be explicitly selected.

正如您所说，标准日期和数字格式取决于 Windows 区域设置，这可能不是理想的行为，因为同一工作簿在不同区域的显示方式可能不同。MS 以数字格式（大约 Excel 2000？）引入了区域代码前缀，如果需要，强制显示一致，但需要明确选择。

If you really want to see a date or number as the user entered it, you could extract the contents of the .xlsx file looking at the worksheet cell format and the shared strings xml definitions which list the number formats in the saved workbook. I don't really see a need to do this though as the underlying value is stored internally as a serial number and this will not change.

如果您真的想查看用户输入的日期或数字，您可以提取 .xlsx 文件的内容，查看工作表单元格格式和列出保存工作簿中数字格式的共享字符串 xml 定义。我真的认为没有必要这样做，因为基础值在内部存储为序列号，并且不会改变。

回答by Kevin

BTW 1: It's been almost 30 years since I was in the military... I worked on helicopters and I was taught to use a format such as this in the aircraft logbooks: 3 Apr 12. So, that's how I still write dates. This way, there's no wondering about 4/3/2012 - is it April 3 or March 4?

BTW 1：我在军队里已经快 30 年了......我在直升机上工作，我被教导在飞机日志中使用这样的格式：12 年 4 月 3 日。所以，我仍然是这样写日期的。这样，2012 年 4 月 3 日就没有问题了——是 4 月 3 日还是 3 月 4 日？

回答by joshoff

I hacked this: I rewrite the original data in a known format. it relies on DateSerial and TimeSerial:

我破解了这个：我以已知格式重写原始数据。它依赖于 DateSerial 和 TimeSerial：

'Google spreadsheet stores dates in USA format (MM/DD/YYYY).  We're in Australia, using DD/MM/YYYY, so we need to swap them.
                    '
                        With dc 'the cell who contains a date in USA format.
                             d = .Value      'capture value in USA format
                             t = TimeValue(d)
                             .NumberFormat = "dd/mm/yyyy" 'set to OZ format, so Excel knows the values were swapped in its internal math.
                             .Value = DateSerial(Year(d), Month(d), Day(d)) 'DateSerial takes y,d,m. We swap Month and Day components, to get OZ format dates
                             .Value = .Value + TimeSerial(Hour(t), Minute(t), Second(t))
                             dc.Font.Bold = True                         ' We bold the cells that are swapped, for debugging
                         End With
                    End If