Excel函數學習之DATEDIF()的使用方法

本篇文章帶大家認識DATEDIF函數！ DATEDIF函數不僅可以用來計算年齡、工齡、工齡工資、專案週期，還可以用來做生日倒數提醒，專案竣工日倒數提醒等等。用上它，您再也不會缺席那些重要的日子，不論是親人生日、專案竣工日，還是兒女的畢業典禮日。

DATEDIF函數和我們平常見到的函數有所不同。大家都知道，一般我們只要在EXCEL中輸入函數字母的前幾位，EXCEL就會自動彈出該函數，然而這個函數字母都輸完了，EXCEL仍沒有任何提示。有的小夥伴可能都會懷疑是否有這個函數。其實DATEDIF函數是EXCEL隱藏函數，在幫助和插入公式裡面是沒有的，只能純手動輸入。

非隱藏函數輸入有提示

 
#隱藏函數輸入無提示 

DATEDIF函數不僅可以用來計算年齡、工齡、工齡薪資、專案週期，還可以用來做生日倒數提醒，專案竣工日倒數提醒等等。下面我們就來認識認識它。

一、初識DATEDIF

DATEDIF函數用來計算兩天之差，傳回兩個日期之間的年、月、日間隔數

函數結構：DATEDIF（起始日期,結束日期,傳回型別）

1.參數解釋

1）起始日期與結束日期

起始日期、結束日期作為需要計算差異的兩個日期。

這兩個日期的輸入方法如下：

①可以直接輸入引號的日期，例如"2017/10/16"。注意起始日期不能早於1900年，結束日期大於起始日期。

②也可以直接引用儲存格中的日期

#③也可以利用其他函式得到，例如TODAY () （注意：範例當日是2019年2月15日）

#2）傳回型別

回傳型別用於設定結算結果的類型。返回類型是文本，輸入時須要帶雙引號。

y：返回兩個日期之間相差整年數（不到一年的不計）

m：返回兩個日期之間相差整月數（不足一月的不計）

d：傳回兩個日期之間相差的天數

ym：計算兩個日期之間略去整年差異後的整月數差異。譬如，兩個日期（2017-4-20,2019-2-20）相差1年10月，略去整年差異1年，則ym的結果就是10月。再譬如，兩個日期（2018-4-20,2019-2-20）相差10月，則ym的結果是10月。

yd：計算兩個日期之間略去整年差異後的天數差異。譬如，兩個日期（2017-4-20,2019-2-20）相差1年306天，略去整年差異1年，則ym的結果就是306天。

md：計算兩個日期之間略去整年和整月差異後的天數差異。譬如，兩個日期（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"之間相差的天數，等於770。

DATEDIF("2017/1/6","2019/2/15","ym")，計算兩天之間除開整年外的間隔月數。兩日期之間實際相差25月，包含了2個整年（24月），所以ym類型回傳值為25-24=1。

DATEDIF("2017/1/6","2019/2/15","yd")，計算兩日期之間除開整年外的間隔天數。兩日期之間實際相差770天，包含了2個整年（730天），所以yd類型回傳值為770-730=40。

3.使用要點

#1）雙引號

到這裡，相信小夥伴們對於DATEDIF函數已經有了初步的認識，可以寫幾個公式練練手啦。寫公式中需注意雙引號的使用。

（1）如果第1、2參數是直接輸入日期，則日期必須帶雙引號。

（2）第3參數是文本，一定要記得帶上雙引號。

2）錯誤類型

DATEDIF函數如果發生錯誤，通常會有以下三類：

##開始或結束日期所引用的單元格格式不是日期格式

錯誤代碼	錯誤原因
##NUM!	①函數第三參數回傳型別輸入值有誤  ②第一參數比第二參數大
#VALUE!

##############NAME?####### ######①函數輸入錯誤 ######②文字類型的資料沒帶雙引號###############

二、DATEDIF函數實際應用範例

1.根據出生日期計算年齡

已知下面員工的出生日期，求他們今年的年齡。

不准偷看答案~

#公式：=DATEDIF(D2,TODAY() ,"y")

TODAY()函數取得的是系統目前日期，列舉的實例為2019/2/15日的計算結果，並不一定跟小夥伴們得到的結果相符哦~

怎麼樣？是不是很簡單呢？

2.根據身分證號碼計算年齡

上一例已經有了出生日期，所以直接用DATEDIF函數套用TODAY函數即可計算出年齡。如果只有身分證號碼，要計算年齡，就需要把出生日期從身分證號碼中提取出來後再計算。公式如下：

           ①         ②        ①公式上

都是公式#①使用MID函數提取出身分證號碼中出生日期的8位數字。

②用TEXT函數讓這8位數字以"0-00-00"的格式顯示，得到像日期格式的文本，然後在TEXT函數前加上負負得正的運算，將文字轉換為日期。

③最後將上面得到的日期作為DATEDIF函數的起始日期，將TODAY()作為結束日期，設定傳回類型為「y」,即可計算出兩日期之間相差的整年數——年齡。

3.根據入職日期計算員工工齡（以年月日的形式展現）

用例1計算年齡的方法，如果知道員工入職的時間，即可計算出以整年計的員工工齡。但如果需要計算出詳細的員工工齡，如幾年多少月幾天，該怎麼做呢？答案如下：

