Datedif函数全面解析及BUG分析 datedif函数在哪里

以前发在EHblog的老博文,现在关闭了,准备陆陆续续搬迁一点过来。时间久了,有些文章也不一定保值了。

DATEDIF函数是一个隐藏的日期函数,继承于Lotus1-2-3。这个函数用于计算两个时间点之间的间隔,并且可以以“日”、“月”、“年”为单位计算间隔数。一般来说,用这个函数会比直接使用日期运算来的简单,但是这个函数并不是那么可靠,偶尔会犯点小毛病。所以就我本人来说,一般情况下都会用其他方式来替代实现它的功能。阅读本篇博文需要一定的函数公式理解能力,初学者慎入。

————————————————————————————————————————————

原文发表于2009-7-27

从不同的角度来看,Datedif函数都是一个比较特殊的函数:
1)在多个Excel版本中,Datedif函数都是隐藏函数,没有出现在函数列表中,Excel2007中的公式自动完成功能也不会自动生成这个函数名称,甚至在多个版本中的帮助文件中都找不到这个函数的踪影。
2)在多个版本中,Datedif函数的算法发生了改变,据我目前所知,Excel2003SP3、Excel2007 SP1、Excel2007 SP2 以及还未正式上市的Excel2010中,这个函数的运算结果都有所不同。更早期的版本尚无研究。
3)工作表函数Datedif与VBA中的函数Datediff也不相同。

本文将主要以Excel 2007SP2版本中的Datedif函数运算作为研究对象,并附上Excel 2003SP3的相应结果作为参考。请使用正确的版本打开附件,否则将会出现不同的运算结果。

Datedif函数全面解析及BUG分析 datedif函数在哪里
Excel早期版本的帮助文件中,对Datedif函数的解释如下:

DATEDIF(start_date,end_date,unit)
参数start_date代表时间段内的第一个日期或起始日期。参数end_date代表时间段内的最后一个日期或结束日期。参数unit为所需信息的返回时间单位代码。各代码对应的含义如下:

"y"——时间段中的整年数。

"m"——时间段中的整月数。

"d"——时间段中的天数。

"md"——start_date与end_date日期中天数的差。忽略日期中的月和年。

"ym"——start_date与end_date日期中月数的差。忽略日期中的日和年。

"yd"——start_date与end_date日期中天数的差。忽略日期中的年。

这6个unit参数看上去极其简单,无非就是年月日的差值运算,但其实里面包含了许多玄机,下面将针对这6种unit代码分别进行详解:
以下假定start_date存放于A2单元格内,end_date存放于B2单元格内

1,=Datedif(A2,B2,"Y")

此参数含义为返回时间段内的整年数,
1)所谓“整年”的判断包含了两个日期值(m-d)的大小判断,假定A2与B2相差一年,如果B2的日期值小于A2的日期值,则不满一整年;如果B2的日期值大于等于A2的日期值,则可以记为一整年。
2)对于包含闰年的情况,不影响日期值大小的判断,例如A2为闰年的2月29日,则B2为闰年的2月29日及以后或非闰年的3月1日及以后都可以判断为大于等于A2日期。

综合以上算法解释,这个参数的算法可以表示为以下的公式:
=YEAR(B2)-YEAR(A2)-1+(DATE(YEAR(B2),MONTH(A2),DAY(A2))<=B2)

=YEAR(B2)-YEAR(A2)-1+(A2<=DATE(YEAR(A2),MONTH(B2),DAY(B2)))


2,=Datedif(A2,B2,"M")

此参数含义为返回时间段内的整月数,
要判断整月数,也是与A2、B2的所在月份及日期相关。
此参数的算法为:将B2、A2相减得到的天数记为Days1,从A2开始到B2的前一个月的所有月份的天数和值记为Days2,如果Days1大于等于Days2,则满足最后一个月的整月条件,否则则不足最后一个月的整月。
换言之,使用此参数时,首先计算前后日期之间的差值,然后以起始月到(中止月-1)之间的整月天数作为计算“整月”的依据,差值大于或等于整月天数的,函数结果就是(中止月-起始月);如果差值小于整月天数,函数结果就是(中止月-起始月-1)。

综合以上算法解释,这个参数的算法可以表示为以下的公式:
=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)-(B2-A2<(TEXT(B2,"yyyy-m-1")-TEXT(A2,"yyyy-m-1")))*1

关于此参数算法的讨论,可参考以前的一个老帖:http://club.excelhome.net/viewthread.php?tid=165589


3,=Datedif(A2,B2,"D")

