DATEDIF函数是一个隐藏的日期函数,继承于Lotus 1-2-3。这个函数用于计算两个时间点之间的间隔,并且可以以“日”、“月”、“年”为单位计算间隔数。一般来说,用这个函数会比直接使用日期运算来的简单,但是这个函数并不是那么可靠,偶尔会犯点小毛病。所以就我本人来说,一般情况下都会用其他方式来替代实现它的功能。阅读本篇博文需要一定的函数公式理解能力,初学者慎入。
从不同的角度来看,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代表时间段内的第一个日期或起始日期。参数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
关于此参数算法的讨论,可参考以前的一个老帖:http://club.excelhome.net/viewthread.php?tid=165589
3,=Datedif(A2,B2,"D")
此参数含义为返回时间段内的天数,
这个参数算法最简单,实质就是两个Date相减得到的天数差,其算法可以表示为以下的公式:
=B2-A2