跳到主要內容

如何計算兩次或兩次之間的時差?

如果您有兩個時間列表,例如A列和B列(A列中的時間早於同一行中的B列),如下面的屏幕截圖所示,您將無法獲得正確的時間,以後的時間減去早期的。 因此,現在,我討論在Excel中計算時差的方法。


箭頭藍色右氣泡 在Excel中計算兩次之間的時差

在本例中,我們假設兩列中只有小時,分鐘和秒,並且同一行中A列的時間總是早於B列。

1.選擇一個空白單元格,在這種情況下,我選擇“單元格C2”,鍵入以下公式 = B2-A2 +(A2> B2) (單元格A2中的時間早於單元格B2,您可以根據需要進行更改),按 Enter 鍵,然後拖動填充手柄以填充應用此公式所需的範圍。 看截圖:

2.選擇結果範圍,在這種情況下,選擇結果範圍C2:C6,然後右鍵單擊> 格式化單元格 在上下文菜單中。 看截圖:

3。 在裡面 單元格格式 對話框中單擊 聯繫電話 標籤> Time,然後從右側部分中選擇所需的時間格式,然後點擊 OK。 看截圖:

然後您可以看到時間差已計算如下:


箭頭藍色右氣泡 在Excel中計算兩個日期之間的時差

如果列中不僅有小時,分鐘和秒,而且還有日期,則可以執行以下操作:

1.選擇一個空白單元格,在這種情況下,我選擇“單元格C2”,鍵入以下公式 = B2-A2 (單元格A2中的時間早於單元格B2,您可以根據需要進行更改),按 Enter 鍵,然後拖動填充手柄以填充應用此公式所需的範圍。 看截圖:

2.選擇結果範圍,在這種情況下,選擇範圍C2:C6,然後右鍵單擊> 格式化單元格 在上下文菜單中。 看截圖:

3。 在裡面 單元格格式 對話框中單擊 聯繫電話 標籤> Time,然後選擇 37:30:55 來自 類別 部分,點擊 OK。 看截圖:

然後您可以看到時間差已計算如下:


箭頭藍色右氣泡 用Kutools for Excel計算兩個日期之間的時差

如果要計算時差並將計算出的時差顯示為單詞(3小時5毫秒12秒),則可以使用 日期和時間助手 的效用 Excel的Kutools.

Excel的Kutools, 與以上 300 方便的功能,使您的工作更加輕鬆。 

免費安裝 Kutools for Excel,請執行以下操作:

1.選擇一個將放置時差的單元格,單擊 庫工具 > 公式助手 > 日期和時間助手,請參見屏幕截圖:
doc時差kte 1

2.在彈出的對話框中,選中 差異 複選框,然後選擇兩次 參數輸入 文本框,在下拉列表中根據需要選擇一種顯示類型 輸出結果類型,請參閱screeshot:
doc kutools日期時間助手2

3.單擊確定,然後顯示第一個結果,將自動填充手柄拖到單元格上以計算所有時間差。
doc kutools日期時間助手3 doc kutools日期時間助手4


相關文章:

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (21)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
how to calculate difference for the below scenario


4/8/2019 10:18:22 AM

4/8/2019 6:10:01 PM

please help me..
This comment was minimized by the moderator on the site
Hi, prasad nalliri, just use one date to minus another date, then format the result (a number string) as a date or time format as you need:
A1 4/8/2019 10:18:22 AM

A2 4/8/2019 6:10:01 PM
Using this formula in B1: ==ABS(A1-A2)
Result: 0.327534722
Format the result as time or date
This comment was minimized by the moderator on the site
Hi there, I know how to calculate the difference between a start and finish time. However, I would like to know what is the formula when I also want to minus a target date Is there such a formula?



example: start date: 10/29/17 12:00 AM

A. 1st transaction date: 10/28/17 03:22 PM
B. end of 1st transaction date: 10/30/17 01:13 PM


if Column A is not equal to 10/29/17 12:00am then excess hours should be subtracted from the accumulated hrs from A and B
This comment was minimized by the moderator on the site
Sorry, Could you give a compliacted instance? Through your description, I do not understand clearly.
This comment was minimized by the moderator on the site
Hi there, I know how to calculate the difference between a start and finish time. However, I would like to know what the formula is when I also want to minus 30minutes. Is there such a formula?
This comment was minimized by the moderator on the site
Thanks for your leaving massage, you can try this formula =A1-TIME(0,30,0). See screenshot:
This comment was minimized by the moderator on the site
Hi team, i want to caluculate the time difference in the same cell ans the working hours should be come in next colum for example under timings 9:00am-06:00pm is there i want to know the working time in hours for example like 8 hrs or 9 hours like that. Sample: Timings Hours 09:15 AM - 06:15 PM
This comment was minimized by the moderator on the site
I want to highlight the late comers time by getting the difference of exceed time(late time) with the actual time For ex: if the actual time is 8.00AM if anyone comes by 8.30AM ,the time difference be 0.30minutes .. How can i highlight that 30 minutes using conditional formating in excel? or is there any options to highlight that?
This comment was minimized by the moderator on the site
I am having A1 cell : 04/14/2016 11:15:43 and B2 cell : 03/31/2016 10:41:23 Please let me know the time duration in hrs of between two difference date. exmple refno reg_code status cpv_incoming_date CPV Out CPV Out Time WH CPV IN WH CPV IN time Range CPV In Time ABC A B 09-MAY-2016,14:00:50 10-May-16 16:06:59 10-May-16 9:30:00 >6 hrs and < 8 Hrs 9-May-16 14:00:50
This comment was minimized by the moderator on the site
I am having A1 cell : 04/14/2016 11:15:43 AM and B2 cell : 03/31/2016 10:41:23 AM. Please let me know the time duration in minutes of between two difference date.
This comment was minimized by the moderator on the site
Scenario, Compare the h:mm:ss from the column A1 and display if the value in A2 cell is greater that 15 minutes then display the timing in proper column data condition There is a value in a cell A2, 0:16:38(h:mm:ss) and column B name "
This comment was minimized by the moderator on the site
ENTER EXIT TURN OVER 825 847 913 939 26 1038 1109 59 1133 1153 24 Please can you help me find a formulate to calculate turnover. The difference between Exit to Enter. Thanks.
This comment was minimized by the moderator on the site
Please advise how to convert time result 7.30 to 7.5 hours. Example result shows 7.30 hours different between two that should be total hours 7.5 in the time sheet.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations