DATEDIF函数全面解析和BUG分析.doc

Datedif函数全面解析和BUG分析 DATEDIF函数是一个隐藏的日期函数，一般来说，用这个函数会比直接使用日期运算来的简单，但是这个函数并不是那么可靠，偶尔会犯点小毛病。所以就来说，一般情况下都会用其他方式来替代实现它的功能。 从不同的角度来看，Datedif函数都是一个比较特殊的函数： 1)在多个Excel版本中，Datedif函数都是隐藏函数，没有出现在函数列表中，Excelxx中的公式自动完成功能也不会自动生成这个函数名称，甚至在多个版本中的帮助文件中都找不到这个函数的踪影。 2)在多个版本中，Datedif函数的算法发生了改变，据我目前所知，ExcelxxSP3、ExcelxxSP1、ExcelxxSP2以及还未正式上市的Excelxx中，这个函数的运算结果都有所不同。更早期的版本尚无研究。 3)工作表函数Datedif与VBA中的函数Datediff也不相同。 本文将主要以ExcelxxSP2版本中的Datedif函数运算作为研究对象，并附上ExcelxxSP3的相应结果作为参考。请使用正确的版本打开附件，否则将会出现不同的运算结果。 Excel早期版本的帮助文件中，对Datedif函数的解释如下： DATEDIF(start_date,end_date,unit) 参数start_date代表时间段内的第一个日期或起始日期。参数end_date代表时间段内的最后一个日期或结束日期。参数unit为所需信息的返回时间单位代码。各代码对应的含义如下： y时间段中的整年数。 m时间段中的整月数。 d时间段中的天数。 mdstart_date与end_date日期中天数的差。忽略日期中的月和年。 ymstart_date与end_date日期中月数的差。忽略日期中的日和年。 ydstart_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=0,DAY(B2)-DAY(A2),B2-TEXT(B2,yyyy-m-1)+DATE(YEAR(A2),MONTH(A2)+1,1)-A2) II)当day(B2) =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) 关于这个参数算法的讨论，还在这个帖子中进行过：club.excelhome./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在Excelxx的SP3中不存在，但在Excelxx中依旧存在，且差值变为113。 综合以上算法解释，这个