Datedif函數全面解析和BUG分析

時間：2024-10-30 15:50:40 office辦公我要投稿

相關推薦

Datedif函數全面解析和BUG分析

　　DATEDIF函數是一個隱藏的日期函數，一般來說，用這個函數會比直接使用日期運算來的簡單，但是這個函數并不是那么可靠，偶爾會犯點小毛病。所以就小編來說，一般情況下都會用其他方式來替代實現它的功能。

　　從不同的角度來看，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<(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 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)

　　=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))

　　當然，這兩種方法只是本人的建議，僅供參考。

　　綜合以上算法解釋，這個參數在不夠減的時候借位是以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中不存在，但在Excel 2010中依舊存在，且差值變為113。

　　綜合以上算法解釋，這個參數的算法可以表示為以下的公式(上面的文字不好理解，如果能看懂下面的公式則比較容易理解上述算法)：

　　=IF(--(TEXT(B2,"!0!0-m-d"))>=--(TEXT(A2,"!0!0-m-d")),IF((DAY(B2)

　　其中包含下劃線的部分為閏年bug的模擬，如果希望排除閏年的錯誤，可以將這部分內容去除。

【Datedif函數全面解析和BUG分析】相關文章：

Javascript函數的定義和用法分析08-15

jQuery 源碼分析和Ready函數06-28

全面解析SAT考試01-14

全面解析新加坡留學優勢09-15

日本留學條件全面解析06-22

解析托福文章的特點和結構邏輯分析07-22