- 相關(guān)推薦
Datedif函數(shù)全面解析和BUG分析
DATEDIF函數(shù)是一個隱藏的日期函數(shù)，一般來說，用這個函數(shù)會比直接使用日期運算來的簡單，但是這個函數(shù)并不是那么可靠，偶爾會犯點小毛病。所以就小編來說，一般情況下都會用其他方式來替代實現(xiàn)它的功能。
從不同的角度來看，Datedif函數(shù)都是一個比較特殊的函數(shù)：
1)在多個Excel版本中，Datedif函數(shù)都是隱藏函數(shù)，沒有出現(xiàn)在函數(shù)列表中，Excel2007中的公式自動完成功能也不會自動生成這個函數(shù)名稱，甚至在多個版本中的幫助文件中都找不到這個函數(shù)的蹤影。
2)在多個版本中，Datedif函數(shù)的算法發(fā)生了改變，據(jù)我目前所知，Excel2003 SP3、Excel2007 SP1、Excel2007 SP2 以及還未正式上市的Excel 2010中，這個函數(shù)的運算結(jié)果都有所不同。更早期的版本尚無研究。
3)工作表函數(shù)Datedif與VBA中的函數(shù)Datediff也不相同。
本文將主要以Excel 2007 SP2版本中的Datedif函數(shù)運算作為研究對象，并附上Excel 2003 SP3的相應結(jié)果作為參考。請使用正確的版本打開附件，否則將會出現(xiàn)不同的運算結(jié)果。
Excel早期版本的幫助文件中，對Datedif函數(shù)的解釋如下：
DATEDIF(start_date,end_date,unit)
參數(shù)start_date代表時間段內(nèi)的第一個日期或起始日期。參數(shù)end_date代表時間段內(nèi)的最后一個日期或結(jié)束日期。參數(shù)unit為所需信息的返回時間單位代碼。各代碼對應的含義如下：
"y"——時間段中的整年數(shù)。
"m"——時間段中的整月數(shù)。
"d"——時間段中的天數(shù)。
"md"——start_date與end_date日期中天數(shù)的差。忽略日期中的月和年。
"ym"——start_date與end_date日期中月數(shù)的差。忽略日期中的日和年。
"yd"——start_date與end_date日期中天數(shù)的差。忽略日期中的年。
這6個unit參數(shù)看上去極其簡單，無非就是年月日的差值運算，但其實里面包含了許多玄機，下面將針對這6種unit代碼分別進行詳解：
以下假定start_date存放于A2單元格內(nèi)，end_date存放于B2單元格內(nèi)
1，=Datedif(A2,B2,"Y")
此參數(shù)含義為返回時間段內(nèi)的整年數(shù)，
1)所謂“整年”的判斷包含了兩個日期值(m-d)的大小判斷，假定A2與B2相差一年，如果B2的日期值小于A2的日期值，則不滿一整年;如果B2的日期值大于等于A2的日期值，則可以記為一整年。
2)對于包含閏年的情況，不影響日期值大小的判斷，例如A2為閏年的2月29日，則B2為閏年的2月29日及以后或非閏年的3月1日及以后都可以判斷為大于等于A2日期。
綜合以上算法解釋，這個參數(shù)的算法可以表示為以下的公式：
=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")
此參數(shù)含義為返回時間段內(nèi)的整月數(shù)，
要判斷整月數(shù)，也是與A2、B2的所在月份及日期相關(guān)。
此參數(shù)的算法為：將B2、A2相減得到的天數(shù)記為Days1，從A2開始到B2的前一個月的所有月份的天數(shù)和值記為Days2，如果Days1大于等于Days2，則滿足最后一個月的整月條件，否則則不足最后一個月的整月。
換言之，使用此參數(shù)時，首先計算前后日期之間的差值，然后以起始月到(中止月-1)之間的整月天數(shù)作為計算“整月”的依據(jù)，差值大于或等于整月天數(shù)的，函數(shù)結(jié)果就是(中止月-起始月);如果差值小于整月天數(shù)，函數(shù)結(jié)果就是(中止月-起始月-1)。
綜合以上算法解釋，這個參數(shù)的算法可以表示為以下的公式：
=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)-(B2-A2<(TEXT(B2,"yyyy-m-\1")-TEXT(A2,"yyyy-m-\1")))*1
關(guān)于此參數(shù)算法的討論，可參考以前的一個老帖：http://club.excelhome.net/viewthread.php?tid=165589
3，=Datedif(A2,B2,"D")
此參數(shù)含義為返回時間段內(nèi)的天數(shù)，
這個參數(shù)算法最簡單，實質(zhì)就是兩個Date相減得到的天數(shù)差，其算法可以表示為以下的公式：
=B2-A2
4，=Datedif(A2,B2,"MD")
此參數(shù)含義為返回時間段內(nèi)的天數(shù)，忽略月和年。
雖然說“忽略”月和年，但實際上當B2的day小于A2的day時，兩者的日期差為負數(shù)，需要借位相減才能得到正數(shù)。如何借位，向誰借位就涉及到了兩個日期的所在月份及其年份。
此參數(shù)算法包含以下幾部分：
1)當B2的day大于等于A2的day時，可直接將兩者的day相減得到結(jié)果。
例如A2為2003年3月4日，B2為2004年1月9日，其中的B2的day為9，A2的day為4，則函數(shù)結(jié)果為9-4=5。
2)當B2的day小于A2的day時，以B2所在日期作為基準，將B2減去Date(B2所在年份、B2的前一個月份、A2的day)所得到的差值為結(jié)果。
例如A2為2003年3月4日，B2為2004年2月3日，則將B2減去2004年1月4日的天數(shù)差作為函數(shù)結(jié)果。假如B2的月份為1月，則其前一個月份為前一年的12月。
3)此參數(shù)在Excel 2007 SP2版本中包含bug，當滿足上面第二個條件且B2日期為閏年的1月份日期時，函數(shù)結(jié)果會偏大164。這個bug在Excel2003 SP3版本中不存在，但在目前尚未發(fā)布的Excel 2010中仍有這個問題存在，只不過那個版本中的差值為113。這個莫名其妙的數(shù)值如何出現(xiàn)的，目前暫時沒搞清楚。
4)此參數(shù)包含的另一個問題可能不能算bug，但在各個版本中都存在，由于第二條算法的原因，當A2的day為29、30、31且B2的月份為3月份時，由于B2的前一個月份即2月份中沒有29號、30號、31號，Date(B2所在年份、B2的前一個月份、A2的day)會由Excel自動將這樣的date轉(zhuǎn)換為3月1日、3月2日、3月3日，由此產(chǎn)生誤差會出現(xiàn)0和負數(shù)。對于這樣一個計算兩個日期差的函數(shù)來說，出現(xiàn)負數(shù)好像有點不太合理。
例如，A2日期為2003年5月31日，B2日期為2005年3月1日，date(2005,2,31)=2005年3月3日，因此B2與此日期相減得到結(jié)果為-2。
基于第4點的問題，個人認為有以下兩種算法可能會更合理一些：
I)當day(B2)
=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)
=IF(DAY(B2)-DAY(A2)>=0,DAY(B2)-DAY(A2),MAX(B2-DATE(YEAR(B2),MONTH(B2)-1,DAY(A2)),0))
當然，這兩種方法只是本人的建議，僅供參考。
綜合以上算法解釋，這個參數(shù)在不夠減的時候借位是以B2為基準的，這個參數(shù)的算法可以表示為以下的公式：
=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)))
關(guān)于這個參數(shù)算法的討論，還在這個帖子中進行過：http://club.excelhome.net/viewthread.php?tid=357741
5，=Datedif(A2,B2,"YM")
此參數(shù)含義為返回時間段內(nèi)的整月數(shù)，忽略日和年。
這里提到了“忽略”日，但實際與參數(shù)“M”一樣，還是有關(guān)日期的相關(guān)計算。這個參數(shù)的算法實際上與參數(shù)“M”的算法一致，只是忽略其中年份差中所包含的月份數(shù)。
其算法可以表示為以下的公式，其中引用了Datedif函數(shù)的“M”參數(shù)方便公式編寫：
=MOD(DATEDIF(A2,B2,"m"),12)
6，=Datedif(A2,B2,"YD")
此參數(shù)含義為返回時間段內(nèi)的天數(shù)，忽略其中的年。
這個參數(shù)的算法比較復雜，情況比較多，簡單地說包括以下幾個重點：
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”參數(shù)類似的閏年bug，函數(shù)結(jié)果偏大164。這個bug在Excel2003的SP3中不存在，但在Excel 2010中依舊存在，且差值變?yōu)?13。
綜合以上算法解釋，這個參數(shù)的算法可以表示為以下的公式(上面的文字不好理解，如果能看懂下面的公式則比較容易理解上述算法)：
=IF(--(TEXT(B2,"!0!0-m-d"))>=--(TEXT(A2,"!0!0-m-d")),IF((DAY(B2)
其中包含下劃線的部分為閏年bug的模擬，如果希望排除閏年的錯誤，可以將這部分內(nèi)容去除。
【Datedif函數(shù)全面解析和BUG分析】相關(guān)文章：
Javascript函數(shù)的定義和用法分析08-15
全面解析SAT考試01-14
全面解析新加坡留學優(yōu)勢09-15
日本留學條件全面解析06-22
解析托福文章的特點和結(jié)構(gòu)邏輯分析07-22
德國留學就業(yè)前景全面解析12-30
法國留學費用全面解析08-27
2016考研：體育碩士全面解析01-11
美國高考SAT、ACT全面解析12-31