ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

   
易厚学堂-专业的职场技能充电站 永久免费,网表让Excel秒变数据库 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 打造核心竞争力的职场宝典 13门Excel免费公开课任你学
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 免费的Excel考勤计算系统
查看: 10296|回复: 17

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 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, 下载次数: 98)

TA的精华主题

TA的得分主题

发表于 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")&" 天就是您要查询的日期")),"零-","差")

TA的精华主题

TA的得分主题

发表于 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编辑过]

TA的精华主题

TA的得分主题

 楼主| 发表于 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,所以才自已动手写这么一个函数。

TA的精华主题

TA的得分主题

 楼主| 发表于 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,就不存在这个问题?

TA的精华主题

TA的得分主题

发表于 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, 下载次数: 730)

TA的精华主题

TA的得分主题

发表于 2009-4-13 11:07 | 显示全部楼层
md的算法,根据楼主的附件:
=MOD(DAY(B2)-DAY(A2),DAY(DATE(YEAR(B2),MONTH(B2)+1,0)))

TA的精华主题

TA的得分主题

 楼主| 发表于 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-312011-3-1

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

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

TA的精华主题

TA的得分主题

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

SP2版:
2009-7-23 15-51-17.png


在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的数字,对于计算日期的差值来说,出现这样的结果是不合理的。

TA的精华主题

TA的得分主题

发表于 2009-7-23 17:28 | 显示全部楼层
上面这个替代算法也可以写成这样:
=MOD(DAY(B2)-DAY(A2),DAY(TEXT(B2,"y-m-!1")-1))

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

[ 本帖最后由 chrisfang 于 2009-7-23 23:49 编辑 ]
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关闭

最新热点上一条 /1 下一条

关注官方微信,高效办公专列,每天发车

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2018-10-18 23:34 , Processed in 0.105657 second(s), 14 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 2001-2017 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表