此参数含义为返回时间段内的天数,
这个参数算法最简单,实质就是两个Date相减得到的天数差,其算法可以表示为以下的公式:
=B2-A2

4,=Datedif(A2,B2,"MD")
此参数含义为返回时间段内的天数,忽略月和年。
虽然说“忽略”月和年,但实际上当B2的day小于A2的day时,两者的日期差为负数,需要借位相减才能得到正数。如何借位,向谁借位就涉及到了两个日期的所在月份及其年份。
此参数算法包含以下几部分:
1)当B2的day大于等于A2的day时,可直接将两者的day相减得到结果。
例如A2为2003年3月4日,B2为2004年1月9日,其中的B2的day为9,A2的day为4,则函数结果为9-4=5。
2)当B2的day小于A2的day时,以B2所在日期作为基准,将B2减去Date(B2所在年份、B2的前一个月份、A2的day)所得到的差值为结果。
例如A2为2003年3月4日,B2为2004年2月3日,则将B2减去2004年1月4日的天数差作为函数结果。假如B2的月份为1月,则其前一个月份为前一年的12月。
3)此参数在Excel 2007SP2版本中包含bug,当满足上面第二个条件且B2日期为闰年的1月份日期时,函数结果会偏大164。这个bug在Excel2003SP3版本中不存在,但在目前尚未发布的Excel2010中仍有这个问题存在,只不过那个版本中的差值为113。这个莫名其妙的数值如何出现的,目前暂时没搞清楚。
4)此参数包含的另一个问题可能不能算bug,但在各个版本中都存在,由于第二条算法的原因,当A2的day为29、30、31且B2的月份为3月份时,由于B2的前一个月份即2月份中没有29号、30号、31号,Date(B2所在年份、B2的前一个月份、A2的day)会由Excel自动将这样的date转换为3月1日、3月2日、3月3日,由此产生误差会出现0和负数。对于这样一个计算两个日期差的函数来说,出现负数好像有点不太合理。
例如,A2日期为2003年5月31日,B2日期为2005年3月1日,date(2005,2,31)=2005年3月3日,因此B2与此日期相减得到结果为-2。
基于第4点的问题,个人认为有以下两种算法可能会更合理一些:
I)当day(B2)<day(A2)时,将B2与其当月1日相减+A2的月末与A2相减+1,用公式来表达就是:
=IF(DAY(B2)-DAY(A2)>=0,DAY(B2)-DAY(A2),B2-TEXT(B2,"yyyy-m-1")+DATE(YEAR(A2),MONTH(A2)+1,1)-A2)
II)当day(B2)<day(A2)时,将B2与Date(B2所在年份、B2的前一个月份、A2的day)相减,当差值小于零时取零作为结果。用公式来表达就是:
=IF(DAY(B2)-DAY(A2)>=0,DAY(B2)-DAY(A2),MAX(B2-DATE(YEAR(B2),MONTH(B2)-1,DAY(A2)),0))
当然,这两种方法只是本人的建议,仅供参考。

综合以上算法解释,这个参数在不够减的时候借位是以B2为基准的,这个参数的算法可以表示为以下的公式:
=IF(DAY(B2)-DAY(A2)>=0,DAY(B2)-DAY(A2),B2-DATE(YEAR(B2),MONTH(B2)-1,DAY(A2))+164*(TEXT(DATE(YEAR(B2),MONTH(B2)+1,29),"m-d")="2-29"))

其中包含下划线的部分是对上面第三点中提到的闰年bug的模拟。如果要排除闰年的错误,则可以使用下面的公式:
=IF(DAY(B2)-DAY(A2)>=0,DAY(B2)-DAY(A2),B2-DATE(YEAR(B2),MONTH(B2)-1,DAY(A2)))

关于这个参数算法的讨论,还在这个帖子中进行过:http://club.excelhome.net/viewthread.php?tid=357741


5,=Datedif(A2,B2,"YM")
此参数含义为返回时间段内的整月数,忽略日和年。
这里提到了“忽略”日,但实际与参数“M”一样,还是有关日期的相关计算。这个参数的算法实际上与参数“M”的算法一致,只是忽略其中年份差中所包含的月份数。

其算法可以表示为以下的公式,其中引用了Datedif函数的“M”参数方便公式编写:
=MOD(DATEDIF(A2,B2,"m"),12)

6,=Datedif(A2,B2,"YD")
此参数含义为返回时间段内的天数,忽略其中的年。
这个参数的算法比较复杂,情况比较多,简单地说包括以下几个重点:
1)当B2月份为3月份且B2的day大于等于A2的day时,两者相减是以A2的所在年份为基准的(如果够减,则以[A2的年份&B2的日期]与A2相减;如果不够减,则以[A2年份+1&B2的日期]与A2相减)
2)当B2月份为3月份且B2的day小于A2的day时,两者相减是以B2的所在年份为基准的(如果够减,则以B2与[B2的年份&A2的日期]相减;如果不够减,则以B2与[B2年份-1&A2的日期]相减)
3)当B2的月份不是3月份时,两者相减是以A2的所在年份为基准的,相减方式同第一条。
4)当B2的day小于A2的day,且B2日期是闰年的1月份日期,且B2与A2日期不直接够减时,存在着与“MD”参数类似的闰年bug,函数结果偏大164。这个bug在Excel2003的SP3中不存在,但在Excel2010中依旧存在,且差值变为113。

综合以上算法解释,这个参数的算法可以表示为以下的公式(上面的文字不好理解,如果能看懂下面的公式则比较容易理解上述算法):
=IF(--(TEXT(B2,"!0!0-m-d"))>=--(TEXT(A2,"!0!0-m-d")),IF((DAY(B2)<DAY(A2))*(MONTH(B2)=3),B2-DATE(YEAR(B2),MONTH(A2),DAY(A2)),DATE(YEAR(A2),MONTH(B2),DAY(B2))-A2),IF((DAY(B2)<DAY(A2))*(MONTH(B2)=3),B2-DATE(YEAR(B2)-1,MONTH(A2),DAY(A2)),DATE(YEAR(A2)+1,MONTH(B2),DAY(B2))-A2+164*(TEXT(DATE(YEAR(B2),MONTH(B2)+1,29),"m-d")="2-29")*(DAY(B2)<DAY(A2))))


其中包含下划线的部分为闰年bug的模拟,如果希望排除闰年的错误,可以将这部分内容去除。

以上分析了6个参数详细算法含义,由于无法得到详细的内部运算代码,因此以上分析结论全部来源于Datedif函数公式的黑箱测试。

欢迎有兴趣的朋友继续测试和分析。以下提供附件:

注意使用相应版本的Excel打开附件:

附件1:Excel2007版(请用Excel 2007SP2版本打开)

http://vdisk.weibo.com/s/3Vh1l
附件2:Excel2003版,2003版中不含闰年bug,其余算法与2007类似(请用Excel2003 SP3版本打开)

http://vdisk.weibo.com/s/3Vh6m

  

爱华网本文地址 » http://www.aihuau.com/a/25101011/68992.html

更多阅读

DNF疑难解析:9 DNF截图在哪里

DNF疑难解析:[9]DNF截图在哪里——简介 广大的地下城与勇士的战友们,你们是不是有时候会苦恼把游戏中的精彩环节,截图下来,结果翻遍了电脑的大小盘,却没有找到呢?下面,就让我来帮助大家找一下。DNF疑难解析:[9]DNF截图在哪里——DNF截图方

九阴真经门派的选择及优劣分析 九阴真经ol门派选择

九阴真经门派的选择及优劣分析——简介九阴真经门派选择介绍可以帮助玩家更加的了解每个门派,九阴真经门派选择介绍是每个新手玩家了解门派特色的最快途径。九阴真经门派的选择及优劣分析——工具/原料九阴真经九阴真经门派的选择

《仙域》蜀山加点及技能分析 武魂2蜀山加点

简介:《仙域》蜀山加点及技能分析工具/原料:《仙域》GCH0401方法/步骤:一。技能说明:基本剑法:修炼系数1伤害系数1冷却时间0攻击距离2醉仙剑法:修炼系数8伤害系数15冷却时间3攻击距离2七星剑法:修炼系数8伤害系数25冷却时间8攻击距

中美贸易摩擦原因及对策分析 中美贸易摩擦的原因

中美贸易摩擦原因及对策分析[摘要]近年来,在世界市场持续低迷的形势下,中国出口连续保持两位数的高速增长,现在已是第二大贸易国。日渐强劲的国际竞争力,向世人展示了一个正在崛起中的贸易大国的崭新形象,但也带来了日益激烈的国际贸易

俄罗斯最新石油资源数据解析及生产前景分析 俄罗斯发展前景

俄罗斯最新石油资源数据解析及生产前景分析摘要:俄罗斯石油资源非常丰富,陆上和海域远景勘探面积约为1290万平方千米,石油总资源量为1114.72亿吨;累计石油产量为212.25亿吨,探明石油可采储量(ABC1级)为180.22亿吨,初步评价可采储量(C2级)为10

声明:《Datedif函数全面解析及BUG分析 datedif函数在哪里》为网友醉酒诉相思分享!如侵犯到您的合法权益请联系我们删除