在 Excel 計算時間加減（時差、工作時間）

由於日期和時間在 Excel 的後端存儲為數字，因此您可以輕鬆地對日期和時間值使用簡單的算術運算和公式。

例如，您可以加減兩個不同的時間值或日期值，或者您可以計算兩個給定日期/時間之間的時間差。

在本教程中，我將向您展示在 Excel 中使用時間執行計算的幾種方法（例如計算時差、加減時間、以不同格式顯示時間以及計算時間值的總和）。

Excel 如何處理日期和時間？

正如我所提到的，日期和時間以數字形式存儲在 Excel 的單元格中。整數代表一整天，數字的小數部分代表一天中的部分（可以轉換為小時、分鐘和秒值）

例如，值 1 在 Excel 中表示 1900 年 1 月 1 日，這是 Excel 開始考慮日期的起點。

因此，2 表示 1990 年 1 月 2 日，3 表示 1900 年 1 月 3 日，依此類推，44197 表示 2021 年 1 月 1 日。

注意：Excel for Windows 和 Excel for Mac 遵循不同的開始日期。Excel for Windows 中的 1 表示 1900 年 1 月 1 日，Excel for Mac 中的 1 表示 1904 年 1 月 1 日

如果這些數字中的小數點後有任何數字，Excel 會將這些數字視為一天的一部分，並且可以將其轉換為小時、分鐘和秒。

例如，44197.5 表示 2021 年 1 月 1 日下午 12:00:00。

因此，如果您在 Excel 中使用時間值，則基本上是在使用數字的小數部分。

Excel 讓您可以靈活地將小數部分轉換為不同的格式，例如僅小時、僅分鐘、僅秒或小時、分鐘和秒的組合

既然您了解了 Excel 中的時間是如何存儲的，那麼讓我們看一些示例，了解如何在 Excel 中計算兩個不同日期或時間之間的時間差

計算兩次時間差的公式

在許多情況下，您要做的就是找出兩個時間值之間經過的總時間（例如，在具有 In-time 和 Out-time 的時間表的情況下）。

您選擇的方法取決於單元格中時間的提及方式以及您想要的結果格式。

讓我們看幾個例子

Excel中計算時差的簡單減法

由於時間在 Excel 中存儲為數字，因此找到 2 個時間值之間的差異，您可以輕鬆地從結束時間中減去開始時間。

結束時間 - 開始時間

減法的結果也將是一個十進制值，表示兩個時間值之間經過的時間。

下面是一個示例，其中我有開始時間和結束時間，並且我通過簡單的減法計算了時間差。

您的結果有可能以時間格式顯示（而不是小數或小時/分鐘值）。在上面的示例中，單元格 C2 中的結果顯示 09:30 AM 而不是 9.5。

這很好，因為 Excel 會嘗試從相鄰列複製格式。

要將其轉換為小數，請將單元格的格式更改為“常規”（該選項位於“數字”組的“主頁”選項卡中）

獲得結果後，您可以用不同的方式對其進行格式化。例如，您可以僅以小時或僅以分鐘或小時、分鐘和秒的組合顯示值。

以下是您可以使用的不同格式：

格式	它能做什麼
hh	僅顯示兩個日期之間經過的小時數
mm	以兩位數顯示小時數（例如 04 或 12）
hh：mm	顯示兩個日期之間經過的小時和分鐘，例如 10:20
hh:mm:ss	顯示兩個日期之間經過的小時、分鐘和秒，例如 10:20:36

如果您想知道在何處以及如何應用這些自定義日期格式，請按照以下步驟操作：

1. 選擇要應用日期格式的單元格
2. 按住 Control 鍵並按 1 鍵（如果使用 Mac，則按 Command + 1）
3. 在打開的“單元格格式”對話框中，單擊“數字”選項卡（如果尚未選擇）
4. 在左側窗格中，單擊自定義
5. 在類型字段中輸入任何所需的格式代碼（在此示例中，我使用的是 hh:mm:ss）
6. 點擊確定

上述步驟將更改格式並根據格式向您顯示值。

請注意，自定義數字格式不會更改單元格中的值。它只會改變值的顯示方式。因此，我可以選擇僅在單元格中顯示小時值，而它仍然具有原始值。

專業提示：如果總小時數超過 24 小時，請改用以下自定義數字格式：[hh]:mm:ss

以小時、分鐘或秒為單位計算時差

當您減去時間值時，Excel 會返回一個十進制數，表示生成的時間差。

由於每個整數都代表一天，因此數字的小數部分將代表一天中的大部分時間，可以輕鬆轉換為小時或分鐘或秒。

以小時為單位計算時差

假設您有如下所示的數據集，並且您想要計算兩個時間值之間的小時數