###公式雖長，卻特別好理解。首先用三個DATEDIF函數分別計算出兩個日期之間相差幾年幾月幾日，最後再用文字連接符號「&」進行連接，得到結果。 #########4.計算工齡薪資#########根據2019年全國推出的工齡薪資規定，員工連續工作滿一年50元/月；連續工作滿兩年100元/月；連續工作滿三年150元/月；連續工作滿四年180元/月，以此類推，累計十年封頂。 ######小夥伴是不是一頭霧水呢？沒事，我們一步一步來，先計算工齡（以整年計算）。 ######公式：###=DATEDIF(C2,D2,"y")###################接著，來到我們的重頭戲，計算工齡工資。 ###############這裡我們藉助了IF函數和MIN函數。 ######根據2019年國家推出的工齡薪資規定，1-3年工齡薪資每年是以50來遞增的，4-10年的工齡薪資每年是以30來遞增的。我們可以使用IF函數分開判斷。 ######先判斷工齡E2是否小於4，小於4則表示員工工齡薪資是以每年50來遞增，回傳「###」的結果；如果工齡E2不小於4，工齡薪資則是在150的基礎上以每年30來遞增，回傳「###」的結果。 ######因為工齡工資只能累積十年，大於十年的工齡工資與十年的工齡工資一致，所有我們使用MIN函數返回10和E2中的最小值作為工齡。 ###

5.製作員工生日提醒

下面是一張員工的資訊表，我們想做一個生日提醒，提前7天提醒某員工的生日快到了。

提示：和IF函數結合使用，快點動腦筋想一想~

## 

                 ①                ②  #   ③

#這個公式之後》這個公式上是這個公式

這個公式

這個公式

##這個公式

##這個公式這個公式#這個公式

這個公式#這個公式

##這個公式#這個公式

##這個公式真的很問題。

我們日常計算距離生日的天數都是用即將到來的生日日期減去今天的日期。而這個公式與我們的習慣不同，它用今天的日期減去出生日期進行計算，並且還將出生日期減少了7天。

為何能這麼做？

首先我們來看看yd回傳類型下不同的目前日期與出生日期的間隔天數法則。下表以出生日期1999年2月22日為例，展示了昨天、今天、明天、後天等距離出生日期的天數。

N16單元格公式= DATEDIF($J$13,N15,"yd")，$J$13代表出生日期，N15代表不同的當前日期。

很明顯，生日當天間隔為0；小於生日日期的，日期越趨近生日，間隔天數越大越趨近365；大於生日日期的，日期越趨近生日，間隔天數越小越趨近0。

其次，在這種情況下，直接套用IF函數根據間隔天數是否小於等於7來給出生日提醒的公式=IF(DATEDIF($J$13,N15,"yd")快過生日啦","")無法實現提前7天提醒。相反，它只能實現生日當天和生日後7天的提醒，如下：

最後，那怎麼才能提前7天提醒？有兩種方法。第一種，設法讓間隔天數0-7提前7天出現。這時，要么把起始日期減少7天（$J$13-7），要么把結束日期增加7天（N15 7），如下：

起始日期減少7天後的間隔天數

##起始日期減少7天後的生日提醒

第二種，修改判斷條件，把修改為>=358即可。如下：

  修改判斷條件後，生日當天不會提醒。

      Ok，到這裡，相信大家就理解前面的公式了。在此基礎上，我們可以修改公式，讓提醒更人性化：

=IF(DATEDIF(D3-7,TODAY(),"yd")還有"&7-DATEDIF(D3-7,TODAY(),"yd")&"天過生日啦","")

再多說兩句：如果按平常思路用即將到來的生日日期減去當前日期來計算距離生日的天數，生日提醒公式該怎麼寫呢？答案如下：

=IF(DATEDIF(TODAY(),IF(TEXT(D3,"M月DD日")

月DD日"),YEAR(TODAY() 365),YEAR( TODAY()))&"年"&TEXT(D3,"M月DD日"),"yd")快過生日啦","")(today(),"m

# #########這是一個非常長的公式！！！######長就長在即將到來的生日日期提取。######公式中的IF(TEXT(D3 ,"M月DD日")###月DD日"),YEAR(TODAY() 365),YEAR(TODAY()))&"年"&TEXT(D3,"M月DD日")用於獲取即將到來的生日日期。意思是：如果出生日期中的月日數小於今日的月日數，表示今年的生日已經過去了，新的生日日期應該是YEAR(TODAY() 365)&"年"&TEXT(D3,"M月DD日"；反之，說明今年的生日還沒過，生日日期應該是YEAR(TODAY())&"年"&TEXT(D3,"M月DD日"。###(today(),"m###

YEAR(TODAY())提取今年的年份，加上365，則得到明年的年份。

TEXT(D3,"m月dd日")提取出生日期中的月份和號數。

到此，DATEDIF函數就介紹完畢。不論是計算年齡、工齡、工齡工資，還是給生日提醒，都可以用DATEDIF來實現。當然，DATEDIF也完全可以用來計算專案用時、距離完工日天數，做完工倒數提醒。如果你是做人事、做薪資核算、做專案管理的，那麼趕快操練起來吧！

相關學習推薦：excel教學

以上是Excel函數學習之DATEDIF()的使用方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！