【Excel技巧】- 辦公常用的十大函數@（二）日期時間類函數

01-30

前面一篇圖文教程我給大家講解了一下文本類函數，各位小夥伴應該可以熟練使用了吧。今天介紹的日期時間類函數也比較多，但相比文本類函數要更加容易。

日期時間類函數常用於計算兩個日期間之間的天數、月數等，計算員工的年齡、本月有哪些員工生日等情況。

下圖是我匯總的日期時間類函數，可以放大查看哦。

日期時間類函數不像文本類函數那麼複雜，也沒有什麼查找和替換，除了一個函數DATEDIF較複雜之外，其他的都非常容易理解。

一 DATE函數

用途：返回代表特定日期的序列號。

語法：DATE(year，month，day)

參數：year為一到四位，根據使用的日期系統解釋該參數。默認情況下，Excel for Windows使用1900日期系統，而Excel for Macintosh使用1904日期系統。Month代表每年中月份的數字。如果所輸入的月份大於12，將從指定年份的一月份執行加法運算。Day代表在該月份中第幾天的數字。如果 day 大於該月份的最大天數時，將從指定月份的第一天開始往上累加。

注意：Excel按順序的序列號保存日期，這樣就可以對其進行計算。如果工作簿使用的是1900日期系統，則Excel會將1900年1月1日保存為序列號1。同理，會將1998年1月1日保存為序列號35796，因為該日期距離1900年1月1日為35795天。

實例：如果採用1900日期系統(Excel默認)，則公式「=DATE(2016，12，5)」，返回42709。

二 DATEVALUE函數

用途：返回date_text所表示的日期的序列號。該函數的主要用途是將文字表示的日期轉換成一個序列號。

語法：DATEVALUE(date_text)

參數：Date_text是用Excel日期格式表示日期的文本。在使用1900日期系統中，date_text必須是1900年1月1日到9999年12月31日之間的一個日期；而在1904日期系統中，date_text必須是1904年1月1日到9999年12月31日之間的一個日期。如果date_text超出上述範圍，則函數DATEVaLUE返回錯誤值#value!。

注意：如果省略參數date_text中的年份，則函數DATEVALUE使用電腦系統內部時鐘的當前年份，且date_text中的時間信息將被忽略。

實例：公式「=DATEVALUE("2016-12-05")」返回42709，DATEVALUE("12-05")返回42709。

三 EDATE函數

用途：返回指定日期(start_date)之前或之後指定月份的日期序列號。

語法：EDATE(start_date，months)

參數：Start_date參數代表開始日期，它有多種輸入方式：帶引號的文本串(例如："2016/12/05")、序列號(如42709表示2016年12月05日)或其他公式或函數的結果(例如：DATEVALUE("2016/12/5"))。Months 為在start_date之前或之後的月份數，未來日期用正數表示，過去日期用負數表示。

實例：公式「=EDATE("2016/12/5"，2)」返回42771即2017年2月5日，公式"=EDATE("2016/12/5"，-6)"返回42526即2016年6月5日。

四 TIME函數

用途：返回某一特定時間的小數值，它返回的小數值從0到 0.99999999之間，代表0:00:00(12:00:00 A.M)到23:59:59(11:59:59 P.M) 之間的時間。

語法：TIME(hour，minute，second)

參數：Hour是0到23之間的數，代表小時；Minute是0到59之間的數，代表分；Second是0到59之間的數，代表秒。

實例：公式「=TIME(12，10，30)」返回序列號0.51，等價於12:10:30 PM。=TIME(9，30，10)返回序列號0.40，等價於9:30:10 AM。公式"=TEXT(TIME(23，18，14)，"h:mm:ss AM/PM")"返回「11:18:14 PM」。

五 TIMEVALUE函數

用途：返回用文本串表示的時間小數值。該小數值為從 0 到 0.999999999 的數值，代表從 0:00:00 (12:00:00 AM) 到 23:59:59 (11:59:59 PM) 之間的時間。

語法：TIMEVALUE(time_text)

參數：Time_text是一個用Excel 時間格式表示時間的文本串(如"6:45 PM"和"18:45"等)。

實例：公式「=TIMEVALUE("3:30 AM")」返回0.145833333，公式"=TIMEVALUE("2001/1/26 6:35 AM")"返回0.274305556。

六 YEARFRAC函數

用途：返回start_date和end_date之間的天數佔全年天數的百分比。

語法：YEARFRAC(start_date，end_date，basis)

參數：Start_date表示開始日期，End_date代表結束日期。函數中的日期有多種輸入方式：帶引號的文本串(如"1998/01/30")、序列號(如35829表示1900 日期系統中的1998年1月30日)，或其他公式或函數的結果(例如DATEVaLUE("1998/1/30"))。Basis表示日計數基準類型，其中0或省略為US(NASD)30/360，1實際天數/實際天數，2實際天數/360，3實際天數/365，4歐洲30/360。