下面的公式將為您提供以小時為單位的時差：

=(B2-A2)*24

上面的公式將為您提供兩個時間值之間經過的總小時數。

有時，Excel 會嘗試提供幫助，並且還會以時間格式為您提供結果（如下所示）。

您可以通過單擊主頁選項卡並在數字組中選擇數字作為格式輕鬆地將其轉換為數字格式。

如果您只想返回兩次之間經過的總小時數（沒有任何小數部分），請使用以下公式：

=INT((B2-A2)*24)

注意：此公式僅適用於兩個時間值在同一天的情況。如果日期發生變化（其中一個時間值是另一個日期，第二個是另一個日期），這個公式會給出錯誤的結果。在本教程後面的日期更改時，請查看我介紹計算時差的公式的部分。

以分鐘為單位計算時差

要以分鐘為單位計算時差，您需要將結果值乘以一天中的總分鐘數（即 1440 或 24*60）。

假設您有一個如下所示的數據集，並且您想要計算開始日期和結束日期之間經過的總分鐘數。

以下是可以做到這一點的公式：

=(B2-A2)*24*60

以秒為單位計算時差

要計算以秒為單位的時間差，您需要將結果值乘以一天中的總秒數（即 24*60*60 或 86400）。

假設您有一個如下所示的數據集，並且您想要計算開始日期和結束日期之間經過的總秒數。

以下是可以做到這一點的公式：

=(B2-A2)*24*60*60

使用 TEXT 函數計算時間差

另一種無需擔心更改格式即可快速獲取時差的簡單方法是使用 TEXT 函數。

TEXT 函數允許您在公式中指定格式。

=TEXT（結束日期 - 開始日期，格式）

第一個參數是您要執行的計算，第二個參數是您要顯示計算結果的格式。

假設您有一個如下所示的數據集，並且您想要計算兩次之間的時間差。

這裡有一些公式可以為您提供不同格式的結果

僅顯示小時數：

=TEXT(B2-A2,"hh")

上面的公式只會給你顯示兩個時間值之間經過的小時數的結果。如果您的結果是 9 小時 30 分鐘，它仍將只顯示 9。

顯示總分鐘數

=TEXT(B2-A2,"[mm]")

顯示總秒數

=TEXT(B2-A2,"[ss]")

顯示小時和分鐘

=TEXT(B2-A2,"[hh]:mm")

顯示小時、分鐘和秒

=TEXT(B2-A2,"hh:mm:ss")

如果您想知道格式（或 mm 和 [mm]）中 hh 和 [hh] 之間的區別是什麼，當您使用方括號時，它會為您提供兩個日期之間的總小時數，即使小時值大於 24。因此，如果您減去兩個相差超過 24 小時的日期值，使用 [hh] 將為您提供總小時數，而 hh 只會為您提供結束日期當天經過的小時數.

獲取一個單位（小時/分鐘）的時差，忽略其他

如果您只想以小時數或分鐘數或秒數計算兩個時間值之間的時間差，則可以使用專用的 HOUR、MINUTE 或 SECOND 函數。

這些函數中的每一個都接受一個參數，即時間值，並返回指定的時間單位。

假設您有一個如下所示的數據集，並且您想要計算這兩次之間經過的總小時數和秒數。

以下是執行此操作的公式：

計算兩次之間經過的小時數

=HOUR(B2-A2)

根據時間值結果計算分鐘（不包括已完成的小時數）

=MINUTE(B2-A2)

從時間值結果計算秒數（不包括完成的小時和分鐘）

=SECOND(B2-A2)

使用這些 HOURS、MINUTE 和 SECOND 公式時需要了解的幾件事：

• 結束時間和開始時間之間的差異不能為負數（日期更改時通常會出現這種情況）。在這種情況下，這些公式將返回 #NUM！錯誤
• 這些公式僅使用結果時間值的時間部分（並忽略天部分）。因此，如果結束時間和開始時間的差是 2 天、10 小時、32 分鐘和 44 秒，則 HOUR 公式將給出 10，MINUTE 公式將給出 32，SECOND 公式將給出 44

計算到現在經過的時間（從開始時間開始）

如果要計算開始時間和當前時間之間經過的總時間，可以使用 NOW 公式而不是結束時間。

NOW 函數返回使用它的單元格中的當前日期和時間。它是不接受任何輸入參數的函數之一。

因此，如果要計算開始時間和當前時間之間經過的總時間，可以使用以下公式：

=NOW() - 開始時間

下面是一個示例，其中我在 A 列中有開始時間，在 B 列中有到現在為止的時間。

如果開始日期和時間與當前時間之間的時間差超過 24 小時，那麼您可以格式化結果以顯示日期和時間部分。

您可以使用以下 TEXT 公式來做到這一點：

=TEXT(NOW()-A2,"dd hh:ss:mm")

您還可以通過更改單元格的自定義格式（如本教程前面所述）來實現相同的目的，以便它顯示日期和時間部分。

如果您的開始時間只有時間部分，那麼 Excel 會將其視為 1990 年 1 月 1 日的時間。

在這種情況下，如果您使用 NOW 函數來計算到現在所經過的時間，它將給您錯誤的結果（因為結果值也將包含自 1990 年 1 月 1 日以來經過的總天數）。

在這種情況下，您可以使用以下公式：

=NOW()- INT(NOW())-A2

上面的公式使用 INT 函數從 now 函數返回的值中刪除天部分，然後用於計算時間差。

請注意，NOW 是一個 volatile 函數，它會在工作表發生更改時更新，但不會實時更新

計算日期更改時的時間（在 Excel 中計算並顯示負時間）

如果您的結束時間晚於開始時間，那麼到目前為止介紹的方法效果很好。

但是，當您的結束時間低於開始時間時，就會出現問題。當您填寫時間表時，通常會發生這種情況，您只輸入時間而不是整個日期和時間。

在這種情況下，如果您在一個夜班工作並且日期發生變化，那麼您的結束時間可能會早於您的開始時間。

例如，如果您在晚上 6:00 PM 開始工作，並在早上 9:00 AM 完成工作並暫停。

如果您只使用時間值，那麼從結束時間中減去開始時間將得到 9 小時 (9 – 18) 的負值。

Excel 無法處理負時間值（就此而言，人類也無法處理，除非您可以進行時間旅行）

在這種情況下，您需要一種方法來確定日期已更改，並應進行相應的計算。

值得慶幸的是，有一個非常簡單的解決方法。

假設您有一個如下所示的數據集，其中我有開始時間和結束時間。

正如您會注意到的那樣，有時開始時間是晚上，結束時間是早上（這表明這是一個通宵班，並且一天已經改變）。

如果我使用下面的公式來計算時間差，它會在結果為負值的單元格中顯示井號（在下圖中以黃色突出顯示）。

=B2-A2

這是一個IF公式，時間差值是否為負，如果為負則返回正確的結果

=IF((B2-A2)<0,1-(A2-B2),(B2-A2))

雖然這在大多數情況下效果很好，但如果開始時間和結束時間相隔超過 24 小時，它仍然不夠用。例如，某人在第 1 天上午 9:00 登錄並在第 2 天上午 11:00 退出。

由於這超過 24 小時，因此無法知道此人是在 2 小時後還是在 26 小時後退出。

雖然解決此問題的最佳方法是確保條目包括日期和時間，但如果它只是您正在使用的時間，那麼上述公式應該可以解決大部分問題（考慮到任何人都不太可能工作超過 24 小時）

在 Excel 中加減時間

到目前為止，我們已經看到了開始和結束時間的例子，我們需要找到時間差。

Excel 還允許您輕鬆地從現有日期和時間值中添加或減去固定時間值。

例如，假設您有一個排隊任務列表，其中每個任務都需要指定的時間，並且您想知道每個任務何時結束。

在這種情況下，您可以輕鬆地將每個任務將花費的時間添加到開始時間，以了解預計任務將在什麼時間完成。

由於 Excel 將日期和時間值存儲為數字，因此您必須確保嘗試添加的時間符合 Excel 已經遵循的格式。

例如，如果您在 Excel 中將日期加 1，它將為您提供下一個日期。這是因為 1 在 Excel 中代表一整天（等於 24 小時）。

因此，如果您想將 1 小時添加到現有時間值，您不能繼續簡單地向其添加 1。您必須確保將該小時值轉換為代表一小時的小數部分。添加分鐘和秒也是如此。

使用時間功能

Excel 中的時間函數獲取小時值、分鐘值和秒值，並將其轉換為表示該時間的十進制數。

例如，如果我想在現有時間上增加 4 小時，我可以使用以下公式：

=開始時間 + TIME(4,0,0)

如果您知道要添加到現有時間的小時數、分鐘數和秒數，並且只需使用 TIME 函數而無需擔心將時間正確轉換為十進制值，這將非常有用。

另外，請注意，TIME 函數只會考慮您輸入的小時、分鐘和秒值的整數部分。例如，如果我在 TIME 函數中使用 5.5 小時，它只會增加 5 小時並忽略小數部分。

另請注意，TIME 函數只能添加小於 24 小時的值。如果你的小時值超過 24，這會給你一個不正確的結果。

分鐘和秒部分也是如此，函數將只考慮小於 60 分和 60 秒的值

就像我使用 TIME 函數添加時間一樣，您也可以減去時間。只需將上述公式中的 + 號更改為負號即可

使用基本算術

當時間功能易於使用時，它確實有一些限制（如上所述）。

如果你想要更多的控制，你可以使用我將在這裡介紹的算術方法。

這個概念很簡單 – 將時間值轉換為代表一天中部分的十進制值，然後您可以將其添加到 Excel 中的任何時間值。

例如，如果您想將 24 小時添加到現有時間值，您可以使用以下公式：

=開始時間 + 24/24

這只是意味著我要在現有時間值上增加一天。

現在採用相同的概念，假設您想將 30 小時添加到時間值，您可以使用以下公式：

=開始時間 + 30/24

上面的公式做同樣的事情，其中​​ (30/24) 的整數部分錶示您要添加的時間的總天數，小數部分錶示小時/分鐘/秒

同樣，如果您想要添加到時間值的特定分鐘數，則可以使用以下公式：

=開始時間 +（添加分鐘數）/24*60

如果您有要添加的秒數，則可以使用以下公式：

=開始時間 +（添加分鐘數）/24*60*60

雖然這種方法不像使用時間函數那樣簡單，但我發現它要好得多，因為它適用於所有情況並遵循相同的概念。與時間功能不同，您不必擔心要添加的時間是小於 24 小時還是大於 24 小時

您也可以在減去時間的同時遵循相同的概念。只需在上面的公式中將 + 更改為負號

如何在Excel中求和時間

有時您可能希望快速將 Excel 中的所有時間值相加。在 Excel 中添加多個時間值非常簡單（只需要一個簡單的 SUM 公式）

但是，當您在 Excel 中添加時間時，您需要了解一些事項，特別是要向您顯示結果的單元格格式。

讓我們看一個例子。

下面我在 B 列中有一個任務列表以及每個任務將花費的時間，我想快速添加這些時間並知道所有這些任務將花費的總時間。

在單元格 B9 中，我使用了一個簡單的 SUM 公式來計算所有這些任務將花費的總時間，它給我的值為 18:30（這意味著它需要 18 小時 20 分鐘才能完成所有這些任務）

到目前為止一切都很好！

如何在 Excel 中求和超過 24 小時

現在看看當我將任務 2 的完成時間從 1 小時更改為 10 小時時會發生什麼。

結果現在顯示 03:20，這意味著完成所有這些任務需要 3 小時 20 分鐘。

這是不正確的（顯然）

這裡的問題不是 Excel 搞砸了。這裡的問題是單元格的格式設置為只顯示結果值的時間部分。

並且由於此處的結果值超過 24 小時，Excel 決定將 24 小時部分轉換為一天，將其從顯示給用戶的值中刪除，僅顯示剩餘的小時、分鐘和秒。

幸運的是，這很容易解決。

您需要做的就是更改單元格格式以強制它顯示小時數，即使它超過 24 小時。

以下是您可以使用的一些格式：

格式	預期結果
[h]:mm	28:30
[m]:ss	1710:00
d “D” hh:mm	第一天 04:30
d “D” hh “Min” ss “Sec”	1 天 04 分鐘 00 秒
d “Day” hh “Minute” ss “Seconds”	1 天 04 分 00 秒

您可以通過轉到格式單元格對話框並應用自定義格式來更改格式，或者使用 TEXT 函數並在公式本身中使用上述任何格式

您可以使用下面的 TEXT 公式來顯示時間，即使它超過 24 小時：

=TEXT(SUM(B2:B7),"[h]:mm:ss")

或者如果要將超過 24 小時的小時數轉換為天數，請使用以下公式：

=TEXT(SUM(B2:B7),"dd hh:mm:ss")

結果顯示哈希 (###) 而不是日期/時間（原因 + 修復）

在某些情況下，您可能會發現 Excel 沒有顯示時間值，而是在單元格中顯示井號。

以下是一些可能的原因以及解決這些問題的方法：

列不夠寬

當一個單元格沒有足夠的空間來顯示完整的日期時，它可能會顯示井號。

它有一個簡單的解決方法——更改列寬並使其更寬。

負日期值

Excel 中的日期或時間值不能為負數。如果您正在計算時差並且結果為負數，Excel 將顯示哈希符號。

修復方法以更改公式以提供正確的結果。例如，如果您正在計算兩次之間的時間差，並且日期發生變化，則需要調整公式以將其考慮在內。

在其他情況下，您可以使用 ABS 函數將負時間值轉換為正數，以便正確顯示。或者，您也可以使用 IF 公式來檢查結果是否為負值並返回更有意義的值。

在本教程中，我介紹了有關在 Excel 中計算時間的主題（您可以在其中計算時間差、加減時間、以不同格式顯示時間以及求和時間值）

我希望您發現本教程很有用。

在 Excel 計算時間加減的常見問題