在 VBA 中读取包含日期时间的 Excel 单元格时出现问题

Problem reading Excel cells containing datetime in VBA

提问by Codo

Some background:For a project I'm working on (building an XML DOM from a given excel spreadsheet of customer data), I need to be able to read the contents of a cell with a datetime in it. The cell in question contains "7/22/2011 0:00," and when I right-click->format cells, it tells me the category is "Custom" (not in the standard date category), and of type "m/d/yyyy h:mm." Yet when I select the cell, the formula pane displays it as "7/22/2011 12:00:00AM." So all three of these attempts to categorize the datatypes don't match up.

一些背景：对于我正在处理的项目（从给定的客户数据 Excel 电子表格构建 XML DOM），我需要能够读取带有日期时间的单元格的内容。有问题的单元格包含“7/22/2011 0:00”，当我右键单击 -> 格式化单元格时，它告诉我类别是“自定义”（不在标准日期类别中），并且类型是“m /d/yyyy h:mm。” 然而，当我选择单元格时，公式窗格将其显示为“7/22/2011 12:00:00AM”。因此，所有这三种对数据类型进行分类的尝试都不匹配。

The problem:When I display the cell contents using ActiveWorkbook.ActiveSheet.Cells(x,y) in a MsgBox for debugging purposes, it only shows 7/22/2011 (cutting off the time). It can't be the space in between the date and time that throws it off, as I am successfully reading cells with spaces in them elsewhere in the spreadsheet.

问题：当我在 MsgBox 中使用 ActiveWorkbook.ActiveSheet.Cells(x,y) 显示单元格内容以进行调试时，它只显示 7/22/2011（截断时间）。它不能是日期和时间之间的空格，因为我正在成功读取电子表格中其他地方带有空格的单元格。

Can anyone tell me why this is happening, or point me in the right direction for a VBA/Excel method that doesn't do this weird cropping thing that Sheet.Cells(x,y) is doing? Thanks. If only I had a penny for every time datetime datatypes caused problems for me..

谁能告诉我为什么会发生这种情况，或者为 VBA/Excel 方法指出正确的方向，该方法不会执行 Sheet.Cells(x,y) 正在做的这种奇怪的裁剪操作？谢谢。如果每次 datetime 数据类型对我造成问题时我都有一分钱就好了。

回答by Codo

Internally, Excel stores dates as numbers. It doesn't implement a date type. The number is the number of days since some point in the past (1900 or 1904, depending on the operation system, with some mistakes built-in regarding leap years). Time is represented as the fractional part of the number.

在内部，Excel 将日期存储为数字。它没有实现日期类型。该数字是自过去某个时间点以来的天数（1900 年或 1904 年，取决于操作系统，有一些关于闰年的内置错误）。时间表示为数字的小数部分。

To make it look like a date to the user, you have to assign the cell a date format. Then the number is displayed as a date in the cell. (Excel assigns a date format automatically if you enter somethings that looks like a date to Excel.)

为了使它看起来像用户的日期，您必须为单元格分配日期格式。然后该数字在单元格中显示为日期。（如果您在 Excel 中输入看起来像日期的内容，Excel 会自动分配日期格式。）

When you use ActiveWorkbook.ActiveSheet.Cells(x,y), you create a Rangeobject and call its default property, which is Value. Valuehas a lot of magic built-in. In your case, it looks at the cell format and - seeing a date format - in converts the internally stored number into a Variantof subtype date. When you display it using a message box, the next trick happens. If the time is 0:00, the date is converted to a string without time. If the time were different from 0:00, it would be converted to a string with date and time. The date format is taken from your user's settings. Its independent of the date format you have assigned to the Excel cell.

当您使用 时ActiveWorkbook.ActiveSheet.Cells(x,y)，您将创建一个Range对象并调用其默认属性，即Value。Value内置了很多魔法。在您的情况下，它查看单元格格式，并查看日期格式，将内部存储的数字转换Variant为子类型日期。当您使用消息框显示它时，下一个技巧就会发生。如果时间为 0:00，则将日期转换为不带时间的字符串。如果时间与 0:00 不同，则将其转换为带有日期和时间的字符串。日期格式取自您的用户设置。它与您分配给 Excel 单元格的日期格式无关。

If you use Value2instead of Value(e.g. by using a messag box to display ActiveWorkbook.ActiveSheet.Cells(x,y).Value2), then you'll see the internal representation of the date, namely a number.

如果您使用Value2代替Value（例如通过使用消息框来显示ActiveWorkbook.ActiveSheet.Cells(x,y).Value2），那么您将看到日期的内部表示，即一个数字。

When you edit a cell, Excel uses yet another date format so you can see and edit all parts of the date: year, month, day and possibly hour, minutes and seconds. This is because your cell's date format could be restricted to just the month name.

当您编辑单元格时，Excel 会使用另一种日期格式，因此您可以查看和编辑日期的所有部分：年、月、日以及可能的时、分和秒。这是因为您的单元格的日期格式可能仅限于月份名称。

Another complexity is added by internationalization. Certain date formats aren't applied directly but are a hint for using a date format from the current user's settings. So the format is first replaced with another date format and then applied. Furthermore, certain parts of the date and time formats are affected by the user's settings. And finally, the patterns of the date format are translated (in English, yyyystands for the year, in German it's jjjj). When the Excel spreadsheet is saved, these formats are stored in the English form so that the sheet can be opened by user's and Excel installations with any language. In your case, internationalization probably affects the date format used when you edit the cell (putting month before day and using 12-hour display with AM/PM looks like Northern America).

国际化增加了另一个复杂性。某些日期格式不是直接应用的，而是使用当前用户设置中的日期格式的提示。所以格式首先被替换为另一种日期格式，然后应用。此外，日期和时间格式的某些部分受用户设置的影响。最后，日期格式的模式被翻译（在英语中，yyyy代表年份，在德语中是jjjj）。保存 Excel 电子表格时，这些格式以英文形式存储，以便用户和 Excel 安装可以使用任何语言打开工作表。在您的情况下，国际化可能会影响您编辑单元格时使用的日期格式（将月份放在日期之前并使用上午/下午的 12 小时显示看起来像北美）。

I don't quite understand why Excel displays "7/22/2011 12:00:00AM" (instead of "7/22/2011 0:00:00AM"). I'm pretty sure your date/time has a time part of "0:00". But the internal number (as reveal by Value2) will tell you for sure.

我不太明白为什么 Excel 显示“7/22/2011 12:00:00AM”（而不是“7/22/2011 0:00:00AM”）。我很确定您的日期/时间的时间部分为“0:00”。但是内部编号（如 所揭示的Value2）会肯定地告诉您。

回答by Jon49

It's like a formula, in the cell it will show the result, in the formula bar it will show the formula. The cell you can format, the formula bar you cannot. So you can change the cell's format to however you would like it to look like.

它就像一个公式，在单元格中它会显示结果，在公式栏中它会显示公式。可以格式化的单元格，不能格式化的公式栏。因此，您可以将单元格的格式更改为您想要的样子。

So, if you want to format the msgbox then you would need to do the following:

因此，如果要格式化 msgbox，则需要执行以下操作：

MsgBox (Format(ActiveWorkbook.ActiveSheet.Cells(x,y), "m/d/yyyy h:mm")