輸入不含冒號的時間 (hhmmss) 並在 Excel 中計算時差

Question

我想輸入不帶冒號的時間hhmmss併計算與另一個單元格的時間差。例如，我在一個儲存格中輸入開始時間，在另一個儲存格中輸入結束時間。然後我需要計算時間差。但它必須包括小時、分鐘和秒。

複雜的是，我想輸入沒有冒號的時間，但在單元格中看到帶有冒號的時間。為此，我使用自訂數字格式設定了儲存格格式00\:00\:00。

Answer 1

一種選擇是將儲存格格式與 Excel 結合使用時間功能。

將時間輸入儲存格（在我的範例中為 A2 和 B2）格式設定為Text.預期的格式始終為hhmmss，因此對於單位數小時的時間輸入前導零。然後你可以用這個公式計算：

=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

這會抓取最左邊的 2 個字元作為“小時”，中間的 2 個字元作為“分鐘”，最右邊的 2 個字元作為“秒”，並將它們轉換為 Excel 識別的時間。然後它從另一個中減去一個，並顯示結果，格式如下hhmmss：

編輯：鑑於要求並不完全符合問題中指定的要求，我修改了公式以通過填充來考慮前導零：

=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))

可讀性極差，但現在用零填充值並使用最右邊的 6，因此無論使用多少個零都應該有效。

我相信您實際上會想要對結果進行特殊格式化，就像hh:mm:ss在本例中一樣。

Answer 2

這是另一種按照您想要輸入的方式處理解碼時間的方法：

我將開始時間和結束時間保留為未格式化的數字，以使操作更加明顯。在此範例中，您的開始時間是午夜或中午後 25 秒，您將輸入 000025。

這種方法根據小時和分鐘在 100 次方中的位置來區分小時和分鐘。 C2中的公式為：

= TIME(INT(B2/10000), INT(MOD(B2/10000,1)*100), RIGHT(B2,2))
 -TIME(INT(A2/10000), INT(MOD(A2/10000,1)*100), RIGHT(A2,2))

MOD 函數與 INT 函數相反。它給出除法後的餘數。

Answer 3

我知道這是一篇舊帖子，但是如果（像我一樣）人們正在通過無數論壇尋找解決方案，以節省輸入時間表的時間，而不必手動輸入“冒號”，而是讓冒號可見，然後能夠進行計算......所有這些都不需要使用VBA腳本，隱藏單元格和公式......等等......等等......這是我想出的解決方案- 它似乎適用於領先零、24 小時時間和跨越午夜的時間表（儘管如果您希望時間表跨越多個午夜，則需要進行調整 - 在我的情況下，我認為這沒有必要）。

另外，我不需要“秒”，但是如果您遵循以下概念，您應該能夠輕鬆地擴展它：

自訂將開始時間和結束時間儲存格設定為：

0#":"##

這會將它們變成“文字單元格”，但填充數字以始終顯示四位數字（即使有前導零，或者對於美國朋友來說為零）。

請注意，這將允許輸入任何看起來像時間格式的四位數。所以理論上可以進入0768，就變成時間07:68，當然不存在了。所以要么

a) 以下列方式建立資料驗證清單： - 在單獨的工作表（標籤）上的單一欄位中，建立所有可接受值的清單。就我而言，這是從 0500、0515、0530、0545.....0445 開始，因為我只處理 15 分鐘的增量。如果你需要深入到秒（認真的？），這會變得很長......即。 050001、050002...等等。 - 選擇所有這些「可接受值」的儲存格，然後在工具列的「公式」標籤中選擇「定義名稱」。 ，選擇涵蓋開始時間的所有儲存格和結束時間，然後前往工具列的“資料”選項卡，選擇“資料驗證>資料驗證>設定”，在“允許”部分從下拉清單中選擇“清單”，在“來源”部分類型在您定義的名稱中前面加上“=”，因此在我的例子中為“=MonkeyTimeList”，然後按一下“確定”，選擇“錯誤警報”選項卡，勾選“顯示錯誤警報...”框，然後在“樣式”下拉列表中選擇“停止”。隨意添加一條錯誤訊息，例如“猴子人說你是個白痴，請嘗試以正確的格式輸入時間！ ”，然後單擊「確定」。

b）理論上，您可以使用條件格式來反白顯示新增了錯誤資料的儲存格。這將避免為資料驗證資料使用單獨的工作表。我沒有這樣做過，所以我才說理論上。這可能是不同的教程...

因此，現在您可以輸入時間，而不必每次都尋找冒號和 Shift 鍵。

如果您確實想要儲存格格式為秒，那麼自訂儲存格格式將如下所示：

0#":"##":"##

所以......現在計算從開始時間到結束時間的小時......再次，這僅基於分鐘......但如果你能遵循邏輯，也可以擴展到秒。

在第三列（假設 A 欄位是開始時間，B 是結束時間，C 是總小時數），輸入公式：

=IF(值(B2)>=值(A2),(SUM(值(MID(文本(B2,"0000"),1,2)),值(MID(文本(B2,"0000"),3 ,2))/60))-(SUM(VALUE(MID(TEXT(A2,"0000"),1,2)),VALUE(MID(TEXT(A2,"0000"),3,2))/ 60)),(SUM(VALUE(MID(文字(B2,"0000"),1,2)),24,VALUE(MID(文字(B2,"0000"),3,2))/60)) -(SUM(VALUE(MID(文本(A2,"0000"),1,2)),VALUE(MID(TEXT(A2,"0000"),3,2))/60)))

現在來解釋一下...

「IF」函數區分 24 小時時鐘上午夜之前和午夜之後的結束時間。如果在午夜之後，則在完成時間上添加 24 小時，以便完成時間減去開始時間的計算仍然有效。如果午夜之前，則無需添加 24 小時。因此，出於計算目的，時間 0100 實際上被視為 2500，0200 被視為 2600，依此類推。筆記。假設輪班時間不超過 23 小時 45 分鐘。如果它運行的時間更長，也許您需要將日期列考慮到您的公式中......再說一遍，我不需要它。

MID 函數作用於 TEXT（因此稱為 TEXT 函數），並以指定的格式從引用的儲存格傳回特定位置的數字... Soooo, "MID(TEXT(B2,"0000"),1,2) "查看儲存格B2，始終以4 位數字/字母的格式查看，並傳回從位置「1」開始的兩位數字。就我而言，這代表整個小時。另一個範例：「MID(TEXT(A2,"000000"),5,2)」將查看儲存格 A2，並傳回從位置「5」開始的兩位數字，這可能是整秒。

我將“VALUE”函數放在所有這些函數的前面，將返回的文字轉換回數字，以便我們可以在計算中使用它。

因此，這使我們能夠為每個單元格分別處理小時、分鐘和秒。小時的計算很簡單，因為它們已經是整數了。然而，分鐘實際上是小時的分數或 60 分鐘的分數，因此，一旦提取了代表“分鐘”的數字，除以“60”即可得到一小時的“分數”。與秒數一樣，除以 3600（一小時內的秒數）即可得到「一小時的小數部分」秒數。

現在時間已表示為帶有分數的數字，您可以根據需要進行加法或減法。就我的公式而言，將給出開始時間和結束時間之間的總小時數。我使用公式將單元格格式化為 Custom> 00.00，然後我可以乘以小時費率等。

如果有人仍然真的想要在他們的公式中加入秒。如果遇到困難，請告訴我。如果我在工作中感到無聊，我也許可以提供幫助......但是秒？真的嗎？