用上DATEDIF，您永不再缺席那些重要的日子！
?
作者：壹仟伍佰萬來源：部落窩教育發(fā)布時間：2019-05-08 13:46:49點擊：8511
編按：
哈嘍，大家好！今天是部落窩函數(shù)課堂第4課，我們將認識DATEDIF函數(shù)！DATEDIF函數(shù)不僅可以用來計算年齡、工齡、工齡工資、項目周期，還可以用來做生日倒計時提醒，項目竣工日倒計時提醒等等。用上它，您再也不會缺席那些重要的日子，不論是親人生日、項目竣工日，還是兒女的畢業(yè)典禮日。
DATEDIF函數(shù)和我們平時見到的函數(shù)有所不同。大家都知道，一般我們只要在EXCEL中輸入函數(shù)字母的前幾位，EXCEL就會自動彈出該函數(shù)，然而這個函數(shù)字母都輸完了，EXCEL仍沒有任何提示。有的小伙伴可能都會懷疑是否有這個函數(shù)。其實DATEDIF函數(shù)是EXCEL隱藏函數(shù)，在幫助和插入公式里面是沒有的，只能純手工輸入。
非隱藏函數(shù)輸入有提示 隱藏函數(shù)輸入無提示
DATEDIF函數(shù)不僅可以用來計算年齡、工齡、工齡工資、項目周期，還可以用來做生日倒計時提醒，項目竣工日倒計時提醒等等。下面我們就來認識認識它。
一、 初識DATEDIF
DATEDIF函數(shù)用于計算兩日期之差，返回兩個日期之間的年、月、日間隔數(shù)
函數(shù)結構：DATEDIF（起始日期,結束日期,返回類型）
1.參數(shù)解釋
1）起始日期和結束日期
起始日期、結束日期作為需要計算差異的兩個日期。
這兩個日期的輸入方法如下：
①可以直接輸入帶引號的日期，例如"2017/10/16"。注意起始日期不能早于1900年，結束日期要大于起始日期。
②也可以直接引用單元格中的日期
③還可以利用其他函數(shù)得到，例如TODAY() （注意：范例當日是2019年2月15日）
2）返回類型
返回類型用于設置結算結果的類型。返回類型是文本，輸入時須要帶雙引號。
y：返回兩個日期之間相差整年數(shù)（不足一年的不計）
m：返回兩個日期之間相差整月數(shù)（不足一月的不計）
d：返回兩個日期之間相差的天數(shù)
ym：計算兩日期之間略去整年差異后的整月數(shù)差異。譬如，兩個日期（2017-4-20,2019-2-20）相差1年10月，略去整年差異1年，則ym的結果就是10月。再譬如，兩個日期（2018-4-20,2019-2-20）相差10月，則ym的結果是10月。
yd：計算兩日期之間略去整年差異后的天數(shù)差異。譬如，兩個日期（2017-4-20,2019-2-20）相差1年零306天，略去整年差異1年，則ym的結果就是306天。
md：計算兩日期之間略去整年和整月差異后的天數(shù)差異。譬如，兩個日期（2017-4-20,2019-2-25）相差1年10月零5天，略去整年和整月差異1年10月，則md的結果就是5天。
2.小栗子
舉個栗子
DATEDIF("2017/2/15","2019/2/15","y")，計算"2017/2/15"與"2019/2/15"之間相差幾個整年。這里相差兩個完整的年，所以等于2。
DATEDIF("2017/1/6","2019/2/15","d")，計算"2017/1/6"與"2019/2/15"之間相差的天數(shù)，等于770。
DATEDIF("2017/1/6","2019/2/15","ym")，計算兩日期之間除開整年外的間隔月數(shù)。兩日期之間實際相差25月，包含了2個整年（24月），所以ym類型返回值為25-24=1。
DATEDIF("2017/1/6","2019/2/15","yd")，計算兩日期之間除開整年外的間隔天數(shù)。兩日期之間實際相差770天，包含了2個整年（730天），所以yd類型返回值為770-730=40。
3.使用要點
1）雙引號
到這里，相信小伙伴們對于DATEDIF函數(shù)已經有了初步的認識，可以寫幾個公式練練手啦。寫公式中需注意雙引號的使用。
（1）如果第1、2參數(shù)是直接輸入日期，則日期必須帶雙引號。
（2）第3參數(shù)是文本，一定要記得帶上雙引號。
2）錯誤類型
DATEDIF函數(shù)如果發(fā)生錯誤，通常有以下三類：
|
錯誤代碼 |
錯誤原因 |
|
#NUM! |
①函數(shù)第三參數(shù)返回類型輸入值有誤 ②第一參數(shù)比第二參數(shù)大 |
|
#VALUE! |
開始或結束日期所引用的單元格格式不是日期格式 |
|
#NAME? |
①函數(shù)輸入有誤 ②文本類型的數(shù)據(jù)沒帶雙引號 |
二、DATEDIF函數(shù)實際應用例舉
1.根據(jù)出生日期計算年齡
已知下面員工的出生日期，求他們今年的年齡。
不準偷看答案哦~
公式：=DATEDIF(D2,TODAY(),"y")
TODAY()函數(shù)獲取的是系統(tǒng)當前日期，列舉的實例為2019/2/15日的計算結果，并不一定和小伙伴們得到的結果相符哦~
怎么樣？是不是很簡單呢？
2.根據(jù)身份證號碼計算年齡
上一例中已經有了出生日期，所以直接用DATEDIF函數(shù)套用TODAY函數(shù)即可計算出年齡。如果只有身份證號碼，要計算年齡，就需要把出生日期從身份證號碼中提取出來后再計算。公式如下：
① ② ③
公式解析：
①使用MID函數(shù)提取出身份證號碼中出生日期的8位數(shù)字。
②用TEXT函數(shù)讓這8位數(shù)字以"0-00-00"的格式顯示，得到像日期格式的文本，然后在TEXT函數(shù)前加上負負得正的運算，將文本轉換為日期。
③最后將上面得到的日期作為DATEDIF函數(shù)的起始日期，將TODAY()作為結束日期，設置返回類型為“y”,即可計算出兩日期之間相差的整年數(shù)——年齡。
3.根據(jù)入職日期計算員工工齡（以年月日的形式展現(xiàn)）
用例1計算年齡的方法，如果知道員工入職的時間，即可計算出按整年計的員工工齡。但如果需要計算出詳細的員工工齡，如多少年多少月多少天，該怎么做呢？答案如下：
公式雖長，卻特別好理解。首先用三個DATEDIF函數(shù)分別計算出兩日期之間相差幾年幾月幾日，最后再用文本連接符“&”進行連接，得到結果。
4.計算工齡工資
根據(jù)2019年國家出臺的工齡工資規(guī)定，員工連續(xù)工作滿一年 50元/月；連續(xù)工作滿兩年 100元/月；連續(xù)工作滿三年 150元/月；連續(xù)工作滿四年180元/月，以此類推，累計十年封頂。
小伙伴是不是一頭霧水呢？沒事，我們一步一步來，首先計算工齡（按整年計算）。
公式：=DATEDIF(C2,D2,"y")
接著，來到我們的重頭戲，計算工齡工資。
這里我們借助了IF函數(shù)和MIN函數(shù)。
根據(jù)2019年國家出臺的工齡工資規(guī)定，1-3年工齡工資每年是以50來遞增的，4-10年的工齡工資每年是以30來遞增的。我們可以使用IF函數(shù)分開判斷。
首先判斷工齡E2是否小于4，小于4則表示員工工齡工資是以每年50來遞增，返回“
”的結果；如果工齡E2不小于4，工齡工資則是在150的基礎上以每年30來遞增，返回“
”的結果。
因為工齡工資只能累計十年，大于十年的工齡工資與十年的工齡工資一致，所有我們使用MIN函數(shù)返回10和E2中的最小值作為工齡。
5.制作員工生日提醒
下面是一張員工的信息表，我們想做一個生日提醒，提前7天提醒某員工的生日快到了。
提示：和IF函數(shù)結合使用，快開動腦筋想一想吧~
① ② ③
是不是感覺這個公式很燒腦？
我們日常計算距離生日的天數(shù)都是用即將到來的生日日期減去今天的日期。而這個公式與我們的習慣不同，它用今天的日期減去出生日期進行計算，并且還將出生日期減少了7天。
為何能這樣做？
首先我們來看看yd返回類型下不同的當前日期與出生日期的間隔天數(shù)規(guī)律。下表以出生日期1999年2月22日為例，展示了昨天、今天、明天、后天等距離出生日期的天數(shù)。
N16單元格公式= DATEDIF($J$13,N15,"yd")，$J$13代表出生日期，N15代表不同的當前日期。
很明顯，生日當天間隔為0；小于生日日期的，日期越趨近生日，間隔天數(shù)越大越趨近365；大于生日日期的，日期越趨近生日，間隔天數(shù)越小越趨近0。
其次，在這種情況下，直接套用IF函數(shù)根據(jù)間隔天數(shù)是否小于等于7來給出生日提醒的公式=IF(DATEDIF($J$13,N15,"yd")<=7,"快過生日啦","")無法實現(xiàn)提前7天提醒。相反，它只能實現(xiàn)生日當天和生日后7天的提醒，如下：
最后，那怎么才能提前7天提醒？有兩種方法。第一種，設法讓間隔天數(shù)0-7提前7天出現(xiàn)。這時，要么把起始日期減少7天（$J$13-7），要么把結束日期增加7天（N15+7），如下：
起始日期減少7天后的間隔天數(shù)
起始日期減少7天后的生日提醒
第二種，修改判斷條件，把<=7修改為>=358即可。如下：
修改判斷條件后，生日當天不會提醒。
Ok，到這里，相信大家就理解前面的公式了。在此基礎上，我們可以修改公式，讓提醒更人性化：=IF(DATEDIF(D3-7,TODAY(),"yd")<=7,"還有"&7-DATEDIF(D3-7,TODAY(),"yd")&"天過生日啦","")
再多說兩句：如果按平常思路用即將到來的生日日期減去當前日期來計算距離生日的天數(shù)，生日提醒公式該怎么寫呢？答案如下：
=IF(DATEDIF(TODAY(),IF(TEXT(D3,"M月DD日")
這是一個非常長的公式?。。?/span>
長就長在即將到來的生日日期提取。
公式中的IF(TEXT(D3,"M月DD日")
YEAR(TODAY())提取今年的年份，加上365，則得到明年的年份。
TEXT(D3,"m月dd日")提取出生日期中的月份和號數(shù)。
到此，DATEDIF函數(shù)就介紹完畢。不論是計算年齡、工齡、工齡工資，還是給出生日提醒，都可以用DATEDIF實現(xiàn)。當然，DATEDIF也完全可以用來計算項目用時、距離完工日天數(shù)，做完工倒計時提醒。如果你是做人事、做工資核算、做項目管理的，那么趕緊操練起來吧！
本文配套的練習課件請加入QQ群：264539405下載。
做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！
掃下方二維碼關注公眾號，可隨時隨地學習Excel：
相關推薦：