| 楼主 chrisfang |
从不同的角度来看，Datedif函数都是一个比较特殊的函数： 1）在多个Excel版本中，Datedif函数都是隐藏函数，没有出现在函数列表中，Excel2007中的公式自动完成功能也不会自动生成这个函数名称，甚至在多个版本中的帮助文件中都找不到这个函数的踪影。 2）在多个版本中，Datedif函数的算法发生了改变，据我目前所知，Excel2003 SP3、Excel2007 SP1、Excel2007 SP2 以及还未正式上市的Excel 2010中，这个函数的运算结果都有所不同。更早期的版本尚无研究。 3）工作表函数Datedif与VBA中的函数Datediff也不相同。 本文将主要以Excel 2007 SP2版本中的Datedif函数运算作为研究对象，并附上Excel 2003 SP3的相应结果作为参考。请使用正确的版本打开附件，否则将会出现不同的运算结果。 Excel早期版本的帮助文件中，对Datedif函数的解释如下： DATEDIF(start_date,end_date,unit) 参数start_date代表时间段内的第一个日期或起始日期。可以是带引号的日期文本串（例如 "2001/1/30"）、日期序列值或其他公式或函数的运算结果（例如DATE(2001,1,30)）等。 参数end_date代表时间段内的最后一个日期或结束日期。 参数unit为所需信息的返回时间单位代码。各代码对应的含义见下面表格。 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日期。 综合以上算法解释，这个参数的算法可以表示为以下的公式：
或
2，=Datedif(A2,B2,"M") 此参数含义为返回时间段内的整月数， 要判断整月数，也是与A2、B2的所在月份及日期相关。 此参数的算法为：将B2、A2相减得到的天数记为Days1，从A2开始到B2的前一个月的所有月份的天数和值记为Days2，如果Days1大于等于Days2，则满足最后一个月的整月条件，否则则不足最后一个月的整月。 换言之，使用此参数时，首先计算前后日期之间的差值，然后以起始月到(中止月-1)之间的整月天数作为计算“整月”的依据，差值大于或等于整月天数的，函数结果就是（中止月-起始月）；如果差值小于整月天数，函数结果就是（中止月-起始月-1）。 综合以上算法解释，这个参数的算法可以表示为以下的公式：
关于此参数算法的讨论，可参考以前的一个老帖：http://club.excelhome.net/viewthread.php?tid=165589 3，=Datedif(A2,B2,"D") 此参数含义为返回时间段内的天数， 这个参数算法最简单，实质就是两个Date相减得到的天数差，其算法可以表示为以下的公式：
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 2007 SP2版本中包含bug，当满足上面第二个条件且B2日期为闰年的1月份日期时，函数结果会偏大164。这个bug在Excel2003 SP3版本中不存在，但在目前尚未发布的Excel 2010中仍有这个问题存在，只不过那个版本中的差值为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，用公式来表达就是：
II)当day(B2)<day(A2)时，将B2与Date(B2所在年份、B2的前一个月份、A2的day)相减，当差值小于零时取零作为结果。用公式来表达就是：
当然，这两种方法只是本人的建议，仅供参考。 综合以上算法解释，这个参数在不够减的时候借位是以B2为基准的，这个参数的算法可以表示为以下的公式：
其中“+164*(TEXT(DATE(YEAR(B2),MONTH(B2)+1,29),"m-d")="2-29")”的部分是对上面第三点中提到的闰年bug的模拟。如果要排除闰年的错误，则可以使用下面的公式：
关于这个参数算法的讨论，还在这个帖子中进行过：http://club.excelhome.net/viewthread.php?tid=357741 5，=Datedif(A2,B2,"YM") 此参数含义为返回时间段内的整月数，忽略日和年。 这里提到了“忽略”日，但实际与参数“M”一样，还是有关日期的相关计算。这个参数的算法实际上与参数“M”的算法一致，只是忽略其中年份差中所包含的月份数。 其算法可以表示为以下的公式，其中引用了Datedif函数的“M”参数方便公式编写：
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中不存在，但在Excel 2010中依旧存在，且差值变为113。 综合以上算法解释，这个参数的算法可以表示为以下的公式（上面的文字不好理解，如果能看懂下面的公式则比较容易理解上述算法）：
其中“+164*(TEXT(DATE(YEAR(B2),MONTH(B2)+1,29),"m-d")="2-29")*(DAY(B2)<DAY(A2))”的部分为闰年bug的模拟，如果希望排除闰年的错误，可以将这部分内容去除。 以上分析了6个参数详细算法含义，由于无法得到详细的内部运算代码，因此以上分析结论全部来源于Datedif函数公式的黑箱测试。欢迎有兴趣的朋友继续测试和分析。以下提供附件： 附件1：Excel2007版（请用Excel 2007 SP2版本打开）  Datedif函数解析_chrisfang.rar附件2：Excel2007版，包含随机重复进行验算的宏（请用Excel 2007 SP2版本打开） Datedif函数解析_chrisfang_含重算验算.rar附件3：Excel2003版，2003版中不含闰年bug，其余算法与2007类似（请用Excel 2003 SP3版本打开） Datedif函数解析_chrisfang_2003.rar注意，以上算法研究基于Excel 2003的SP3版本和Excel 2007的SP2版本。目前已发现2007的不同版本中算法和结果有所不同，还没有具体研究。 |
| 2楼 gouweicao78 |
谢谢方兄分享。 |
| 3楼 BIN_YANG168 |
谢谢阿方分享。 |
| 4楼 syz105729913 |
谢谢方版，收藏学习了。 |
| 5楼 aichong |
这样的好贴最好让微软的开发人员看到，希望2010版本不再出现BUG，呵呵。 |
| 6楼 tongliaozyr |
谢谢方兄分享。 |
| 7楼 ahui21 |
收藏慢慢学习。 |
| 8楼 wangqilong1980 |
逝者如斯夫 不舍昼夜。 |
| 9楼 吉七儿 |
很完整，涵盖较全面，谢谢楼主。 |
| 10楼 海洋之星 |
谢谢分享，收藏了 |
| 11楼 传递 |
上次看到一个“bbb”的佛历 |
| 12楼 健康快乐123 |
很高深啊，只可以看懂其中的一小部分 |
| 13楼 larkzh |
好帖！经常用这个函数，没想到有这么多的玄机。 |
| 14楼 Rongson_Chart |
学习了~ |
| 15楼 じ☆潴の︵ゞ |
谢谢讲解,太明了啦 |
| 16楼 kuangben |
这帖子，应该顶起来！牛人，感谢 |
| 17楼 花鹿と火斂 |
 收藏 |
| 18楼 tsoyzm |
这个函数的第三个参数，一直没搞懂 |
| 19楼 kyser |
好东西 谢谢分享 |
| 20楼 maxizer0218 |
mark，很全面很实用，谢谢分享 |
| 21楼 天凉好个秋 |
谢谢，太详细了。学习一下。 |
| 22楼 小风筝 |
两个日期直接的距离，第三个参数就是如何表示这个距离的方式，年月日 |
| 23楼 jackenjun |
2007里面没有这个函数吗？怎么操作起来没有效果？ |
| 24楼 海洋之星 |
不错，值得收藏 |
| 25楼 芐雨 |
 |
| 26楼 海洋之星 |
谢谢分享 |
| 27楼 lrlxxqxa |
|
| 28楼 731w |
 很详细，工作中经常用到，谢谢。 |
| 29楼 feishifan |
谢谢谢方版， 这个得好好学习 |
| 30楼 jellyn |
请问如何用年月日的形式显示两个日期之间的相差的时间呢？ |
| 31楼 过期品24 |
学习 |
| 32楼 stacyshan1125 |
菜鸟来围观学习 |