實例：公式「=YEARFRAC("2016/1/1"，"2016/12/5"，0)」返回0.9277778，公式"=YEARFRAC("2016/01/01"，"2016/06/01")"返回0.4166667。

七 YEAR函數

用途：返回某日期的年份。其結果為1900到9999之間的一個整數。

語法：YEAR(serial_number)

參數：Serial_number是一個日期值，其中包含要查找的年份。日期有多種輸入方式：帶引號的文本串(例如 "1998/01/30")、序列號(例如，如果使用 1900 日期系統則 35825 表示 1998 年 1 月 30 日)或其他公式或函數的結果(例如 DATEVALUE("1998/1/30"))。

實例：公式「=YEAR("2016/8/6")返回2016」，=YEAR("2013/05/01")返回2013，=YEAR(42207)返回2015。

八 MONTH函數

用途：返回以序列號表示的日期中的月份，它是介於 1(一月)和12(十二月)之間的整數。

語法：MONTH(serial_number)

參數：Serial_number表示一個日期值，其中包含著要查找的月份。日期有多種輸入方式:帶引號的文本串(如"1998/01/30")、序列號(如表示1998年1月30日的35825)或其他公式或函數的結果(如DATEVaLUE("1998/1/30"))等。

實例：公式「=MONTH("2016/02/24")」返回2，公式"=MONTH(42207)"返回7，公式"=MONTH(DATEVaLUE("2016/6/30"))"返回6。

九 EOMONTH函數

用途：返回start-date之前或之後指定月份中最後一天的序列號。

語法：EOMONTH(start_date，months)

參數：Start_date參數代表開始日期，它有多種輸入方式:帶引號的文本串(如"1998/01/30")、序列號(如1900日期系統中的35825)或其他公式或函數的結果(如DATEVaLUE("1998/1/30"))。Month為start_date之前或之後的月份數，正數表示未來日期，負數表示過去日期。

實例：公式「=EOMONTH("2016/01/01"，2)」返回36981即2016年3月31日，公式"=EOMONTH("2016/01/01"，-6)"返回36738即2015年7月31日。獲取當月最後一天日期公式"=EOMONTH(TODAY(),0)"。

十 WEEKDAY函數

用途：返回某日期的星期數。在默認情況下，它的值為1(星期天)到7(星期六)之間的一個整數。

語法：WEEKDAY(serial_number，return_type)

參數：Serial_number是要返回日期數的日期，它有多種輸入方式:帶引號的文本串(如"2001/02/26")、序列號(如35825表示1998年1月30日)或其他公式或函數的結果(如DATEVaLUE("2000/1/30"))。Return_type為確定返回值類型的數字，數字1或省略則1至7代表星期天到數星期六，數字2則1至7代表星期一到星期天，數字3則0至6代表星期一到星期天。

實例：公式「=WEEKDAY("2016/8/28"，2)」返回7(星期六)，公式"=WEEKDAY("2016/02/23"，3)"返回1(星期日)。

十一 WEEKNUM函數

用途：返回一個數字，該數字代表一年中的第幾周。

語法：WEEKNUM(serial_num，return_type)

參數：Serial_num代表一周中的日期。應使用DATE函數輸入日期，或者將日期作為其他公式或函數的結果輸入。Return_type為一數字，確定星期計算從哪一天開始。默認值為 1。

實例：公式"=WEEKNUM(DATEVALUE("2016-12-05"),1)"返回50，也就是日期2016-12-05是本年度第50個周。

十二 WORKDAY函數

用途：返回某日期(起始日期)之前或之後相隔指定工作日(不包括周末和專門指定的假日)的某一日期的值，並扣除周末或假日。

語法：WORKDAY(start_date，days，holidays)

參數：Start_date為開始日期；Days為Start_date之前或之後不含周末及節假日的天數；Days是正值將產生未來日期、負值產生過去日期；Holidays為可選的數據清單，表示需要從工作日曆中排除的日期值(如法定假日或非法定假日)。此清單可以是包含日期的單元格區域，也可以是由代表日期的序列號所構成的數組常量。日期有多種輸入方式：帶引號的文本串(如"1998/01/30")、序列號(如1900 日期系統時的35825表示1998年1月30日)或其他公式或函數的結果(例如 DATEVALUE("1998/1/30"))。

實例：請見如下動態圖。

十三 DAY函數

用途：返回用序列號(整數1到31)表示的某日期的天數，用整數 1 到 31 表示。

語法：DAY(serial_number)

