Excel VBA 编程基础 -- 日期与时间(四)- 日期(四)

关于日期类型的一些注意事项。

日期 1899-12-30

我们已经说过，日期类型内部表示为双精度浮点数，整数部分表示日期，小数部分表示时间，正数表示 1899 年 12 月 30 日以后的日期，负数表示 1899 年 12 月 30 日以前的日期，0 表示 1899 年 12 月 30 日。日期类型所能表示的日期范围是公元 100 年 1 月 1 日（-657434）至公元 9999 年 12 月 31 日（2958465）。

但日期 1899 年 12 月 30 日的表示法与其他日期有些不同。

我们曾说过，使用 # 括起的日期常量可以表示某个日期，但却不能表示表示 1899 年 12 月 30 日，如下面代码所示：

Dim myDate As DatemyDate = #12/30/1899#  ' 回车以后，VBA 自动就变成如下的时间形式myDate = #12:00:00 AM# ' 这是中午 12 点的时间Debug.Print FormatDateTime(myDate, vbShortDate) ' 1899-12-30

由FormatDateTime 函数的输出结果可以看出，虽然 myDate 的表示变成了时间形式，但 myDate 的值仍然是正确的。

下面是使用“构造函数”的结果：

myDate = DateSerial(1899, 12, 30)Debug.Print FormatDateTime(myDate, vbShortDate)  ' 1899-12-30myDate = DateValue("1899-12-30")Debug.Print FormatDateTime(myDate, vbShortDate)  ' 1899-12-30

那此时 myDate 的内部表示又是什么呢？

Debug.Print CDbl(myDate)   ' 0

上面说过，0 表示 1899 年 12 月 30 日，而且日期类型的内部表示为双精度浮点数，上面的结果印证了这一点。

Excel 自动识别的日期

Excel 可以自动识别单元格内的数据类型，但有些诡异。

图1 Excel 自动识别的日期

由图可以看出，Excel 可以自动识别 1900-1-1 及以后的日期，但却将 1899-12-31 及以前的日期作为文本对待，也就是不能识别 1899-12-31 及以前的日期。

我们来看 VBA 代码读出的结果，如下图所示：

图2 VBA 读 Excel 日期

首先，我们看到，1899-12-31 及以前的日期，Excel 统一作为文本对待，VBA 读出的值，其 TypeName 为 String 就说明了这一点。

其次，1900-1-1 及以后的日期，Excel 确实自动识别为日期类型，VBA 读出的值，其 TypeName 为 Date 也说明了这一点。

第三，1900-1-1 至 1900-2-29 这几个日期值，Excel 的值与 VBA 读出来的值相差 1，即 Excel 的值 1900-1-1，VBA 读出来的却是 1899-12-31！为什么会这样？

问题出在 1900-2-29！这个日期不存在！

我们看 C# 对这个日期的处理：

图3 C# 对 1900-2-29 的处理

从图可见，当我们用 1900-2-29 这个日期构造一个 DateTime 类型时，C# 抛出异常，说这个年、月、日参数描述了一个“不可表示的日期类型”（an un-representable DateTime）。

但 Excel 为什么会有这样一个不存在的日期呢？

问题出在 Lotus 1-2-3。

1983 年，Lotus 推出 Lotus 1-2-3，开发者为了节省宝贵的 CPU 和内存资源，在计算闰年时取了个巧：认为年份能够整除 4 的话，就是闰年。这样一来，1900 年就顺理成章地成了闰年，由此就有了 1900-2-29 这一天。实际上，1900 年不是闰年，因此 1900-2-29 不存在！

等到微软在推出 Excel 时，Lotus 1-2-3 已经成了市场的主流，因此微软只能采取与 Lotus 1-2-3 兼容的做法，容许 1900-2-29 的存在！可以说，这个 bug 在几乎所有的电子表格软件中都存在。兼容性已经变得如此重要，以至于改正这个 bug 变得不可能。

由于这个 bug 的存在，使得 Excel 和 VBA 在处理 1900-1-1 到 1900-2-29 这段日期时出现了不一致：

Excel 可以表示 1900-2-29，但这个日期在 VBA 中没意义

DateSerial(1900, 2, 29) => 1900-03-01
DateValue("1900-2-29") 出错！

为了避开 1900-2-29 这个“幽灵”，Excel 的日期 1900-1-1，读到 VBA 里就变成了 1899-12-31，1900-2-29 读到 VBA 中就成了 1900-2-28。

如果你用不到 1900 年，可以将 Excel 设置为使用 1904 日期系统，如图：

图4 使用 1904 日期系统

勾选“使用 1904 日期系统”复选框，即可。

附：关于闰年（leap year）

地球绕太阳一周大约需要 365.2425 天，所以大约每隔 4 年需要补足一天，这一天就补在 2 月份，因此每到闰年，2 月份就是 29 天。

闰年算法：

如果年份能被 4 整除，即是候选闰年；
如果候选闰年能被 100 整除但不能被 400 整除，则不是闰年。

Lotus 1-2-3 在计算闰年时，只检查了第一步，没有检查第二步。1900 能被 4 整除，同时能被 100 整除却不能被 400 整除，因此不是闰年。

代码如下：

Dim y As IntegerDim IsLeapYear As Booleany = year(Date())IsLeapYear = (y Mod 4 = 0) And Not (y Mod 100 = 0 And y Mod 400 <> 0)

在这段代码中，满足条件 y Mod 4 = 0 的 y 是候选闰年，还得要从候选闰年中去掉那些能被 100 整除但不能被 400 整除的年份，Not (y Mod 100 = 0 And y Mod 400 <> 0) 就去掉了那些“假闰年”。两个条件的合取（And）就是闰年的充分必要条件。