excel 无法正确处理 1900-03-01 前的日期

问题由来:excel 用公式 =TEXT(A1,"yyyy-mm-dd") 转日期时,当A1 的值等于59 的时候,返回值是1900-02-28;当A1 的值等于61 的时候,返回值是1900-03-01;那么当 A1的值为 60 的时候,返回值是多少?

根据给出的信息,当A1的值为59时,返回值是1900-02-28,而当A1的值为61时,返回值是1900-03-01。这意味着A1的值每增加1,日期增加一天。

因此,当A1的值为60时,日期应该是在1900-02-28和1900-03-01之间的一天,即1900-02-29。

但是,需要注意的是1900年并不是闰年,所以1900年没有2月29日。在这种情况下,Excel通常会将日期调整到下一天,即1900-03-01。

然而,根据题目中的描述,我们假设Excel在这里的处理方式是简单地忽略闰年规则,将日期直接加一天。

因此,当A1的值为60时,返回值将是1900-02-29。

在Excel中,日期是从1900年1月1日开始计算的,其中1900年1月1日对应序号1。Excel错误地将1900年当作闰年来处理,实际上1900年并不是闰年。这导致2月29日被错误地认为是有效日期。

当A1的值为59时,对应的日期是1900-02-28,增加1变为60,按照正常逻辑应当是1900-03-01,但由于Excel的这个错误,它会将1900年2月29日作为一个有效的日期来处理。

因此,当A1的值为60时,由于Excel的错误处理,返回值会错误地显示为1900-02-29,即使这一天在现实中并不存在。

所以确实可以认为这是一个BUG,但这是Excel的一个已知问题,而且是一个历史性的问题,自Excel最初版本以来就存在,并且在后续版本中为了兼容性的原因一直保留至今。

返回值是1900-02-29。

DateDiff("d", "1899-12-31", "1900-3-1") 返回值 60 是错误的,

DateDiff 函数的本意是计算两个日期之间的差值,通常不会考虑日期系统误差。然而,由于Excel中1900年日期系统的错误处理,导致在计算涉及1900年2月的日期差值时会出现问题。在这个具体的例子中:

DateDiff("d", "1899-12-31", "1900-3-1") 应该计算的是从1899年12月31日到1900年3月1日之间的天数。
正常情况下,这应该是61天(1月份31天 + 2月份28天 + 3月1日),因为1900年不是闰年。
但是由于Excel错误地将1900年作为闰年处理,它认为2月有29天,因此返回了错误的天数60。
这个问题没有直接的解决办法,因为它根植于Excel的底层日期处理机制。当使用DateDiff函数计算包含1900年2月的日期差值时,需要手动调整结果以抵消这个错误。

此外,在使用DateDiff进行日期差值计算时,应当注意以下几点:

确保日期正确:检查所有日期数据确保其准确性,特别是涉及到1900年的日期。
避免使用1900年2月的日期:在可能的情况下,避免使用1900年2月的日期作为计算范围的一部分,以免产生错误的结果。
手动调整:如果必须使用1900年的日期,可能需要在计算后手动调整结果以反映实际的日期差值。

DateDiff("d", 0, "1900-1-1") 返回值是 2 也是错误的。

Excel中的日期是从1900年1月1日开始计算的,其中1900年1月1日被当作第1天。因此,当使用DateDiff函数计算从基准日期(即1900年1月1日)到"1900-1-1"这一日期之间的天数差时,实际上就是在计算基准日期自身,所以结果是0。但是,由于某些原因,可能是由于四舍五入或内部处理误差,Excel错误地返回了2。

DATEVALUE("1900-02-29") 返回值是 60 ,是什么BUG?

这是由于Excel的日期系统BUG导致的。

在Excel中,日期是以一种称为序列号的数字形式存储的,起始点是1900年1月1日。理论上,1900年2月29日应该是第60天的序列号,因为1900年并不是一个闰年。但是,Excel错误地将1900年视为闰年,因此它将2月29日计入了序列号中。这就是为什么DATEVALUE("1900-02-29")返回值是60的原因。

这个BUG源于Excel最初的设计决策,当时并没有考虑到1900年实际上不是闰年。这一错误一直保留至今,尽管后续版本的Excel已经可以正确处理大多数日期问题。

需要注意的是,在使用DATEVALUE函数时,需要确保输入的日期文本格式正确,否则可能会遇到错误或不一致的结果。例如,如果电脑系统时间设置与Excel中的日期格式不匹配,可能会导致一些日期函数无法正常工作。

Excel 无法正确处理 1900-03-01 前的日期

主要是因为它使用了 1900 年作为日期的起始点。这导致了一些与现实不符的情况,例如将 1900 年错误地视为闰年。

为了解决这个问题,可以采取以下措施:

使用正确的日期格式:确保输入的日期符合正确的格式,如 "YYYY-MM-DD"。这可以帮助 Excel 正确解析和处理日期数据。
避免使用 1900 年 2 月 29 日:由于 Excel 错误地将 1900 年视为闰年,因此应避免使用该日期进行计算。可以选择其他日期或手动调整结果以纠正误差。
使用其他工具或编程语言:如果需要进行复杂的日期计算或处理大量数据,可以考虑使用其他工具或编程语言,如 Python 或 R。这些工具通常具有更强大的日期处理功能,并且不受 Excel 的限制。
更新系统设置:在某些情况下,可能需要更新操作系统或 Excel 的设置以解决日期问题。例如,某些版本的 Windows 可能需要更新以解决与 1900 年 2 月 29 日相关的问题。
总之,虽然 Excel 在处理日期方面存在一些限制和问题,但通过采取适当的措施,仍然可以有效地处理和计算日期数据。

本来计划自己写个程序将字符串日期转换为Excel默认的日期计数值,但是因为以上问题,无法得到正确的结果,代码如下:

Public Function StrDT2Long(d, Optional n = "d") As Long
    Dim S As String, SS
    SS = "1899-12-31"
    S = CStr(d)
    StrDT2Long = DateDiff(n, SS, S)
End Function

以上函数能正确处理 1900-1-1 到 1900-2-28 之前的日期输入,但是之后的就是错误的,结果少了1。于是乎进行修改,将 SS 改为 0 :

Public Function StrDT2Long(d, Optional n = "d") As Long
    Dim S As String, SS
    SS = 0 '"1899-12-31"
    S = CStr(d)
    StrDT2Long = DateDiff(n, SS, S)
End Function

这样可以正确处理 1900-3-1 至今的日期,但是无法正确处理 1900-3-1 之前的日期,结果多了1 。

因此,在 Excel 中处理 1900-3-1 之前的日期都要注意核对结果,

因为excel中存在 1900-01-00 和 1900-02-29 两个无效的日期。

因此处理现在的日期时,以上函数 SS 的值选择设为 0 即可,不要用1900-1-1、1899-12-31或1899-12-30。

上一篇:总结|性能优化思路及常用工具及手段


下一篇:Android startForegroundService与startForeground