[讨论]使用Datedif函数中奇怪的3月1日和3月2日

lbpp · 发表于 2008-9-28 12:06

由于 VBA中无法调用工作表函数datedif，所以就打算自己动手写一个，但无法搞清楚这个函数的算法，搜遍论坛，找到了一个朋友写的函数（不好意思，当时没记下地址），在分析了该朋友的算法后，自己动手写了一个。

为了验证编写的函数的正确性，于是用随机的方法产生大量数据运行测试，结果发现偶尔会有一些数据，自己编写的函数与datedif函数得出的结果不一致，由于发生错误的机率不大，于是把出错的数据记录下来，经过分析，发现所有出错的数据都第一个日期为月末，第二个日期为3月1日或3月2日。而且datedif(date1,date2,"md")得出的结果为－1,或－2.想了很久没想出这-1和－2是什么意思。在论坛搜索了一下，找到一遍讨论“DATEDIF函数计算月数的疑问”http://club.excelhome.net/viewthread.php?tid=165589&replyID=&skin=0 ，里面已经讲到关于月计计算的算法问题，受到一些启发，但还是搞不懂这个－1,或－2是什么意思。

请大家一起来讨论讨论。

附上我的那个测试工作簿。

z4US5guY.rar (123.27 KB, 下载次数: 122)

wshcw · 发表于 2008-9-28 13:38

2000-12-31 2008-3-1 计算结果为查询日开始至查询日结束的第 7年2个月差1 天
公式:
=SUBSTITUTE(IF(B1-A1>0,"计算结果为查询日开始至查询日结束的第 "&DATEDIF(A1,B1,"y")&"年"&DATEDIF(A1,B1,"ym")&"个月零"&DATEDIF(A1,B1,"md")&" 天",IF(B1-A1=0,"开始日和查询日在同一天","从查询日开始再过 "&DATEDIF(B1,A1,"y")&"年"&DATEDIF(B1,A1,"ym")&"个月零"&DATEDIF(B1,A1,"md")&" 天就是您要查询的日期")),"零-","差")

水星钓鱼 · 发表于 2008-9-28 13:47

QUOTE:

以下是引用lbpp在2008-9-28 12:06:00的发言：

由于 VBA中无法调用工作表函数datedif，所以就打算自己动手写一个，但无法搞清楚这个函数的算法，搜遍论坛，找到了一个朋友写的函数（不好意思，当时没记下地址），在分析了该朋友的算法后，自己动手写了一个。

微软不用datedif是因为。。。

所以VBA中有个替换的函数叫DateDiff。

最后佩服下楼住，自己编了个DateDiff函数。

[em17]

[此贴子已经被作者于2008-9-28 13:50:07编辑过]

lbpp · 发表于 2008-9-28 15:58

QUOTE:

以下是引用水星钓鱼在2008-9-28 13:47:00的发言：

微软不用datedif是因为。。。

所以VBA中有个替换的函数叫DateDiff。

最后佩服下楼住，自己编了个DateDiff函数。

[em17]

datedif 与 VBA中的datediff是不一样的，2005-12-31与2006-1-1，用datedif得到的结果为0，用datediff得到的结果为1。

这两个函数的结果类似年龄中的虚岁与实岁。：）

因为工作中需要用于计算工龄，所以不能用datediff来替代datedif，所以才自已动手写这么一个函数。

lbpp · 发表于 2008-9-28 16:03

QUOTE:

以下是引用wshcw在2008-9-28 13:38:00的发言：
2000-12-31 2008-3-1 计算结果为查询日开始至查询日结束的第 7年2个月差1 天
公式:
=SUBSTITUTE(IF(B1-A1>0,"计算结果为查询日开始至查询日结束的第 "&DATEDIF(A1,B1,"y")&"年"&DATEDIF(A1,B1,"ym")&"个月零"&DATEDIF(A1,B1,"md")&" 天",IF(B1-A1=0,"开始日和查询日在同一天","从查询日开始再过 "&DATEDIF(B1,A1,"y")&"年"&DATEDIF(B1,A1,"ym")&"个月零"&DATEDIF(B1,A1,"md")&" 天就是您要查询的日期")),"零-","差")

这么说倒有点道理，但不知为什么其他的日期不会出现负数，只出现在3月1日和2日这两天？开始以为和闰年有关，但结果还是没找到原因。

