公式與函數

計算兩個日期之間的差

計算兩個日期之間的差

當您想要計算兩個日期之間的差異時,請使用 DATEDIF 函數。 首先,將 [開始日期] 放在儲存格,並將 [結束日期] 放在另一個儲存格中。 然後輸入如下所示的公式。

警告: I f Start_date大於End_date,結果會#NUM!

天數的差異

= DATEDIF (D9,E9,"d"),結果為856

在這個範例中,開始日期是在儲存格 D9 中,而結束日期則在 E9 中。 公式是在 F9 中。 "D"傳回兩個日期之間的整天數。

周中的時差

= (DATEDIF (D13,E13,"d")/7)及結果:122.29

在這個範例中,開始日期是在儲存格 D13 中,而結束日期則在 E13 中。 "D" 會傳回天數。 但請注意,結尾的/7 。 這會將天數除以7,因為每週7天。 請注意,此結果也必須設定為數字格式。 按 CTRL + 1。 然後按一下 [數位]> [小數位數: 2]。

月份差異

= DATEDIF (D5,E5,"m")和結果:28

在這個範例中,開始日期是在儲存格 D5 中,而結束日期則在 E5 中。 在公式中, "m"傳回兩個日期之間的完整月份數。

年差額

= DATEDIF (D2,E2,"y")和結果:2

在這個範例中,開始日期是在儲存格 D2 中,而結束日期是在 E2 中。 "Y"傳回兩個日期之間的完整年份數。

以累計年數、月數和天數計算年齡

您也可以計算年齡或某人的服務時間。 結果可以是「2年、4個月、5天」之類的內容。

1. 使用 DATEDIF 找出總年數。

= DATEDIF (D17,E17,"y")和結果:2

在這個範例中,開始日期是在儲存格 D17 中,而結束日期則在 E17 中。 在公式中, "y"傳回兩個日期之間的完整年份數。

2. 使用 [ym] 再次使用 DATEDIF 來尋找月份。

= DATEDIF (D17,E17,"ym")和結果:4

在另一個儲存格中,使用 DATEDIF 公式與"ym"參數。 "Ym" 傳回最後一個完整年份的剩餘月份數。

3. 使用不同的公式來尋找日期。

=DATEDIF(D17,E17,"md") 且結果為:5

現在,我們需要找出剩餘天數的數目。 我們將撰寫不同類型的公式來執行此動作,如上所示。 此公式會以 E17 儲存格中的原始結束日期 (5/6/2016) 減去結束月份的第一天 (5/1/2016)。 以下是公式計算的方式:首先,DATE 函數建立了日期:5/1/2016。 接著,它會使用 E17 儲存格中的年份,以及 E17 儲存格中的月份來產生結果。 而 1 則代表該月份的第一天。 DATE 函數的結果是 5/1/2016。 然後,我們會以 E17 儲存格中的原始結束日期減去上述結果,也就是 5/6/2016。 5/6/2016 減去 5/1/2016 等於 5 天。

警告: 我們建議您不要使用 DATEDIF "md" 引數,因為它可能會計算不正確的結果。

4. 選用:將三個公式合併成一個。

= DATEDIF (D17,E17,"y") & "年," &DATEDIF (D17,E17,"ym") & "月份," &DATEDIF (D17,E17,"md") & "days" 和結果:2年、4個月、5天

您可以像這個範例一樣,將三個計算放在一個儲存格中。 使用 [符號]、[引號] 和 [文字]。 您可以輸入較長的公式,但至少都在其中。 秘訣: 按 ALT + ENTER,將分行符號放在公式中。 這可讓您更容易閱讀。 此外,如果您看不到整個公式,請按 CTRL + SHIFT + U。

下載我們的範例

您可以下載包含本文中所有範例的範例活頁簿。 您可以追蹤,或建立您自己的公式。

下載日期計算範例

其他日期和時間計算

正如您所看到的,DATEDIF 函數會計算開始日期和結束日期之間的差異。 不過,您也可以在公式中使用TODAY ()函數,而不是輸入特定的日期。 當您使用 TODAY ()函數時,Excel 會針對日期使用電腦的目前日期。 請記住,這會在未來一天再次開啟檔案時變更。

= DATEDIF (TODAY (),D28,"y")和結果:984

請注意,在撰寫本文時,這一天是2016年10月6日。

使用 NETWORKDAYS。國際函式,您想要計算兩個日期之間的工作日數。 您也可以讓它排除週末和假日。

開始之前:決定是否要排除假日日期。 如果您這樣做,請在個別的區域或工作表中輸入假日日期清單。 將每個假日日期放在各自的儲存格中。 然後選取這些儲存格,選取 [公式] > [定義名稱]。 命名範圍MyHolidays,然後按一下[確定]。 然後使用下列步驟建立公式。

1. 輸入開始日期和結束日期。

儲存格 D53 中的開始日期是1/1/2016,則 [結束日期] 位於 [儲存格 E53] 是12/31/2016

在這個範例中,開始日期是在儲存格 D53 中,而結束日期則在儲存格 E53 中。

