DATEDIF用於計算兩個日期的間隔年數或月數,語法為:
DATEDIF(開始日期,結束日期,間隔型別)
其中,參數 開始日期 和 結束日期 是兩個日期,並且前者一定不能大於後者。
間隔型別 有以下6個參數,分別用來計算不同的差異,如下表所示。
DATEDIF的參數
2
計算兩個日期間的年、月、日間隔
如下圖所示,這是DATEDIF的常規用法,這6個參數的實際意義,我們可以結合圖中的數據進行講解。
DATEDIF常規用法
首先,在D16、D24單元格中分別輸入以下公式,向下分別復制到D21、D29單元格,以計算出不同參數的差異:
=DATEDIF(E16,F16,C16)
=DATEDIF(E24,F24,C24)
D24單元格,參數「Y」,單看2017年和2020年,相差年數應為3,但是從2017/7/28到2020/2/8,先過2年到2019/7/28,還沒到要求的2020/2/8,再過1年的話,就到了2020/7/28,會超過結束日期,所以其結果返回2,不能返回3。
D25單元格,參數「M」,2017/7/28過30個月便到了2020/1/28,然後再過1個月就到了2020/2/28,超過了結束日期2020/2/8,所以結果只能為30,不能為31。要充分體會「整年數」「整月數」中「整」字的意思。
D26單元格,參數「D」,就相當於兩個日期直接相減,計算天數的差。
D27單元格,參數「MD」,這個計算忽略月和年,相當於把start_date拉近到end_date 前最接近的日期。也就是說,將2017/7/28拉近到2020/2/8之前日期為28的最接近日期,即2020/1/28,然後計算2020/1/28與2020/2/8之間的天數差,即11天。
D28單元格,參數「YM」,忽略日和年計算整月數,即相當於把2017/7/28拉近到 2020/2/8之前最接近的7月28日,變成2019/7/28,然後計算其與2020/2/8之間的「整」月數差,即6個月。
D29單元格,參數「YD」,忽略年計算天數差,相當於把start_date拉近到end_date前最接近的相同月和相同日的日期。也就是說,將2017/7/28拉近到2019/7/28,然後計算2019/7/28與2020/2/8之間的天數差,即195天。
在使用「MD」「YD」參數計算天數差時,由於閏年的存在,有時會與理想值相差一天,這種情況一般不會影響我們的日常使用。
3
整年、月、日區別
如下圖所示,列出了2017/7/28到2020/7/27與2017/7/28到2020/7/28的對比,雖然結束日期只差了1天,但是結果有比較大的差異。計算原理相同,要體會「整」字的含義。
整年、月、日區別
這麽多參數需要怎麽記憶呢?首先要知道這個函式的作用,理解每一個參數的計算原理。如果工作中常常需要計算日期,可以將其打印出來,貼在桌子旁即查即用。
4
案例:工齡計算
假定今天是2019/7/28,每個員工參加工作的日期如下圖中C列所示,那麽每個人的工齡是多少呢?工齡可表示成m年n個月的形式。
工齡計算
可以分步進行操作。首先計算「整年」數,然後計算「整月」 數。計算月數時需要註意,月數的值最大不會超過11,因為到12個月就是1年了,即要忽略年份的存在來計算月數。那麽使用哪個參數計算呢?
計算整年數使用參數「Y」,而忽略年計算整月數使用「YM」。於是D51單元格的函式公式可以寫成:
(
左右拖動檢視完整公式
)
=DATEDIF(C51,"2019/7/28","Y")&" 年 "&DATEDIF(C51,"2019/7/28","YM")&" 個月 "
我們看一下D54:D56單元格區域,僅相差1天,計算結果便不同。所以使用DATEDIF時,始終要有一個「整」的概念在腦海中。
另外,DATEDIF中的Y、M、D參數,大小寫均可以。
5
案例:年假天數計算
【職工帶薪年休假條例】規定,職工累計工作已滿1年不滿10年的,年休假為5天;已滿10年不滿20年的,年休假為10天;已滿20年的,年休假為15天。
同樣,假設截止日期是2019/7/28,那麽每名員工的年休假天數分別為多少天呢?
其實這個題目比上一節的案例更簡單,只需知道每名員工參加工作的年數即可。
如下圖所示,在D66單元格中輸入以下公式,計算出每名員工的工作年數:
=DATEDIF(C66,DATE(2019,7,28),"Y")
年假天數計算
在這裏再次強調,如果在公式中使用快捷輸入的方式表達日期,必須加雙引號,如上節中的「DATEDIF(C51,"2019/7/28","Y")」。
根據D列的年數,可以計演算法定年假的天數,在E66單元格中輸入以下公式:
=LOOKUP(D66,{0,1,10,20},{0,5,10,15})
好了,今天的內容就是這些,祝大家一天好心情。
作者:翟振福