同样为月末和月初如果我把2008-3-1改为2008-4-1，就不存在这个问题？

wshcw · 发表于 2008-9-28 18:24

QUOTE:

以下是引用lbpp在2008-9-28 16:03:00的发言：

这么说倒有点道理，但不知为什么其他的日期不会出现负数，只出现在3月1日和2日这两天？开始以为和闰年有关，但结果还是没找到原因。

同样为月末和月初如果我把2008-3-1改为2008-4-1，就不存在这个问题？

为什么负数天尽出在3月初呢?这也不难理解,这追溯到2008年2月份(2月有29天,平年2月时只有28天,会出现负2天的原因了).不足30天.如下例:

2000-12-31 2006-3-1 计算结果为查询日开始至查询日结束的第5年2个月差2 天(平年:最多-2 天,闰年最多-1天),你对照一下吧.是不是这样呢?

参考附件:

HLt92Cya.rar (14.96 KB, 下载次数: 758)

[此贴子已经被作者于2008-9-29 0:49:27编辑过]

chrisfang · 发表于 2009-4-13 11:07

md的算法，根据楼主的附件：
=MOD(DAY(B2)-DAY(A2),DAY(DATE(YEAR(B2),MONTH(B2)+1,0)))

lbpp · 发表于 2009-4-23 13:04

原帖由 chrisfang 于 2009-4-13 11:07 发表
md的算法，根据楼主的附件：
=MOD(DAY(B2)-DAY(A2),DAY(DATE(YEAR(B2),MONTH(B2)+1,0)))

1912-7-31

2011-3-1

呵呵，这个公式有点强，前半部分计算两个日期之间当月天数的差，后半部分计算日期2所在月份的天数，把这两个数求余，得到剩余的天数。但我觉得得到的答案不太对。如上面的公式中最后计算mod(-30,31)=-1余1,因此得到的答案为1。但这和我的那个计算公式得到的答案不同。
我又分析了一下我写的那个函数，我发现似乎我写的函数得到的答案是正确的，而datedif函数得到的答案在表述上有点问题，如上面两个日期datedif得到的答案是7个月-2天，我写的函数得到的答案是6个月29天。其实从答案上来看似乎表示的是同一天。不知为什么在datedif中要这样来表示这个结果？
搞不懂！

现在我已经基本能确定我的函数是正确的，这下我以后可以大胆用了。

chrisfang · 发表于 2009-7-23 17:19

今天查看自己参与的帖子的时候发现了这里存在的问题。
上面提到的MD的算法，在4月份当时我是测试过的，当时用的是Excel2007 sp1版。现在升级到SP2版本以后发现结果出现了变化，于是和楼主所说的一样有差异了。
看来在sp1到sp2升级过程中，微软已经更改了datedif函数的算法。
可以看两张图来比较一下两个版本不同的运算结果：
SP1版：

SP2版：

在SP2版本之下，我重新研究了一下Datedif函数参数为MD的算法。
基本确定其算法为：
=IF(DAY(B2)-DAY(A2)>=0,DAY(B2)-DAY(A2),B2-DATE(YEAR(B2),MONTH(B2)-1,DAY(A2)))
用语言来描述这个算法，就是当B2的日期（即day(B2)）大于等于A2的日期时，直接将日期相减的差值就是函数结果；而当B2的日期小于A2的日期时，则将B2减去B2的前一个月中的A2的日期。

按我的理解，微软是按照这个算法思路来制作这个函数参数的。但在实际情况中，目前SP2版本下的这个Datedif函数还是存在bug。
当B2的年份为闰年，月份为1月份，且B2的日期小于A2的日期时，函数运算结果就会出错。结果会出现一个大于100的数字，对于计算日期的差值来说，出现这样的结果是不合理的。

chrisfang · 发表于 2009-7-23 17:28

上面这个替代算法也可以写成这样：
=MOD(DAY(B2)-DAY(A2),DAY(TEXT(B2,"y-m-!1")-1))

呵呵，有点奇怪，第一次写的时候那个感叹号没显示出来。

[ 本帖最后由 chrisfang 于 2009-7-23 23:49 编辑 ]

		自动登录	找回密码
密码			免费注册