2. 在另一個儲存格中,輸入如下所示的公式:

= NETWORKDAYS。國際(D53,E53,1)和結果:261

輸入如上述範例的公式。 公式中的1會將星期六和星期天建立為週末,並將其排除在總天數內。

注意: Excel 2007 沒有 NETWORKDAYS。國際函式。 不過,它確實有 NETWORKDAYS。 上述範例將在 Excel 2007: = NETWORKDAYS (D53,E53)中贊。 您不會指定1,因為 NETWORKDAYS 會假設週末是星期六和星期天。

3. 如有需要,請變更1。

顯示兩個星期日、星期一的 Intellisense 清單3-星期一、星期二等

如果星期六和星期天不是您的週末日,請將1變更為 IntelliSense 清單中的另一個數位。 例如,2將星期天與星期一建立為週末的天數。

如果您使用的是 Excel 2007,請跳過此步驟。 Excel 2007 的 NETWORKDAYS 函數總是假設週末是星期六和星期天。

4. 輸入假日範圍名稱。

= NETWORKDAYS。國際(D53、E53、1、MyHolidays)及結果:252

如果您在前面的 [開始之前] 區段中建立一個假日範圍名稱,請在末尾輸入它,如下所示。 如果您沒有假日,您可以離開逗號並 MyHolidays 出來。 如果您使用的是 Excel 2007,上述範例會改為: = NETWORKDAYS (D53,E53,MyHolidays)

技巧如果您不想參照假日範圍名稱,您也可以改為輸入範圍,例如D35: E:39。 或者,您可以在公式中輸入每一個假日。 例如,如果您的假日是在2016年1月1日和2號,您可以輸入如下所示: = NETWORKDAYS。國際(D53,E53,1,{"1/1/2016","1/2/2016"})。 在 Excel 2007 中,看起來會像這樣: = NETWORKDAYS (D53,E53,{"1/1/2016","1/2/2016"})

您可以從一個時間減去另一個時間來計算經過的時間。 首先,將 [開始時間] 放在儲存格中,另一個則是 [結束時間]。 請務必輸入完整的時間,包括小時、分鐘,以及在AM 或 PM 之前的空格。 方法如下:

1. 輸入 [開始時間] 和 [結束時間]。

開始日期/時間為 7:15 AM,即 4:30 PM 的結束日期/時間

在這個範例中,開始時間是在儲存格 D80 中,而結束時間是在 E80 中。 請務必在AM 或 PM 前輸入小時、分鐘和空格。

2. 設定 h:mm AM/PM 格式。

[儲存格格式] 對話方塊、自訂命令、h:mm AM/PM 類型

選取兩個日期,然後按 CTRL + 1 (或在 Mac 上 MAC Command 按鈕圖示的圖像 + 1)。 如果尚未設定,請務必選取 [自訂> H:MM AM/PM

3. 減去兩次。

= E80-D80 和 result: 9:15 AM

在另一個儲存格中,從 [結束時間] 儲存格減去 [開始時間] 儲存格。

4. 設定 h:mm 格式。

[儲存格格式] 對話方塊、自訂命令、h:mm 類型

按 CTRL + 1 (或在 Mac 上 MAC Command 按鈕圖示的圖像 + 1)。 選擇 [自訂> h:mm ],讓結果不包括 AM 和 PM。

若要計算兩個日期與時間之間的時間,您可以直接將它減去另一個日期和時間。 不過,您必須將格式設定套用至每個儲存格,以確保 Excel 會傳回您想要的結果。

1. 輸入兩個完整的日期和時間。

1/1/16 1:00 PM 的開始日期;1/2/16 2:00 PM 的結束日期

在一個儲存格中,輸入完整的開始日期/時間。 然後在另一個儲存格中,輸入完整的結束日期/時間。 在 AM 或 PM 前,每個儲存格都應該有一個月、日、年、小時、分鐘和一個空格。

2. 設定 3/14/12 1:30 PM 格式。

[儲存格格式] 對話方塊、[日期] 命令、3/14/12 1:30 PM 類型

選取兩個儲存格,然後按 CTRL + 1 (或在 Mac 上 MAC Command 按鈕圖示的圖像 + 1)。 然後選取 [日期] > 3/14/12 1:30 PM。 這不是您要設定的日期,只是格式外觀的範例。 請注意,在 Excel 2016 之前的版本中,此格式可能會有不同的範例日期,例如 3/14/01 1:30 PM。

3. 減去這兩者。

= E84-D84,結果為1.041666667

在另一個儲存格中,從結束日期/時間減去開始日期/時間。 結果可能看起來會像是數位,也是小數。 您將在下一個步驟中修正這個問題。

4. 設定 [h]: mm 格式。

[儲存格格式] 對話方塊,自訂命令,[h]: mm type

按 CTRL + 1 (或在 Mac 上 MAC Command 按鈕圖示的圖像 + 1)。 選取 [自訂]。 在 [類型] 方塊中,輸入[h]: mm

相關主題

DATEDIF 函數
NETWORKDAYS。國際函數
NETWORKDAYS
更多日期和時間函數
計算兩個時間之間的差

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×