.NumberFormat有时会返回带有日期和时间的错误值 [英] .NumberFormat sometimes returns the wrong value with dates and times

问题描述

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

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.

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

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

我通过测试单元格值来实现这一效果成为一个数字，日期或时间。如果是，我检索数字格式，并使用它来格式化单元格值：

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.

经过多次实验与Excel 2003和Excel 2007之后，我发现了原因。 （我无法访问Excel 2010，但是从我推断出来的问题也有同样的问题）。这个问题部分是为了向世界揭示这个问题，因为我可以在互联网上发现没有任何人注意到它。 （毫无疑问，有人会回答说他们是xyz，并立即得到答案）然而，这个问题的主要目的是寻求在所有情况下获得我需要的结果的建议。

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.

通常我输入日期，例如23mar12。 Excel将此识别为日期，并将其格式化为23-Mar-12。我可以选择格式化单元格并输入或选择自定义格式或选择一种日期格式，以便我可以有任何格式，我可以想象包括非英文名称的天和几个月。

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

然而，在一种情况下，我选择的格式不是录制的格式：自定义格式dd / mm / yyyy被记录为日期格式* 14/03/2001 。这不是一个明显的问题，直到更进一步。

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.

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

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.

在某些情况下，Excel选择和记录的格式不是返回的格式通过NumberFormat：

In a number of cases the format selected and recorded by Excel was not the format returned by 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;@

值（1）和（2）在Excel格式列中添加了我，以表明有两个明显相同的格式。从NumberFormat列可以看出，在每种情况下，第二个版本都会禁止前导零。

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.

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

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.

但是，三种标准格式带有星号的微软标记被不可接受地改​​变。日期从小字符串变为中间字节;时间从24小时改为12小时，并将星期几添加到* 2001年3月14日。

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

针对日期的星号引用注释：除了类型列表（数字选项卡，格式化单元格对话框）中具有星号（）的项目，您应用的日期格式不会与操作系统切换日期订单。星号与时间引用注释：除了类型列表（数字选项卡，格式化单元格对话框）中具有星号（）的项目，您应用的时间格式不会与操作系统切换时间订单。

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

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

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

回到我的开始点：这是一个奇怪的处理一个特定的日期格式，这么多人声称他们的日期的原因有时被转换成美国格式，这是为什么这个问题是如此难以捉摸？我在另一组微软程序员的其他地方遇到这种类型的问题，不知道另外一个组正在做什么。这是为什么一些功能总是工作，有时也不行吗？一些Microsoft程序员知道在哪里寻找正确的格式，而其他的则不是？

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?

更重要的是，对我来说，任何人都可以建议：

More importantly, for me, can anyone suggest:

• 如何获取真实的日期或时间格式？


• 确定用户选择的日期或时间的显示格式的其他方法？

BTW 1：我记得三十多年前，我被告知美国军方不使用月/日格式;只有美国平民使用这种格式。任何人都可以告诉我，如果这是真的吗？

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 2：类似的问题是Excel颜色。 Excel将其颜色保存为ggbbrr，而其他所有者都将其保存为rrggbb。 .Net Excel操作系统的程序员没有被通知，并且在使用它之前没有反转Excel颜色编号来控制屏幕。

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.

推荐答案

当打开使用不同区域设置保存的文本文件时，我主要反对格式化和日期问题。处理这个的两个有用的单元格属性是：

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:

• .Text 返回单元格价值显示


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

• .Text returns the cell value as it is displayed
• .Value2 returns the unformatted cell value or date serial number.

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

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.

如果您真的想看到用户输入的日期或号码，您可以提取.xlsx文件的内容，查看工作表单元格式以及列出保存的工作簿中的数字格式的共享字符串xml定义。我真的不需要这样做，因为底层价值是作为序列号存储在内部的，这不会改变。

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.

这篇关于.NumberFormat有时会返回带有日期和时间的错误值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！