參數：Serial_number是要查找的天數日期，它有多種輸入方式：帶引號的文本串(如"1998/01/30")、序列號(如1900日期系統的35825表示的1998年1月30日)，以及其他公式或函數的結果(如DATEVaLUE("1998/1/30"))。

實例：公式「=DAY("2016/1/27")」返回27，公式"=DAY(35825)"返回30，公式"=DAY(DATEVALUE("2016/1/25"))"返回25。

十四 DAYS360函數

用途：按照一年360天的演算法(每個月30天，一年共計12 個月)，返回兩日期間相差的天數。

語法：DAYS360(start_date，end_date，method)

參數：Start_date和end_date是用於計算期間天數的起止日期。如果start_date在end_date之後，則DAYS360將返回一個負數。日期可以有多種輸入方式：帶引號的文本串(例如："1998/01/30")、序列號(例如：如果使用1900日期系統，則35825表示1998年1月30日)或其他公式或函數的結果(例如，DATEVaLUE("1998/1/30"))。

　　Method是一個邏輯值，它指定了在計算中是採用歐洲方法還是美國方法。若為FALSE或忽略，則採用美國方法(如果起始日期是一個月的31日，則等於同月的30日。如果終止日期是一個月的31日，並且起始日期早於30日，則終止日期等於下一個月的1日，否則，終止日期等於本月的30日)。若為TRUE則採用歐洲方法(無論是起始日期還是終止日期為一個月的 31 號，都將等於本月的 30 號)。

實例：公式「=DAYS360("2016/1/1"，"2016/12/31")」返回360。

十五 HOUR、MINUTE和SECOND函數

這三個函數沒什麼特別的，詳情請見如下動態圖。

十六 NETWORKDAYS函數

用途：返回參數start-data和end-data之間完整的工作日(不包括周末和專門指定的假期)數值。

語法：NETWORKDAYS(start_date，end_date，holidays)

參數：Start_date代表開始日期，End_date代表終止日；Holidays是表示不在工作日曆中的一個或多個日期所構成的可選區域，法定假日以及其他非法定假日。此數據清單可以是包含日期的單元格區域，也可以是由代表日期的序列號所構成的數組常量。

　　函數中的日期有多種輸入方式：帶引號的文本串(如"1998/01/30")、序列號(如使用1900日期系統的35825)或其他公式或函數的結果(如 DATEVALUE("1998/1/30"))。

注意：該函數只有載入「分析工具庫」以後方能使用。

實例：計算2016年一共有多少個工作日，公式"=NETWORKDAYS("2016-01-01", "2016-12-31")"返回261，也就是說一年只有261個工作日。

十七 DATEDIF函數

用途：計算兩個日期之間的天數、月數或年數。提供此函數是為了與 Lotus 1-2-3 兼容。

語法：DATEDIF(start_date,end_date,unit)。

參數：

start_date 為一個日期，它代表時間段內的第一個日期或起始日期。日期有多種輸入方法：帶引號的文本串（例如 "2001/1/30"）、系列數（例如，如果使用 1900 日期系統則 36921 代表 2001 年 1 月 30 日）或其他公式或函數的結果（例如，DATEVALUE("2001/1/30")）。有關日期系列數的詳細信息，請參閱 NOW。

end_date 為一個日期，它代表時間段內的最後一個日期或結束日期。

unit 為所需信息的返回類型，其中：

　　"Y"：時間段中的整年數。

　　"M"：時間段中的整月數。

　　"D"：時間段中的天數。

　　"MD"：start_date 與 end_date 日期中天數的差。忽略日期中的月和年。

　　"YM"：start_date 與 end_date 日期中月數的差。忽略日期中的日和年。

　　"YD"：start_date 與 end_date 日期中天數的差。忽略日期中的年。

實例：從身份證信息中計算出年齡。公式：=DATEDIF(DATE(MID(B33,7,4), MID(B33,11,2), MID(B33,13,2)), TODAY(), "Y")。請見如下動態圖。

十八 NOW和TODAY函數

通過上面的講解，細心的小夥伴會看到我已經用到了這兩個函數。上面計算員工年齡的公式中就用到了TODAY函數。

注意：NOW和TODAY函數會根據日期和時間進行更改的，如果需要給文檔加上日期，可以通過快捷鍵Ctrl+分號插入日期，Ctrl+Shift+分號插入時間。

文章篇幅較長，其實內容很容易理解，大家只需要了解Excel提供了該函數，具體用法可以等到要使用的時候再去幫助文檔進行查看。能夠看到這裡的小夥伴，我相信大家已經有了成功的先決條件，只要有耐心就能成功。相信大家不用多久，就會升職加薪，當上總經理，出任CEO，迎娶白富美，走上人生巔峰。是不是想想還有點小激動呢，嘿嘿^_^