你的Excel有DATEDIF功能嗎？

  • A+
所屬分類：百科知識

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

一個(gè)DATEDIF是一個(gè)神奇的隱藏函數(shù)，它存在于Excel中，但是沒有在Excel的幫助文件中找到。

現(xiàn)在我們要對這個(gè)神奇的隱藏函數(shù)進(jìn)行一次深入的研究。

1
DATEDIF函數(shù)詳解
DATEDIF的基本語法是：
DATEDIF(開始日期、結(jié)束日期、間隔類型)
其中，參數(shù)start_date和end_date是兩個(gè)日期，前者不得超過后者。
unit有以下6個(gè)參數(shù)，用于計(jì)算不同的差異，如下表所示。

你的Excel有DATEDIF功能嗎？-1

在日常拼寫中，有些人會錯(cuò)過函數(shù)名稱中間的D，變成DATEIF，這是錯(cuò)誤的，當(dāng)輸入錯(cuò)誤時(shí)，Excel系統(tǒng)不會提示。

這個(gè)函數(shù)詞有一個(gè)簡單的記憶方法:DATEDIF在DateDifferent中縮寫，翻譯成不同的日期。
2
計(jì)算兩個(gè)日期間的年、月、日間隔
如下圖所示，這是DATEDIF的常用方法，這6個(gè)參數(shù)的實(shí)際意義可以結(jié)合圖中的數(shù)據(jù)進(jìn)行說明。

你的Excel有DATEDIF功能嗎？-2

DATEDIF常規(guī)用法

首先，在D16、D24單元格中分別輸入以下公式，向下復(fù)制到D21、D29單元格，計(jì)算不同參數(shù)的不同
=DATEDIF(E16、F16、C16)
=DATEDIF(E24、F24、C24)
D24單元格，參數(shù)Y，單看2017年和2020年，相差年限應(yīng)該是3，但是從2017/7/28到2020/2/8，先是2年到2019/7/28，還沒有達(dá)到要求的2020/2/8，再過一年，就到了2020/7/28，超過了結(jié)束日期，所以結(jié)果回到2，不能回到3。
D25單元格，參數(shù)m，2017/7/28超過30個(gè)月達(dá)到2020/1/28，再過1個(gè)月達(dá)到2020/2/28，超過結(jié)束日期2020/2/8，結(jié)果只有30，不能達(dá)到31。要充分體會整年數(shù)整月數(shù)中整字的含義。
D26單元格、參數(shù)d相當(dāng)于兩個(gè)日期直接減少，計(jì)算天數(shù)的差異。
D27單元格，參數(shù)MD，這個(gè)計(jì)算無視月和年，相當(dāng)于將start_date拉近end_date前最接近的日期。也就是說，將2017/7/28拉近到2020/2/8的最接近日，即2020/1/28，計(jì)算2020/1/28和2020/2/8之間的天數(shù)差，即11天。
D28單元格，參數(shù)YM，無視日期和年度計(jì)算月數(shù)，即將2017/7/28接近2020/2/8的最接近7月28日，成為2019/7/28，計(jì)算與2020/2/8之間的整月數(shù)之差，即6個(gè)月。
D29單元格，參數(shù)YD，無視年計(jì)算天數(shù)差，相當(dāng)于將start_date拉近end_date前最接近的同一個(gè)月和同一天的日期。也就是說，將2017/7/28拉近2019/7/28，計(jì)算2019/7/28和2020/2/8之間的天數(shù)差，即195天。
在使用MDYD參數(shù)計(jì)算天數(shù)較差時(shí)，由于閏年的存在，有時(shí)與理想值相差一天，這種情況一般不會影響我們的日常使用。
3
整年、月、日區(qū)別
如下圖所示，列出了2017/7/28到2020/7/27與2017/7/28到2020/7/28的對比，雖然end_date只差了1天，但是結(jié)果有比較大的差異。計(jì)算原理相同，要體會“整”字的含義。

你的Excel有DATEDIF功能嗎？-3

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

這么多參數(shù)需要怎么記憶？首先要了解這個(gè)函數(shù)的作用，了解每個(gè)參數(shù)的計(jì)算原理。工作中經(jīng)常需要計(jì)算日期的話，可以印刷，貼在桌子旁邊查一下。
4
案例：工齡計(jì)算
假設(shè)今天是2019/7/28，每個(gè)員工參加工作的日期如下圖c列所示，每個(gè)人的工作年數(shù)是多少？工作年數(shù)可以表示為m年n個(gè)月的形式。

你的Excel有DATEDIF功能嗎？-4

可以分階段?操作。先計(jì)算整年數(shù)，再計(jì)算整月數(shù)。計(jì)算月數(shù)時(shí)，請注意月數(shù)值最大不超過11。因?yàn)榈搅?2個(gè)月就是1年了，所以無視年份的存在計(jì)算月數(shù)。那么用哪些參數(shù)來計(jì)算呢？
從上一節(jié)所說的DATEDIF參數(shù)對照表可以看出，計(jì)算全年使用參數(shù)Y，而忽略了年計(jì)算全月使用YM。D51單元格的函數(shù)公式可以寫成:
(左右拖動看完整的公式)

=DATEDIF(C51、“2019/7/28”、“Y””&“年”&DATEDIF(C51、“2019/7/28”、“YM”&“月”
讓我們看看D54:D56單元格區(qū)域。只差一天，計(jì)算結(jié)果就不同了。因此，使用DATEDIF時(shí)，必須有整的概念。
另外，DATEDIF中的Y、M、D參數(shù)、大小寫都可以。
5
案例：年假天數(shù)計(jì)算
根據(jù)《員工帶薪年假條例》，員工累計(jì)工作已滿1年未滿10年的，年假為5天；已滿10年未滿20年的，年假為10天；已滿20年的，年假為15天。
再重申一遍，假設(shè)今天是2019/7/28，每個(gè)雇員的年休假分別是多少天？
實(shí)際上，這一主題比上節(jié)中的案例要簡單得多，只要知道每個(gè)員工參與工作的年數(shù)就可以了。
如下圖所示，在D66單元格中輸入下面的公式來計(jì)算每個(gè)雇員的工作年數(shù)：
*DATEDIF(C66,DATE(2019,7,28)"Y")

你的Excel有DATEDIF功能嗎？

此處再次強(qiáng)調(diào)，如果在公式中使用快速輸入的方式來表達(dá)日期，則必須添加雙引號，例如上節(jié)中的“DATEDIF(C51,2019/7/28,Y)”，如果不能掌握雙引號的使用，則必須定期使用DATE函數(shù)，以確保不會出錯(cuò)。
按D列年數(shù)計(jì)算法定年假天數(shù)，在E66單元格中輸入下列公式：
=LOOKUP(D66,{0,1,10,20},{0,5,10,15})

歷史上的今天:

推薦應(yīng)用

發(fā)表評論

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: