Excel隱藏函數(shù)之DATEDIF

本篇文章給大家?guī)砹岁P(guān)于excel的相關(guān)知識,其中主要介紹了隱藏函數(shù)的相關(guān)問題,主要是datedif函數(shù),下面一起來看一下,希望對大家有幫助。

Excel隱藏函數(shù)之DATEDIF

相關(guān)學(xué)習(xí)推薦:excel

Excel中有一類函數(shù)叫隱藏函數(shù),你在Excel的函數(shù)列表里是找不到它們的身影的,甚至連微軟的幫助文件里也沒有相關(guān)說明,但是它們不但功能強(qiáng)大,而且在工作中應(yīng)用廣泛。

DATEDIF就是一個神奇的隱藏函數(shù),它存在于Excel中,但是在Excel的幫助文件中卻找不到它。

今天我們就來扒一扒這個神奇的隱藏函數(shù)。

1、DATEDIF函數(shù)詳解

DATEDIF的基礎(chǔ)語法為:

DATEDIF(開始日期,結(jié)束日期,間隔類型)

Excel隱藏函數(shù)之DATEDIF

其中,參數(shù)start_date和end_date是兩個日期,并且前者一定不能大于后者。

unit有以下6個參數(shù),分別用來計算不同的差異,如下表所示。

DATEDIF的參數(shù)

在日常拼寫中,有的人會漏寫函數(shù)名稱中間的D,變成DATEIF,這是錯誤的,而且輸寫錯誤時,Excel系統(tǒng)不會提示。

此函數(shù)單詞有一個簡單的記憶方式:DATEDIF縮寫于Date Different,譯為不同的日期。

2、計算兩個日期間的年、月、日間隔

如下圖所示,這是DATEDIF的常規(guī)用法,這6個參數(shù)的實際意義,我們可以結(jié)合圖中的數(shù)據(jù)進(jìn)行講解。

Excel隱藏函數(shù)之DATEDIF

DATEDIF常規(guī)用法

首先,在D16、D24單元格中分別輸入以下公式,向下分別復(fù)制到D21、D29單元格,以計算出不同參數(shù)的差異:

=DATEDIF(E16,F16,C16)

=DATEDIF(E24,F24,C24)

D24單元格,參數(shù)“Y”,單看2017年和2020年,相差年數(shù)應(yīng)為3,但是從2017/7/28到2020/2/8,先過2年到2019/7/28,還沒到要求的2020/2/8,再過1年的話,就到了2020/7/28,會超過結(jié)束日期,所以其結(jié)果返回2,不能返回3。

D25單元格,參數(shù)“M”,2017/7/28過30個月便到了2020/1/28,然后再過1個月就到了2020/2/28,超過了結(jié)束日期2020/2/8,所以結(jié)果只能為30,不能為31。要充分體會“整年數(shù)”“整月數(shù)”中“整”字的意思。

D26單元格,參數(shù)“D”,就相當(dāng)于兩個日期直接相減,計算天數(shù)的差。

D27單元格,參數(shù)“MD”,這個計算忽略月和年,相當(dāng)于把start_date拉近到end_date 前最接近的日期。也就是說,將2017/7/28拉近到2020/2/8之前日期為28的最接近日期,即2020/1/28,然后計算2020/1/28與2020/2/8之間的天數(shù)差,即11天。

D28單元格,參數(shù)“YM”,忽略日和年計算整月數(shù),即相當(dāng)于把2017/7/28拉近到 2020/2/8之前最接近的7月28日,變成2019/7/28,然后計算其與2020/2/8之間的“整”月數(shù)差,即6個月。

D29單元格,參數(shù)“YD”,忽略年計算天數(shù)差,相當(dāng)于把start_date拉近到end_date前最接近的相同月和相同日的日期。也就是說,將2017/7/28拉近到2019/7/28,然后計算2019/7/28與2020/2/8之間的天數(shù)差,即195天。

在使用“MD”“YD”參數(shù)計算天數(shù)差時,由于閏年的存在,有時會與理想值相差一天,這種情況一般不會影響我們的日常使用。

3、整年、月、日區(qū)別

如下圖所示,列出了2017/7/28到2020/7/27與2017/7/28到2020/7/28的對比,雖然end_date只差了1天,但是結(jié)果有比較大的差異。計算原理相同,要體會“整”字的含義。

Excel隱藏函數(shù)之DATEDIF

整年、月、日區(qū)別

這么多參數(shù)需要怎么記憶呢?首先要知道這個函數(shù)的作用,理解每一個參數(shù)的計算原理。如果工作中常常需要計算日期,可以將其打印出來,貼在桌子旁即查即用。

4、案例:工齡計算

假定今天是2019/7/28,每個員工參加工作的日期如下圖中C列所示,那么每個人的工齡是多少呢?工齡可表示成m年n個月的形式。

Excel隱藏函數(shù)之DATEDIF

工齡計算

可以分步進(jìn)行操作。首先計算“整年”數(shù),然后計算“整月” 數(shù)。計算月數(shù)時需要注意,月數(shù)的值最大不會超過11,因為到12個月就是1年了,即要忽略年份的存在來計算月數(shù)。那么使用哪個參數(shù)計算呢?

從上一節(jié)講的DATEDIF的參數(shù)對照表中可以看到,計算整年數(shù)使用參數(shù)“Y”,而忽略年計算整月數(shù)使用“YM”。于是D51單元格的函數(shù)公式可以寫成:

(左右拖動查看完整公式)

=DATEDIF(C51,”2019/7/28″,”Y”)&” 年 “&DATEDIF(C51,”2019/7/28″,”YM”)&” 個月 ”

我們看一下D54:D56單元格區(qū)域,僅相差1天,計算結(jié)果便不同。所以使用DATEDIF時,始終要有一個“整”的概念在腦海中。

另外,DATEDIF中的Y、M、D參數(shù),大小寫均可以。

5、案例:年假天數(shù)計算

《職工帶薪年休假條例》規(guī)定,職工累計工作已滿1年不滿10年的,年休假為5天;已滿10年不滿20年的,年休假為10天;已滿20年的,年休假為15天。

同樣,假設(shè)今天是2019/7/28,那么每名員工的年休假天數(shù)分別為多少天呢?

其實這個題目比上一節(jié)的案例更簡單,只需知道每名員工參加工作的年數(shù)即可。

如下圖所示,在D66單元格中輸入以下公式,計算出每名員工的工作年數(shù):

=DATEDIF(C66,DATE(2019,7,28),”Y”)

Excel隱藏函數(shù)之DATEDIF

年假天數(shù)計算

在這里再次強(qiáng)調(diào),如果在公式中使用快捷輸入的方式表達(dá)日期,必須加雙引號,如上節(jié)中的“DATEDIF(C51,”2019/7/28″,”Y”)”,如果掌握不了雙引號使用,就規(guī)規(guī)矩矩地使用DATE函數(shù),保證不會出錯。

根據(jù)D列的年數(shù),可以計算法定年假的天數(shù),在E66單元格中輸入以下公式:

=LOOKUP(D66,{0,1,10,20},{0,5,10,15})

相關(guān)學(xué)習(xí)推薦:excel

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點贊11 分享