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

Question

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

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

有人能告诉我为什么会出现这种情况，或者指点我一个VBA / Excel方法，不会像Sheet.Cells（x，y）正在做的那样进行奇怪的裁剪吗？谢谢。如果每次日期时间数据类型引起问题时我都有一便士就好了...

- user520559

---

单元格的值和显示可能存在差异。如果单元格的值仅输入为7/22/2011（没有时间组件），但单元格格式化为显示时间，则这是有意义的。在消息框方案中，您不进行任何格式化，而只查看“原始”值，因此只获取日期... 如果您想要显示的值，请使用ActiveSheet.Cells(x,y).Text。 - Tim Williams

Answer 1

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

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

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

如果您使用Value2而不是Value（例如通过使用消息框显示ActiveWorkbook.ActiveSheet.Cells（x，y）。Value2），则将看到日期的内部表示形式，即数字。

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

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

我不太明白为什么Excel显示“7/22/2011 12:00:00AM”（而不是“7/22/2011 0:00:00AM”）。我非常确定您的日期/时间没有时间部分。“Value2”所显示的内部数字可以确保告诉您。

- Codo

---

非常好的解释，正是我所需要的。我通过输入另一个带有时间组件的日期时间进行了测试，结果显示得很好，所以根本没有问题哈哈。我想这只是因为对于 Excel 来说，午夜时间都是零，所以它只是截断了午夜时间。谢谢！ - user520559

Answer 2

这就像一个公式，在单元格中它将显示结果，在公式栏中它将显示公式。你可以格式化单元格，但不能格式化公式栏。因此，您可以更改单元格的格式，使其看起来符合您的要求。

所以，如果您想要格式化msgbox，那么您需要按照以下步骤进行：

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

- Jon49