Trong nội dung bài viết này, Học Excel Online đang lý giải phần đông phương pháp hữu dụng nhằm cộng/trừ thời gian, chênh lệch thời gian, đếm giờ; phương pháp cùng trừ thời hạn vào excel.
Bạn đang xem: Cách trừ thời gian trong excel
Cách tính chênh lệch thời gian trong ExcelCách tính với hiển thị thời hạn âm vào ExcelCộng/trừ thời hạn trong ExcelCách cùng thời gian vào ExcelCách hiển thị khoảng chừng thời gian to hơn 24 tiếng, 60 phút, 60 giây?Cách cộng trừ thời hạn to hơn 24 giờ đồng hồ, 60 phút ít, 60 giây
Cách tính chênh lệch thời gian vào Excel
Công thức 1. Trừ thời gian
Như chúng ta vẫn biết, thời hạn vào Excel thường được format số thập phân nhằm trông kiểu như thời gian. Vì chúng là số, cần chúng ta có thể cùng trừ thời gian như các cực hiếm số không giống.
Công thức đơn giản duy nhất để tính chênh lệch thời hạn là:
=End time – Start time
Tùy vào kết cấu tài liệu, chênh lệch thời hạn thực tế rất có thể có không ít dạng khác biệt, ví dụ:
| Công thức | Giải thích |
| =A2-B2 | Tính toán thù chênh lệch thời gian thân nhị cực hiếm vào ô A2 cùng B2 |
| =TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”) | Tính toán chênh lệch thời gian thân nhì thời hạn vậy thể |
| =TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) – TIME(HOUR(B2), MINUTE(B2), SECOND(B2)) | Tính tân oán chênh lệch thời hạn thân nhị quý giá trong ô A2 cùng B2, ngoài chênh lệch ngày tháng nếu ô tính chứa cả giá trị ngày tháng và thời gian |
Trong khối hệ thống Excel, thời hạn biểu hiện ở trong phần thập phân, các bạn sẽ có kết quả nhỏng sau:
Phần thập phân trong cột D đúng mà lại không có ý nghĩa. Để giúp chúng báo tin có ích rộng, chúng ta cũng có thể vận dụng định dạng thời gian với cùng 1 trong những mã sau đây:
| Mã thời gian | Giải thích |
| h | Số giờ đồng hồ trôi qua, ví dụ 4 |
| h:mm | Số giờ và số phút trôi qua, ví dụ 4:10 |
| h:mm:ss | Số giờ đồng hồ, số phút với số giây trôi qua, ví dụ 4:10:20. |
Để vận dụng định hình thời hạn, thừa nhận Ctrl + 1 để msống vỏ hộp thoại Format Cells, chọn Custom tự Category menu cùng gõ mã thời hạn vào vỏ hộp Type.
Bây giờ, để xem công thức tính chênh lệch thời gian và mã thời gian chuyển động thế nào trong bảng tính thực sự. Với Start time vào cột A cùng End time trong cột B, chúng ta có thể sao chép bí quyết vào cột C và E:
=$B2-$A2
Thời gian chênh lệch được hiển thị không giống nhau tùy theo format thời gian trong những cột:
Chụ ý. Nếu thời hạn chênh lệch được hiển đô thị #####, tức là ô cất bí quyết không đủ rộng lớn nhằm hiển thị hết thời gian hoặc tác dụng phxay tính là 1 trong cực hiếm âm.
Công thức 2. Tính chênh lệch thời gian bởi hàm TEXT
Một chuyên môn không giống nhằm tính chênh lệch thời gian trong Excel là sử dụng hàm TEXT .
Tính toán thù chênh lệch giờ đồng hồ giữa nhì thời gian: =TEXT(B2-A2, “h”)Tính toán chênh lệch giờ và phút ít thân hai thời gian: =TEXT(B2-A2, “h:mm”)Tính toán thù chênh lệch giờ, phút với giây giữa hai thời gian: =TEXT(B2-A2, “h:mm:ss”)
Chú ý.
Kết quả của hàm TEXT luôn là văn uống phiên bản. Các giá trị text được căn lề trài trong số cột C, D, E trong hình bên trên. Trong vài ba tình huống một mực, vấn đề này rất có thể là 1 trong những tiêu giảm vì bạn sẽ không thể thực hiện tác dụng này nhằm tiếp tục tính toán.Công thức 3. Tính giờ đồng hồ, phút, giây giữa hai thời gian
Để hiển thị chênh lệch thân nhì thời gian bằng một đơn vị chức năng (tiếng, phút, giây), bạn cần triển khai những phxay tính sau.
Phxay tính tiếng giữa nhì thời gian
=(End time – Start time) * 24
Giả sử thời hạn ban đầu trong ô A2 và thời gian xong xuôi vào ô B2, các bạn hãy thực hiện phép tính đơn giản dễ dàng là B2-A2 để tính toán chênh lệch thời hạn, sau đó nhân cùng với 24.
=(B2-A2) * 24
Để gồm số Complete hours, sử dụng hàm INT để triển khai tròn mang lại số ngulặng hần nhất:
=INT((B2-A2) * 24)
Tổng số phút thân hai thời gian:
Để tính số phút giữa hai thời hạn, nhân chênh lệch thời hạn cùng với 1440
=(End time – Start time) * 1440
Công thức rất có thể đến tác dụng âm hoặc dương, kết quả âm xẩy ra Khi thời gian xong to hơn thời gian bắt đầu như sản phẩm 5:
=(B2-A2)*1440
Tổng số giây giữa nhì thời gian:
Để tính số giây giữa hai thời gian, nhân chênh lệch thời gian cùng với 86400
=(End time – Start time) * 86400
Trong ví dụ, công thức đang nhỏng sau:
=(B2-A2)* 86400
Chụ ý. Định dạng General buộc phải được tùy chỉnh thiết lập đến ô tính đựng cách làm tính chênh lệch thời gian để hiệu quả hiển thị đúng.
Công thức 4. Tính chênh lệch thời hạn trong cùng một đơn vị thời gian cùng làm lơ những đơn vị chức năng không giống
Để tính chênh lệch thân nhì thời hạn trong cùng một đơn vị, bỏ lỡ những đơn vị thời hạn không giống, hãy áp dụng một trong những hàm sau:
Chênh lệch giờ đồng hồ, bỏ qua mất phút ít với giây:
=HOUR(B2-A2)
Chênh lệch phút, bỏ lỡ giờ cùng giây:
=MINUTE(B2-A2)
Chênh lệch giây, bỏ qua mất tiếng và phút:
=SECOND(B2-A2)
Lúc áp dụng hàm HOUR, MINUTE, SECOND, hãy hãy nhờ rằng hiệu quả ko được thừa thừa 24 giờ, 60 phút ít hoặc 60 giây.
Crúc ý. Nếu thời gian chấm dứt lớn hơn thời hạn bắt đầu, ví dụ ví như hiệu quả là số âm, bí quyết đang hiển thị lỗi #NUM!
Công thức 5. Tính thời gian trôi qua kể từ thời hạn bắt đầu mang đến bây giờ
Để tính thời hạn sẽ trôi qua, bạn có thể thực hiện hàm Now và Time. Trường vừa lòng khoảng tầm thời gian trôi qua dưới 24 giờ, bạn chỉ cần sử dụng hàm NOW để lấy hiệu quả ngày tháng với thời gian hiện thời, kế tiếp trừ đi ngày với thời gian bước đầu.
Giả sử ô A2 chứa thời điểm bắt đầu, bí quyết =NOW()-A2 mang lại hiệu quả sau cùng với cột B được định dạng thời gian tương xứng, vào ví dụ này nó bao gồm dạng h:mm
Trong trường vừa lòng khoảng thời gian trôi qua quá vượt 24 giờ, thực hiện một trong số format vài ngày sau, ví dụ d ‘days’ h:mm:ss như trong hình sau:
Nếu thời hạn bước đầu chỉ bao gồm giờ đồng hồ phút mà không có ngày, bạn cần thực hiện hàm Time để tính toán khoảng tầm thời hạn trôi đi một biện pháp đúng đắn. ví dụ như, phương pháp sau trả về khoảng tầm thời hạn trường đoản cú ô A2 mang lại tiếng.
=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) – A2
Chụ ý. Khoảng thời hạn trôi qua sẽ không còn được cập nhật theo thời gian thực tế, nó chỉ update lúc bảng tính được msinh hoạt lại hoặc tính toán thù lại. Để cập nhật công thức, nhấn Shift + F9 nhằm tính lại trang tính hoặc dìm F9 để tính lại tổng thể bảng tính.
Công thức 6. Hiển thị chênh lệch thời gian “XX days, XX hours, XX minutes and XX seconds”
Quý khách hàng thực hiện những hàm HOUR, MINUTE với SECOND để mang tác dụng thời gian tương tứng cùng hàm INT nhằm tính chênh lệch ngày.Sau kia, các bạn lồng ghnghiền các hàm vào và một công thức cùng các chuỗi text như sau :
=INT(B2-A2) & ” days, ” và HOUR(B2-A2) & ” hours, ” & MINUTE(B2-A2) & ” minutes & ” & SECOND(B2-A2) & ” seconds”
Để ẩn quý giá 0, lồng tư hàm IF vào đó:
=IF(INT(B2-A2)>0, INT(B2-A2) và ” days, “,””) và IF(HOUR(B2-A2)>0, HOUR(B2-A2) và ” hours, “,””) & IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) và ” minutes & “,””) và IF(SECOND(B2-A2)>0, SECOND(B2-A2) & ” seconds”,””)
Trong khi, bạn có thể tính toán thù chênh lệch thời gian bằng cách mang thời gian bước đầu trừ thời hạn dứt (ví dụ = B2-A2) rồi thiết lập cấu hình định dạng sau cho ô tính:
d “days,” h “hours,” m “minutes and” s “seconds”
Cách tính cùng hiển thị thời gian âm trong Excel
Lúc tính toán chênhh lệch thờ gian, chúng ta có thể bắt gặp lỗi ##### vì độ chênh lệch này âm. Tuy nhiên, cũng có khá nhiều phương pháp để hiển thị kết quả âm trong Excel.
Phương pháp 1. Tgiỏi thay đổi hệ thông tháng ngày của Excel thành 1904 date system
Vào File > Options > Advanced, kéo thanh khô cuốn nắn xuống When calculating this workbook với khắc ghi lựa chọn Use 1904 date system.
Nhấp OK để lưu lại thiết đặt mới, quý giá âm sẽ tiến hành hiển thị nhỏng sau:
Phương thơm pháp 2. Tính toán thù thời gian âm bằng công thức
Nếu không muốn áp dụng phương thức 1, chúng ta có thể áp dụng công thức sau:
=IF(A2-B2>0, A2-B2, “-” và TEXT(ABS(A2-B2),”h:mm”))
=IF(A2-B2>0, A2-B2, TEXT(ABS(A2-B2),”-h:mm”))
Cả nhị bí quyết đã soát sổ chênh lệch thời gian (A2-B2) bao gồm lớn hơn 0 hay là không cùng nó gồm hiện nay tác dụng hay không. Nếu chênh lệch nhỏ thêm hơn 0, phương pháp trước tiên sẽ tính trị hoàn hảo nhất rồi thêm lốt trừ. Công thức sản phẩm nhị đến kết quả tựa như bởi định hình thời hạn âm “-h:: mm”.
Chụ ý. Hãy đừng quên phương thức thứ nhất đã cho ra tác dụng âm là quý hiếm số âm, kết quả hàm TEXT luôn luôn là chuỗi văn uống bạn dạng với quan yếu sử dụng trong phxay tân oán hoặc cách làm khác.
Xem thêm: Muốn Ẩn Số Điện Thoại Trên Zalo Đơn Giản Nhất, Hướng Dẫn Cách Ẩn Số Điện Thoại Trên Zalo
Cộng/trừ thời gian vào Excel
Về cơ phiên bản, có 2 phương pháp cộng/trừ thời hạn vào Excel:
Sử dụng hàm TIMESử dụng phxay tân oán dựa vào số giờ (24), số phút ít (1440), số giây (86400) trong một ngày.Hàm TIME(hour, minute, second) góp bài toán tính tân oán dễ dãi hơn, tuy vậy nó không chất nhận được việc cộng trừ hơn 23 giờ, 59 phút ít hoặc 59 giây. Nếu bạn muốn tính tân oán khoảng thời hạn to hơn, hãy thực hiện những phxay tân oán sau đây.
Tđê mê khảo ngay: Giáo trình Excel 2020
Cộng/trừ tiếng vào thời gian vậy thể
Hàm thời gian nhằm thêm vào đó bên dưới 24 giờ
=Start time + TIME(N hours, 0, 0)
Ví dụ, trong ô A2 là thời hạn bước đầu, bạn muốn thêm 2 giờ vào đó, cách làm đã là:
=A2 + TIME(2, 0, 0)
Công thức để cộng ngẫu nhiên thời gian như thế nào (dưới/bên trên 24 giờ)
=Start time + (N hours/24)
lấy một ví dụ, nhằm cộng thêm 28 tiếng vào ô A2, nhập phương pháp sau:
=A2 + (28/24)
Để trừ đi một vài giờ, sử dụng công thức tương tự nhỏng nỗ lực dấu + bằng dấu –
lấy ví dụ, trừ ô A2 mang đến 3 giờ, nhập một trong những công thức sau:
=A2-(3/24)
=A2-TIME(3,0,0)
Nếu chúng ta trừ số giờ lớn hơn 23, sử dụng cách làm đầu tiên.
Cách cộng/trừ phút vào thời hạn nỗ lực thể
Cộng/trừ bên dưới 60 phút
Sử dụng hàm TIME nhằm cộng trừ số phút ít bạn có nhu cầu vào tmê mệt số thứ 2:
=Start time + TIME(0, N minutes, 0)
Đây là một trong những vài cách làm thực tiễn nhằm tính số phút vào Excel:
Công trăng tròn phút ít vào A2: =A2 + TIME(0,đôi mươi,0)
Trừ A2 mang đến 30 phút: =A2 – TIME(0,30,0)
Cộng trừ trên 60 phút
Trong phép tính của chúng ta, phân chia số phút mang đến 1440 – số phút vào một ngày với thêm thời gian bước đầu vào :
=Start time + (N minutes / 1440)
Để trừ số phút, cố gắng dấu + thành vệt -. Ví dụ :
Cộng thêm 200 phút: =A2 + (200/1440)
Trừ đi 300 phút: =A2 -(300/1440)
Cách cộng/trừ giây vào thời hạn cụ thể
Để thêm vào đó số giây thấp hơn 60 vào thời gian cụ thể, thực hiện hàm TIME:
=Start time + TIME(0, 0, N seconds)
Để cộng thêm số giây lớn hơn 59, áp dụng công thức:
=Start time + (N seconds / 86400)
Để trừ số giây, tận dụng tối đa cách làm tựa như mà lại nỗ lực lốt + thành vết -.
Trên trang tính Excel, phương pháp trông nhỏng sau:
Thêm 30 giây vào ô A2: =A2 + TIME(0,0,31)Thêm 1200 giây vào ô A2: =A2 + (1200/86400)Trừ ô A2 mang đến 40 giây: =A2 – TIME(0,0,40)Trừ ô A2 mang đến 900 giây: =A2 – (900/86400)Cách cùng thời gian vào ExcelPhnghiền cùng Excel thường xuyên áp dụng hàm SUM, và vận dụng format cân xứng đến hiệu quả.
Giả sử chúng ta tất cả một vài ba thời gian vào cột B cùng bạn muốn cùng bọn chúng với nhau. Quý Khách viết cách làm SUM nhỏng sau =SUM(B2:B4), tác dụng hiển thị theo định dạng mặc định nlỗi hh:mm:ss
Trong một vài ba ngôi trường phù hợp, định dạng thời gian mang định có thể ổn, tuy nhiên đôi lúc bạn muốn hiển thị tổng thời gian dưới dạng phút cùng giây, hoặc chỉ hiển thị giây. Khi kia, chúng ta ko bắt buộc phxay tính như thế nào cả, các bạn chỉ cần định hình lại thời hạn đến ô tính đựng bí quyết SUM.
Nhấp đề xuất vào ô tính và lựa chọn Format Cells tự danh sách tùy chọn, dấn Ctrl + 1 để mnghỉ ngơi vỏ hộp thoại Format Cells. Chọn Custom trường đoản cú Category danh sách cùng gõ một trong số format sau vào hộp Type:
Hiển thị thời hạn dưới dạng phút và giây:
Hiển thị thời gian bên dưới dạng giây:
Kết đúng thật sau:
Cách cùng qua 24 tiếng trong Excel
Để tính toán thù công dụng hơn 24 giờ, chúng ta có thể thực hiện bí quyết SUM, cùng chọn một trong những định hình sau:
| Định dạng | Hiển thị | Giải thích |
| 30:10 | Giờ cùng phút | |
| 30:10:20 | Giờ, phút cùng giây | |
| 30 hours, 10 minutes, 20 seconds | ||
| d h:mm:ss | 1 06:10:20 | Ngày, giờ đồng hồ, phút với giây |
| d “day” h:mm:ss | 1 day 06:10:20 | |
| d “day,” h “hours,” m “minutes and” s “seconds” | 1 day, 6 hours, 10 minutes & 20 seconds |
Để coi kết quả format vào trang tính thực, xem hình tiếp sau đây (công thức SUM được nhập vào ô A9 cho tới ô A13:
=SUM($B$2:$B$4)
Chụ ý. Định dạng thời hạn chỉ áp dụng mang lại số dương. Nếu kết quả tính tân oán là số âm, tức thị chúng ta lấy cực hiếm nhỏ hơn trừ cho quý giá lớn hơn, tác dụng vẫn hiển thị #####.
Cách hiển thị khoảng thời gian lớn hơn 24 giờ đồng hồ, 60 phút ít, 60 giây?
Để hiện thị một khoảng tầm thời gian lớn hơn 24 tiếng, 60 phút ít, 60 giây, hãy sử dụng custom time format nơi cơ mà mã đơn vị thời gian tương xứng nằm trong dấu ngoặc vuông, ví dụ điển hình như
Lớn rộng 24 giờ:
Lớn rộng 60 phút:
Lớn hơn 60 giây:
Hình ảnh chụp màn hình tiếp sau đây cho biết định dạng thời hạn “lớn hơn 24 giờ” bên trên thực tế:
Dưới đấy là một vài ba định dạng không giống. Chúng ta rất có thể thực hiện nó nhằm hiển thị đầy đủ khoảng thời gian lớn hơn chiều lâu năm đơn vị chức năng thời gian chuẩn.
| Miêu tả | Mã định dạng |
| Tổng số giờ | |
| Số giờ đồng hồ với phút | |
| Số giờ, phút ít, giây | |
| Tổng số phút | |
| Số phút và giây | |
| Tổng số giây |
khi được thực hiện mang lại dữ liệu chủng loại của chúng ta (Thời gian tổng cộng 50:40 vào hình họa chụp màn hình trên), hầu hết format thời gian này đã mang đến hiệu quả nhỏng sau:
| A | B | C | |
| 1 | Miêu tả | Thời gian hiển thị | Định dạng |
| 2 | Số giờ | 50 | |
| 3 | Số giờ và phút | 50:40 | |
| 4 | Số giờ đồng hồ, phút, giây | 50:40:30 | |
| 5 | Số phút | 3040 | |
| 6 | Sốphút với giây | 3040:30 | |
| 7 | Số giây | 182430 |
Để đa số khoảng chừng thời gian được hiển thị sinh hoạt trên dễ nắm bắt hơn đối với người tiêu dùng, bạn có thể bổ sung đơn vị chức năng thời hạn bởi các trường đoản cú viết tắt tương ứng, ví dụ:
| A | B | C | |
| 1 | Miêu tả | Thời gian hiển thị | Định dạng |
| 2 | Số giờ đồng hồ và phút | 50 tiếng và 40 phút | |
| 3 | Số giờ đồng hồ, phút, giây | 50h.40m.30s | |
| 4 | Số phút | 3040 phút | |
| 5 | Số phút và giây | 3040 phút ít và 30 giây | |
| 6 | Số giây | 182430 giây |
Lưu ý. Mặc mặc dù hầu hết khoảng tầm thời hạn trên trông y hệt như chuỗi cam kết từ, dẫu vậy bọn chúng vẫn là quý hiếm số, cũng chính vì định dạng văn bản số vào Excel chỉ chuyển đổi về hiệ tượng chứ thực chất không đổi khác. Vì rứa, chúng ta có thể tự do cùng tuyệt trừ hồ hết khoảng tầm thời hạn đã có format một biện pháp bình thường, hãy tham chiếu chúng trong công thức cùng áp dụng trong số phxay tính khác.
Cách cộng trừ thời gian to hơn 24 giờ, 60 phút ít, 60 giây
Để cộng khoảng tầm thời hạn ước muốn cho 1 khoảng tầm thời gian mang lại sẵn, hãy đem thời gian, phút ít, tuyệt giây nhưng mà bạn muốn cùng cho đơn vị chức năng thời hạn tương ứng trong một ngày (24 giờ đồng hồ, 1440 phút, 86400 giây), rồi cùng số thương mang lại thời hạn bước đầu.
Cộng hơn 24 giờ đồng hồ.Thời gian bước đầu + (N/24)
Cộng rộng 60 phút.Thời gian ban đầu + (N/1440)
Cộng hơn 60 giây.Thời gian bước đầu + (N/86400)
Trong đó, N là số giờ, phút, tuyệt giây cơ mà bạn có nhu cầu cùng.
Đây là 1 trong những vài ba ví dụ phương pháp bên trên thực tế:
Để cùng 45 giờ đồng hồ mang đến thời hạn bắt đầu vào ô A2:
=A2+(45/24)
Để cộng 100 phút cho thời hạn bước đầu vào ô A2:
=A2+(100/1440)
Để cùng 200 giây mang lại thời hạn bắt đầu vào ô A2:
=A2+(200/86400)
Hoặc là, chúng ta cũng có thể nhập khoảng thời hạn đề xuất cùng vào một trong những ô đơn lẻ rồi tđắm đuối chiếu ô kia vào cách làm có dạng nlỗi vào hình họa chụp màn hình hiển thị bên dưới đây:
Để trừ khoảng chừng thời gian trong Excel, hãy sử dụng bí quyết tương tự như tuy thế hãy nạm dấu cộng bằng lốt trừ nhé!
Trừ hơn 24 giờThời gian ban đầu – (N/24)
Trừ hơn 60 phút.Thời gian bắt đầu – (N/1440)
Trừ hơn 60 giâyThời gian bước đầu – (N/86400)
Hình ảnh chụp màn hình hiển thị sau đây hiển thị những kết quả:
Lưu ý:
Nếu khoảng chừng thời gian được tính toán có dạng số thập phân, hãy áp dụng format thời gian/ngày để định hình ô.Nếu sau thời điểm thực hiện định hình và ô hiển thị #####, thì thường thì ô không được rộng nhằm hiển thị cực hiếm thời gian trong thời gian ngày. Để xử trí điều này, hãy không ngừng mở rộng chiều lâu năm ô bằng cách nhấp đúp loài chuột tuyệt kéo size mặt đề xuất của cột.Đó là tổng thể cách để những chúng ta có thể biện pháp cộng trừ thời gian vào excel. Nếu còn bất kể vướng mắc gì thì chớ mắc cỡ ngần cmt xuống dưới và để được câu trả lời nhanh hao tốt nhất nhé!
