Zi 字媒體

上次給大家分享了《2017年最全的excel函數大全（5）——邏輯函數》，這次分享給大家日期和時間函數（上）。DATE 函數返回特定日期的序列號描述DATE 函數返回表示特定日期的連續序列號。用法DATE(year,month,day)DATE 函數用法具有下列參數：ü Year：必需。year 參數的值可以包含一到四位數字。Excel 將根據計算機正在使用的日期系統來解釋 year 參數。默認情況下，Microsoft Excel for Windows 使用的是 1900 日期系統，這表示第一個日期為 1900 年 1 月 1 日。提示: 為避免出現意外結果，請對 year 參數使用四位數字。例如，「07」可能意味著「1907」或「2007」。因此，使用四位數的年份可避免混淆。· 如果 year 介於 0（零）到 1899 之間（包含這兩個值），則 Excel 會將該值與 1900 相加來計算年份。例如，DATE(108,1,2) 返回 2008 年 1 月 2 日 (1900+108)。· 如果 year 介於 1900 到 9999 之間（包含這兩個值），則 Excel 將使用該數值作為年份。例如，DATE(2008,1,2) 將返回 2008 年 1 月 2 日。· 如果 year 小於 0 或大於等於 10000，則 Excel 返回 錯誤值 #NUM!。ü 月：必需。 一個正整數或負整數，表示一年中從 1 月至 12 月（一月到十二月）的各個月。· 如果 month 大於 12，則 month 會從指定年份的第一個月開始加上該月份數。例如，DATE(2008,14,2) 返回表示 2009 年 2 月 2 日的序列數。· 如果 month 小於 1，則 month 會從指定年份的第一個月開始減去該月份數，然後再加上 1 個月。例如，DATE(2008,-3,2) 返回表示 2007 年 9 月 2 日的序列號。ü 日：必需。 一個正整數或負整數，表示一月中從 1 日到 31 日的各天。· 如果 day 大於指定月中的天數，則 day 會從該月的第一天開始加上該天數。例如，DATE(2008,1,35) 返回表示 2008 年 2 月 4 日的序列數。· 如果 day 小於 1，則 day 從指定月份的第一天開始減去該天數，然後再加上 1 天。例如，DATE(2008,1,-15) 返回表示 2007 年 12 月 16 日的序列號。注意: Excel 可將日期存儲為連續序列號，以便能在計算中使用它們。1900 年 1 月 1 日的序列號為 1，2008 年 1 月 1 日的序列號為 39448，這是因為它與 1900 年 1 月 1 日之間相差 39,447 天。需要更改數字格式（設置單元格格式）以顯示正確的日期。案例案例 1例如：=DATE(C2,A2,B2) 將單元格 C2 中的年、單元格 A2 中的月以及單元格 B2 中的日合併在一起，並將它們放入一個單元格內作為日期。以下案例顯示了單元格 D2 中的最終結果。案例 2根據其他日期計算某個日期可以使用 DATE 函數創建基於其他單元格中日期的一個日期。例如，可以使用 YEAR、MONTH 和 DAY 函數來創建基於另一個單元格的周年紀念日期。假設，某個員工第一天上班的日期為 2016 年 10 月 1 日，則可以使用 DATE 函數創建他上班 5 周年的紀念日期：1. DATE 函數會創建一個日期。2. =DATE(YEAR(C2)+5,MONTH(C2),DAY(C2))3. YEAR 函數會查找單元格 C2 並從中提取「2012」。4. 「+5」表示加上 5 年，並在單元格 D2 中創建「2017」作為周年紀念日的年。5. MONTH 函數從單元格 C2 中提取「3」。這將在單元格 D2 中創建「3」作為月。6. DAY 函數從單元格 C2 中提取「14」。這將在單元格 D2 中創建「14」作為天。案例 3 將文本字元串和數字轉換為日期有時Excel的日期是無法識別的。這可能是因為數字與典型的日期不相似，也可能因為數據被設置成了文本格式。如果是這種情況，則可以使用 DATE 函數將信息轉換成日期。例如，在下圖中，單元格 C2 包含採用以下格式的日期：YYYYMMDD。它也被設置成了文本格式。若要將其轉換成日期，則可以將 DATE 函數與 LEFT、MID 和 RIGHT 函數配合使用。1. DATE 函數會創建一個日期。2. =DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))3. LEFT 會在單元格 C2 中查找並從左起提取前 4 個字元。這將在單元格 D2 中創建「2014」作為轉換後日期的年。4. MID 函數將在單元格 C2 中查找。它將從第 5 個字元開始，然後向右提取 2 個字元。這將在單元格 D2 中創建「03」作為轉換後日期的月。因為 D2 的格式設置為 Date，因此「0」不包括在最終結果中。5. RIGHT 函數會在單元格 C2 中查找，然後從最右側開始向左提取前 2 個字元。這將在 D2 中創建「14」作為日期的日。案例 4 按一定的天數加減日期若要按一定的天數加減日期，只需向值或包含日期的單元格引用加上或減去天數即可。在以下案例中，單元格 A5 包含我們想加上和減去 7 天（C5 中的值）的日期。DATEDIF 函數計算兩個日期之間的天數、月數或年數。描述計算兩個日期之間相隔的天數、月數或年數。警告：Excel 提供了 DATEDIF 函數，以便支持來自 Lotus 1-2-3 的舊版工作簿。在某些應用場景下，DATEDIF 函數計算結果可能並不正確。有關詳細信息，請參閱本文中的「已知問題」部分。用法DATEDIF(start_date,end_date,unit)ü Start_date：用於表示時間段的第一個（即起始）日期的日期。 日期值有多種輸入方式：帶引號的文本字元串（例如 2001/1/30）、序列號（例如 36921，在商用 1900 日期系統時表示 2001 年 1 月 30 日）或其他公式或函數的結果（例如 DATEVALUE(2001/1/30)）。ü End_date：用於表示時間段的最後一個（即結束）日期的日期。ü Unit：要返回的信息類型：其他l 日期存儲為可用於計算的序列號。默認情況下，1899 年 12 月 31 日的序列號是 1，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。l DATEDIF 函數在用於計算年齡的公式中很有用。案例已知問題「MD」參數可能導致出現負數、零或不準確的結果。若要計算上一完整月份后餘下的天數，可使用如下方法：此公式從單元格 E17 中的原始結束日期 (5/6/2016) 減去當月第一天 (5/1/2016)。其原理如下：首先，DATE 函數會創建日期 5/1/2016。DATE 函數使用單元格 E17 中的年份和單元格 E17 中的月份創建日期。1 表示該月的第一天。DATE 函數的結果是 5/1/2016。然後，從單元格 E17 中的原始結束日期（即 5/6/2016）減去該日期。5/6/2016 減 5/1/2016 得 5 天。DATEVALUE 函數將文本格式的日期轉換為序列號描述DATEVALUE 函數將存儲為文本的日期轉換為 Excel 識別為日期的序列號。 例如，公式=DATEVALUE(1/1/2008) 返回 39448，即日期 2008-1-1 的序列號。 即使如此，請注意，計算機的系統日期設置可能會導致 DATEVALUE 函數的結果會與此案例不同。如果工作表包含採用文本格式的日期並且要對這些日期進行篩選、排序、設置日期格式或執行日期計算，則 DATEVALUE 函數將十分有用。用法DATEVALUE(date_text)DATEVALUE 函數用法具有下列參數：ü Date_text 必需。代表採用 Excel 日期格式的日期的文本，或是對包含這種文本的單元格的引用。例如，用於表示日期的引號內的文本字元串 2008-1-30 或 30-Jan-2008。· 使用 Microsoft Excel for Windows 中的默認日期系統時，參數 date_text 必須代表 1900 年 1 月 1 日和 9999 年 12 月 31 日之間的某個日期。 如果參數 date_text的值在此範圍之外， DATEVALUE函數將返回錯誤值 「#VALUE!。· 如果省略參數 date_text 中的年份部分，則 DATEVALUE 函數會使用計算機內置時鐘的當前年份。 參數 date_text 中的時間信息將被忽略。其他l Excel 可將日期存儲為序列號，以便可以在計算中使用它們。 默認情況下，1900 年 1 月 1 日的序列號為 1，2008 年 1 月 1 日的序列號為 39,448，這是因為它距 1900 年 1 月 1 日有 39,447 天。l 大部分函數都會自動將日期值轉換為序列數。案例DAY 函數將序列號轉換為月份日期描述返回以序列數表示的某日期的天數。 天數是介於 1 到 31 之間的整數。用法DAY(serial_number)DAY 函數用法具有下列參數：ü Serial_number 必需。要查找的日期。應使用 DATE 函數輸入日期，或將日期作為其他公式或函數的結果輸入。例如，使用函數 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。如果日期以文本形式輸入，則會出現問題。其他l Microsoft Excel 可將日期存儲為可用於計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。l 無論提供的日期值的顯示格式如何，YEAR、MONTH 和 DAY 函數返回的值都是公曆值。例如，如果提供的日期的顯示格式是回曆，則 YEAR、MONTH 和 DAY 函數返回的值將是與對應的公曆日期相關聯的值。案例DAYS 函數返回兩個日期之間的天數描述返回兩個日期之間的天數。用法DAYS(end_date, start_date)DAYS 函數用法具有以下參數。ü End_date 必需。 Start_date 和 End_date 是用於計算期間天數的起止日期。ü Start_date 必需。Start_date 和 End_date 是用於計算期間天數的起止日期。注意: Excel 可將日期存儲為序列號，以便可以在計算中使用它們。 默認情況下，1900 年 1 月 1 日的序列號是 1，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39447 天。其他l 如果兩個日期參數為數字，DAYS 使用 EndDate–StartDate 計算兩個日期之間的天數。l 如果任何一個日期參數為文本，該參數將被視為 DATEVALUE(date_text) 並返回整型日期，而不是時間組件。l 如果日期參數是超出有效日期範圍的數值，DAYS 返回 #NUM! 錯誤值。l 如果日期參數是無法解析為字元串的有效日期，DAYS 返回 #VALUE! 錯誤值。案例DAYS360 函數以一年 360 天為基準計算兩個日期間的天數描述按照一年 360 天的演算法（每個月以 30 天計，一年共計 12 個月），DAYS360 函數返回兩個日期間相差的天數，這在一些會計計算中將會用到。 如果財會系統是基於一年 12 個月，每月 30 天，可使用此函數幫助計算支付款項。用法DAYS360(start_date,end_date,[method])DAYS360 函數用法具有下列參數：ü Start_date、end_date 必需。 用於計算期間天數的起止日期。 如果 start_date 在 end_date 之後，則 DAYS360 函數將返回一個負數。 應使用 DATE 函數輸入日期，或者將從其他公式或函數派生日期。 例如，使用函數 DATE(2008,5,23) 以返回 2008 年 5 月 23 日。 如果日期以文本形式輸入，則會出現問題。ü 方法 可選。 邏輯值，用於指定在計算中是採用美國方法 還是歐洲方法。注意:Excel 可將日期存儲為序列號，以便可以在計算中使用它們。 默認情況下，1900 年 1 月 1 日的序列號為 1，2008 年 1 月 1 日的序列號為 39,448，這是因為它距 1900 年 1 月 1 日有 39,447 天。案例EDATE 函數返回用於表示開始日期之前或之後月數的日期的序列號描述返回表示某個日期的序列號，該日期與指定日期 (start_date) 相隔（之前或之後）指示的月份數。 使用函數 EDATE 可以計算與發行日處於一月中同一天的到期日的日期。用法EDATE(start_date, months)EDATE 函數用法具有以下參數：ü Start_date 必需。一個代表開始日期的日期。應使用 DATE 函數輸入日期，或將日期作為其他公式或函數的結果輸入。例如，使用函數 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。如果日期以文本形式輸入，則會出現問題。ü Months必需。 start_date 之前或之後的月份數。 months 為正值將生成未來日期；為負值將生成過去日期。其他Microsoft Excel 可將日期存儲為可用於計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。如果 start_date 不是有效日期，則 EDATE 返回 錯誤值 #VALUE!。如果 months 不是整數，將截尾取整。案例EOMONTH 函數返回指定月數之前或之後的月份的最後一天的序列號描述返回某個月份最後一天的序列號，該月份與 start_date 相隔（之後或之後）指示的月份數。 使用函數 EOMONTH 可以計算正好在特定月份中最後一天到期的到期日。用法EOMONTH(start_date, months)EOMONTH 函數用法具有以下參數：ü Start_date 必需。一個代表開始日期的日期。應使用 DATE 函數輸入日期，或將日期作為其他公式或函數的結果輸入。例如，使用函數 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。如果日期以文本形式輸入，則會出現問題。ü Months 必需。 start_date 之前或之後的月份數。 months 為正值將生成未來日期；為負值將生成過去日期。注意: 如果 months 不是整數，將截尾取整。其他l Microsoft Excel 可將日期存儲為可用於計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。l 如果 start_date 不是有效日期，則 EOMONTH 返回 錯誤值 #NUM!。l 如果 start_date 加 months 產生非法日期值，則 EOMONTH 返回 錯誤值 #NUM!。案例HOUR 函數將序列號轉換為小時描述返回時間值的小時數。 小時數是介於 0 (12:00 A.M.) 到 23 (11:00 P.M.) 之間的整數。用法HOUR(serial_number)HOUR 函數用法具有下列參數：ü Serial_number 必需。 時間值，其中包含要查找的小時數。 時間值有多種輸入方式：帶引號的文本字元串（例如 6:45 PM）、十進位數（例如 0.78125 表示 6:45 PM）或其他公式或函數的結果（例如 TIMEVALUE(6:45 PM)）。其他時間值為日期值的一部分，並用十進位數表示（例如 12:00 PM 可表示為 0.5，因為此時是一天的一半）。案例ISOWEEKNUM 函數返回給定日期在全年中的 ISO 周數描述返回給定日期在全年中的 ISO 周數。用法ISOWEEKNUM(date)ISOWEEKNUM 函數用法具有下列參數：ü 日期 必需。 日期是 Excel 用於日期和時間計算的日期-時間代碼。其他l Microsoft Excel 可將日期存儲為可用於計算的序列數字。 默認情況下，1900 年 1 月 1 日的序列數字是 1，而 2008 年 1 月 1 日的序列數字是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。l 如果日期參數為無效數值，則 ISOWEEKNUM 將返回錯誤值 #NUM! 。l 如果日期參數為無效日期類型，則 ISOWEEKNUM 將返回錯誤值 #VALUE! 。案例MINUTE 函數將序列號轉換為分鐘描述返回時間值中的分鐘。 分鐘是一個介於 0 到 59 之間的整數。用法MINUTE(serial_number)MINUTE 函數用法具有下列參數：ü Serial_number 必需。 一個時間值，其中包含要查找的分鐘。 時間值有多種輸入方式：帶引號的文本字元串（例如 6:45 PM）、十進位數（例如 0.78125 表示 6:45 PM）或其他公式或函數的結果（例如 TIMEVALUE(6:45 PM)）。其他時間值為日期值的一部分，並用十進位數表示（例如 12:00 PM 可表示為 0.5，因為此時是一天的一半）。案例MONTH 函數將序列號轉換為月描述返回日期（以序列數表示）中的月份。 月份是介於 1（一月）到 12（十二月）之間的整數。用法MONTH(serial_number)MONTH 函數用法具有下列參數：Serial_number 必需。要查找的月份日期。應使用 DATE 函數輸入日期，或將日期作為其他公式或函數的結果輸入。例如，使用函數 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。如果日期以文本形式輸入，則會出現問題。其他Microsoft Excel 可將日期存儲為可用於計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。無論提供的日期值的顯示格式如何，YEAR、MONTH 和 DAY 函數返回的值都是公曆值。例如，如果提供的日期的顯示格式是回曆，則 YEAR、MONTH 和 DAY 函數返回的值將是與對應的公曆日期相關聯的值。案例NETWORKDAYS 函數返回兩個日期間的完整工作日的天數描述返回參數 start_date 和 end_date 之間完整的工作日數值。 工作日不包括周末和專門指定的假期。 可以使用函數 NETWORKDAYS，根據某一特定時期內僱員的工作天數，計算其應計的報酬。提示: 若要使用參數來指明周末的日期和天數，從而計算兩個日期間的全部工作日數，請使用 NETWORKDAYS.INTL 函數。用法NETWORKDAYS(start_date, end_date, [holidays])NETWORKDAYS 函數用法具有下列參數：ü Start_date 必需。 一個代表開始日期的日期。ü End_date 必需。 一個代表終止日期的日期。ü Holidays 可選。不在工作日曆中的一個或多個日期所構成的可選區域，例如：省/市/自治區和國家/地區的法定假日以及其他非法定假日。該列表可以是包含日期的單元格區域，或是表示日期的序列號的數組常量。重要: 應使用 DATE 函數輸入日期，或者將日期作為其他公式或函數的結果輸入。 例如，使用函數 DATE(2012,5,23) 輸入 2012 年 5 月 23 日。 如果日期以文本形式輸入，則會出現問題。其他Microsoft Excel 可將日期存儲為可用於計算的序列號。 默認情況下，1900 年 1 月 1 日的序列號為 1，2012 年 1 月 1 日的序列號為 40909，這是因為它距 1900 年 1 月 1 日有 40,909 天。如果任一參數不是有效日期，則 NETWORKDAYS 返回 錯誤值 #VALUE!。案例NETWORKDAYS.INTL 函數返回兩個日期之間的完整工作日的天數描述返回兩個日期之間的所有工作日數，使用參數指示哪些天是周末，以及有多少天是周末。 周末和任何指定為假期的日期不被視為工作日。用法NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])NETWORKDAYS.INTL 函數用法具有以下參數：ü start_date 和 end_date 必需。 要計算其差值的日期。 start_date 可以早於或晚於 end_date，也可以與它相同。ü weekend 可選。 表示介於 start_date 和 end_date 之間但又不包括在所有工作日數中的周末日。 Weekend 是一個用於指定周末日的周末數字或字元串。weekend 數值表示以下周末日：周末字元串值的長度為七個字元，並且字元串中的每個字元表示一周中的一天（從星期一開始）。1表示非工作日，0表示工作日。 在字元串中僅允許使用字元1和 0。 使用 1111111 將始終返回 0。例如，0000011 結果為星期六和星期日是周末。ü holidays 可選。 一組可選的日期，表示要從工作日日曆中排除的一個或多個日期。 holidays 應是一個包含相關日期的單元格區域，或者是一個由表示這些日期的序列值構成的數組常量。 holidays 中的日期或序列值的順序可以是任意的。其他l 如果 start_date 晚於 end_date，則返回值將為負數，數量將是所有工作日的數量。l 如果 start_date 在當前日期基準值的範圍之外，則 NETWORKDAYS.INTL 返回 錯誤值 #NUM!。l 如果 end_date 在當前日期基準值的範圍之外，則 NETWORKDAYS.INTL 返回 錯誤值 #NUM!。l 如果 weekend 字元串的長度無效或包含無效字元，則 NETWORKDAYS.INTL 返回 錯誤值 #VALUE!。案例以上是所有EXCEL的日期和時間函數（上）說明語法以及使用案例。這次分享中存在哪些疑問或者哪些不足，可以在下面進行評論。如果覺得不錯，可以分享給你的朋友，讓大家一起掌握這些excel的邏輯函數。