Trong bài viết này, Học Excel Online sẽ gợi ý những phương pháp hữu ích để cộng/trừ thời gian, chênh lệch thời gian, đếm giờ; bí quyết cộng trừ thời hạn trong excel.

Bạn đang xem: Trừ thời gian trong excel


Cách tính chênh lệch thời gian trong ExcelCách tính và hiển thị thời gian âm trong ExcelCộng/trừ thời gian trong ExcelCách cộng thời gian trong ExcelCách hiển thị khoảng chừng thời gian to hơn 24 giờ, 60 phút, 60 giây?Cách cộng trừ thời gian lớn hơn 24 giờ, 60 phút, 60 giây

Cách tính chênh lệch thời hạn trong Excel

Công thức 1. Trừ thời gian

Như chúng ta đã biết, thời gian trong Excel thường xuyên được định hình số thập phân để trông giống thời gian. Vày chúng là số, nên bạn cũng có thể cộng trừ thời gian như những giá trị số khác.

Công thức đơn giản và dễ dàng nhất để tính chênh lệch thời hạn là:

=End time – Start time

Tùy vào cấu trúc dữ liệu, chênh lệch thời hạn thực tế có thể có khá nhiều dạng không giống nhau, ví dụ:

Công thứcGiải thích
=A2-B2Tính toán chênh lệch thời hạn giữa hai quý giá trong ô A2 với B2
=TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”)Tính toán chênh lệch thời gian giữa hai thời gian cụ thể
=TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) – TIME(HOUR(B2), MINUTE(B2), SECOND(B2))Tính toán chênh lệch thời gian giữa hai cực hiếm trong ô A2 cùng B2, ko kể chênh lệch ngày tháng giả dụ ô tính chứa cả quý giá ngày tháng và thời gian

Trong khối hệ thống Excel, thời gian biểu hiện ở phần thập phân, bạn sẽ có kết quả như sau:

*
1 – biện pháp tính thời gian trong Excel

Phần thập phân vào cột D đúng nhưng không tồn tại ý nghĩa. Để góp chúng báo tin có ích hơn, bạn cũng có thể áp dụng định dạng thời gian với một trong các mã sau đây:

Mã thời gianGiải thích
hSố giờ trôi qua, lấy ví dụ như 4
h:mmSố giờ với số phút trôi qua, ví dụ 4:10
h:mm:ssSố giờ, số phút với số giây trôi qua, ví dụ 4:10:20.

Để vận dụng định dạng thời gian, dìm Ctrl + 1 để mở hộp thoại Format Cells, lựa chọn Custom từ Category các mục và 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 vận động như nắm nào trong bảng tính thực sự. Với Start time trong cột A và End time vào cột B, chúng ta có thể sao chép công thức vào cột C với E:


*

*

=$B2-$A2

Thời gian chênh lệch được hiển thị khác nhau tùy vào định dạng thời hạn trong mỗi cột:

*
2 – phương pháp tính thời gian trong Excel

Chú ý. Nếu thời gian chênh lệch được hiển đô thị #####, tức là ô chứa cách làm không đầy đủ rộng để hiển thị hết thời hạn hoặc hiệu quả phép tính là 1 giá trị âm.

Công thức 2. Tính chênh lệch thời gian bằng hàm TEXT

Một chuyên môn khác để tính chênh lệch thời gian trong Excel là sử dụng hàm TEXT .

Tính toán chênh lệch giờ giữa hai thời gian: =TEXT(B2-A2, “h”)Tính toán chênh lệch giờ và phút giữa hai thời gian: =TEXT(B2-A2, “h:mm”)Tính toán chênh lệch giờ, phút cùng giây giữa hai thời gian: =TEXT(B2-A2, “h:mm:ss”)
*
3 – giải pháp tính thời gian trong Excel

Chú ý.

Kết trái của hàm TEXT luôn luôn là văn bản. Những giá trị text được căn lề trài trong số cột C, D, E trong hình trên. Vào vài tình huống nhất định, điều này hoàn toàn có thể là một tiêu giảm vì bạn sẽ không thể sử dụng kết quả này để liên tiếp tính toán.

Công thức 3. Tính giờ, phút, giây giữa hai thời gian

Để hiển thị chênh lệch giữa hai thời hạn bằng một đơn vị (giờ, phút, giây), chúng ta cần thực hiện các phép tính sau.

Phép tính giờ thân hai thời gian

=(End time – Start time) * 24

Giả sử thời gian bước đầu trong ô A2 với thời gian kết thúc trong ô B2, bạn hãy thực hiện phép tính dễ dàng là B2-A2 để thống kê giám sát chênh lệch thời gian, tiếp nối nhân với 24.

=(B2-A2) * 24

Để tất cả số Complete hours, thực hiện hàm INT để làm tròn cho số nguyên hần nhất:

=INT((B2-A2) * 24)

*
4 – bí quyết tính thời gian trong Excel

 

Tổng số phút giữa hai thời gian:

Để tính số phút thân hai thời gian, nhân chênh lệch thời hạn với 1440

=(End time – Start time) * 1440

Công thức có thể cho hiệu quả âm hoặc dương, tác dụng âm xảy ra khi thời gian ngừng lớn hơn thời gian bước đầu như sản phẩm 5:

=(B2-A2)*1440

*
5 – phương pháp tính thời hạn trong Excel

 

Tổng số giây giữa hai thời gian:

Để tính số giây thân hai thời gian, nhân chênh lệch thời gian với 86400

=(End time – Start time) * 86400

Trong ví dụ, phương pháp sẽ như sau:

=(B2-A2)* 86400

*
6 – bí quyết tính thời hạn trong Excel

Chú ý. Định dạng General nên được thiết lập cấu hình cho ô tính chứa bí quyết 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 đối kháng vị thời hạn và quăng quật qua các đơn vị khác

Để tính chênh lệch thân hai thời hạn trong cùng một đối chọi vị, bỏ qua những đơn vị thời hạn khác, hãy thực hiện một trong số hàm sau:

Chênh lệch giờ, bỏ qua mất phút cùng giây:

=HOUR(B2-A2)

Chênh lệch phút, bỏ qua giờ cùng giây:

=MINUTE(B2-A2)

Chênh lệch giây, bỏ qua mất giờ với phút:

=SECOND(B2-A2)

Khi sử dụng hàm HOUR, MINUTE, SECOND, hãy ghi nhớ rằng kết quả không được vượt thừa 24 giờ, 60 phút hoặc 60 giây.

*
7 – cách tính thời hạn trong Excel

Chú ý. Nếu thời gian xong xuôi lớn hơn thời gian bắt đầu, rõ ràng nếu công dụng là số âm, cách làm sẽ hiển thị lỗi #NUM!

Công thức 5. Tính thời gian trôi qua kể từ thời gian bắt đầu đến bây giờ

Để tính thời hạn đã trôi qua, bạn cũng có thể sử dụng hàm Now cùng Time. Trường hòa hợp khoảng thời hạn trôi qua dưới 24 giờ, bạn chỉ cần áp dụng hàm NOW nhằm lấy kết quả ngày mon và thời hạn hiện tại, tiếp nối trừ đi ngày và thời hạn bắt đầu.

Giả sử ô A2 chứa thời khắc bắt đầu, bí quyết =NOW()-A2 cho tác dụng sau với cột B được định dạng thời hạn phù hợp, trong lấy ví dụ này nó bao gồm dạng h:mm

*
8 – giải pháp tính thời gian trong Excel

 

Trong trường phù hợp khoảng thời hạn trôi qua vượt quá 24 giờ, thực hiện một trong các định dạng thời gian sau, lấy ví dụ như d ‘days’ h:mm:ss như vào hình sau:

*
9 – biện pháp tính thời hạn trong Excel

Nếu thời gian bước đầu chỉ gồm giờ phút mà không có ngày, bạn phải sử dụng hàm Time để giám sát khoảng thời gian trôi đi một cách bao gồm xác. Ví dụ, bí quyết sau trả về khoảng thời hạn từ ô A2 mang đến giờ.

=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) – A2

*
10- cách tính thời gian trong Excel

Chú ý. Khoảng thời hạn trôi qua sẽ không được update theo thời gian thực tế, nó chỉ cập nhật khi bảng tính được mở ra hoặc giám sát lại. Để cập nhật công thức, dìm Shift + F9 để tính lại trang tính hoặc dấn F9 để tính lại toàn 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”

Bạn sử dụng những hàm HOUR, MINUTE SECOND nhằm lấy hiệu quả thời gian tương tứng cùng hàm INT để tính chênh lệch ngày.Sau đó, chúng ta lồng ghép những hàm vào cùng một phương pháp cùng những chuỗi text như sau :

=INT(B2-A2) và ” days, ” và HOUR(B2-A2) và ” hours, ” và MINUTE(B2-A2) và ” minutes và ” và SECOND(B2-A2) và ” 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) & ” minutes and “,””) và IF(SECOND(B2-A2)>0, SECOND(B2-A2) & ” seconds”,””)

 

*

Ngoài ra, chúng ta có thể tính toán chênh lệch thời gian bằng cách lấy thời gian bắt đầu trừ thời gian kết thúc (ví dụ = B2-A2) rồi tùy chỉnh định dạng sau đến ô tính:

d “days,” h “hours,” m “minutes and” s “seconds”

*
11 – phương pháp tính thời gian trong Excel

Cách tính cùng hiển thị thời gian âm trong Excel

Khi đo lường và thống kê chênhh lệch bái gian, bạn có thể bắt gặp lỗi ##### vì độ chênh lệch này âm. Tuy nhiên, cũng có thể có nhiều phương pháp để hiển thị hiệu quả âm vào Excel.

Phương pháp 1. Thay đổi hệ thông ngày tháng của Excel thành 1904 date system

Vào File > Options > Advanced, kéo thanh cuốn xuống When calculating this workbook và khắc ghi chọn Use 1904 date system.

*
12 – cách tính thời hạn trong Excel

Nhấp OK để lưu cài đặt mới, quý giá âm sẽ tiến hành hiển thị như sau:

*

Phương pháp 2. Tính toán thời gian âm bằng công thức

Nếu không muốn sử dụng phương pháp 1, bạn cũng có thể sử 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ả hai phương pháp sẽ chất vấn chênh lệch thời hạn (A2-B2) có to hơn 0 hay không và nó gồm hiện kết quả hay không. Giả dụ chênh lệch bé hơn 0, công thức thứ nhất sẽ tính trị tuyệt vời nhất rồi thêm dấu trừ. Cách làm thứ hai cho công dụng tương tự bằng định dạng thời gian âm “-h:: mm”.

*

Chú ý. Hãy lưu giữ rằng phương pháp đầu tiên mang lại ra hiệu quả âm là quý hiếm số âm, kết quả hàm TEXT luôn luôn là chuỗi văn bản và không thể thực hiện trong phép toán hoặc cách làm khác.

Cộng/trừ thời gian trong Excel

Về cơ bản, bao gồm 2 cách cộng/trừ thời hạn trong Excel:

Sử dụng hàm TIMESử dụng phép toán dựa trên số giờ đồng hồ (24), số phút (1440), số giây (86400) trong một ngày.

Hàm TIME(hour, minute, second) giúp vấn đề tính toán dễ dàng hơn, tuy vậy nó không có thể chấp nhận được việc cộng trừ hơn 23 giờ, 59 phút hoặc 59 giây. Nếu bạn muốn tính toán khoảng thời hạn lớn hơn, hãy sử dụng những phép toán bên dưới đây.

Tham khảo ngay: Giáo trình Excel 2020

Cộng/trừ giờ đồng hồ vào thời hạn cụ thể

Hàm thời hạn để cộng thêm dưới 24 giờ

=Start time + TIME(N hours, 0, 0)

Ví dụ, vào ô A2 là thời gian bắt đầu, bạn muốn thêm 2 giờ vào đó, bí quyết sẽ là:

=A2 + TIME(2, 0, 0)

 

*

Công thức để cộng ngẫu nhiên số giờ nào (dưới/trên 24 giờ)

=Start time + (N hours/24)

Ví dụ, để thêm vào đó 28 giờ đồng hồ vào ô A2, nhập bí quyết sau:

=A2 + (28/24)

*

Để trừ đi một số trong những giờ, thực hiện công thức tựa như như nỗ lực dấu + bằng dấu –

Ví dụ, trừ ô A2 đế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 bạn trừ số giờ lớn hơn 23, thực hiện công thức đầu tiên.

Cách cộng/trừ phút vào thời hạn cụ thể

Cộng/trừ dưới 60 phút

Sử dụng hàm TIME để cùng trừ số phút bạn có nhu cầu vào tham số trang bị 2:

=Start time + TIME(0, N minutes, 0)

Đây là một trong vài công thức thực tiễn để tính số phút trong Excel:

Công trăng tròn phút vào A2: =A2 + TIME(0,20,0)

Trừ A2 mang lại 30 phút: =A2 – TIME(0,30,0)

Cộng trừ trên 60 phút

Trong phép tính của bạn, chia số phút đến 1440 – số phút vào một ngày và thêm thời gian bắt đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút, cụ dấu + thành lố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 ít hơn 60 vào thời hạn cụ thể, thực hiện hàm TIME:

=Start time + TIME(0, 0, N seconds)

Để cộng thêm số giây to hơn 59, thực hiện công thức:

=Start time + (N seconds / 86400)

Để trừ số giây, tận dụng công thức tựa như nhưng cầm dấu + thành dấu -.

Trên trang tính Excel, công thức trông như 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 cho 40 giây: =A2 – TIME(0,0,40)Trừ ô A2 đến 900 giây: =A2 – (900/86400)Cách cộng thời hạn trong Excel

Phép cộng Excel thường sử dụng hàm SUM, và áp dụng định dạng cân xứng cho kết quả.

Giả sử bạn có một vài thời hạn trong cột B và bạn muốn cộng bọn chúng với nhau. Bạn viết công thức SUM như sau =SUM(B2:B4), công dụng hiển thị theo định hình mặc định như hh:mm:ss

 

*

Trong một vài trường hợp, định dạng thời gian mặc định có thể ổn, tuy vậy đôi khi 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. Lúc đó, chúng ta không nên phép tính nào cả, bạn chỉ việc định dạng lại thời gian cho ô tính chứa cách làm SUM.

Nhấp yêu cầu vào ô tính và chọn Format Cells từ list tùy chọn, nhận Ctrl + 1 để mở hộp thoại Format Cells. Chọn Custom từ Category menu và gõ một trong số định dạng sau vào hộp Type:

Hiển thị thời gian dưới dạng phút cùng giây: :ss

Hiển thị thời gian dưới dạng giây:

Kết đúng như sau:

*

Cách cùng qua 24 giờ đồng hồ trong Excel

Để tính toán hiệu quả hơn 24 giờ, chúng ta cũng có thể sử dụng công thức SUM, và lựa chọn một trong các định dạng sau:

Định dạngHiển thịGiải thích
:mm30:10Giờ cùng phút
:mm:ss30:10:20Giờ, phút với giây
“hours”, milimet “minutes”, ss “seconds”30 hours, 10 minutes, đôi mươi seconds
d h:mm:ss1 06:10:20Ngày, giờ, phút và giây
d “day” h:mm:ss1 day 06:10:20
d “day,” h “hours,” m “minutes and” s “seconds”1 day, 6 hours, 10 minutes và 20 seconds

Để xem hiệu quả định dạng vào trang tính thực, coi hình tiếp sau đây (công thức SUM được nhập vào ô A9 tới ô A13:

=SUM($B$2:$B$4)

Chú ý. Định dạng thời gian chỉ áp dụng cho số dương. Nếu công dụng tính toán là số âm, nghĩa là chúng ta lấy giá trị nhỏ tuổi hơn trừ mang lại giá trị béo hơn, kết quả sẽ hiển thị #####.

*

Cách hiển thị khoảng tầm thời gian lớn hơn 24 giờ, 60 phút, 60 giây?

Để hiện lên một khoảng chừng thời gian lớn hơn 24 giờ, 60 phút, 60 giây, hãy sử dụng custom time format nơi mà mã đối kháng vị thời gian tương ứng phía bên trong dấu ngoặc vuông, ví dụ điển hình như , , xuất xắc . Dưới đây là quá trình hướng dẫn đưa ra tiết:

Chọn một hay các ô mà bạn có nhu cầu định dạng.Nhấp chuột cần vào những ô được chọn rồi lại nhấp chuột phải vào Format Cells, hay chúng ta có thể nhấn Ctrl + 1, thao tác này để giúp đỡ mở hộp thoại Format Cells.Trong Number tab, mặt dưới Category, hãy chọn Custom rồi gõ trong những định dạng thời gian dưới đây trong hộp Type:

Lớn rộng 24 giờ: :mm:ss xuất xắc :mm

Lớn rộng 60 phút: :ss

Lớn rộng 60 giây:

Ảnh chụp screen dưới đây cho biết thêm định dạng thời gian “lớn rộng 24 giờ” bên trên thực tế:

Dưới đấy là một vài định hình khác. Chúng ta có thể sử dụng nó để hiển thị những khoảng tầm thời gian lớn hơn chiều dài đối kháng vị thời gian chuẩn.

Miêu tảMã định dạng
Tổng số giờ
Số giờ và phút:mm
Số giờ, phút, giây:mm:ss
Tổng số phút
Số phút với giây:ss
Tổng số giây

Khi được thực hiện cho dữ liệu mẫu của bọn họ (Thời gian tổng số 50:40 trong hình ảnh chụp screen trên), hồ hết định dạng thời hạn này đã cho hiệu quả như sau:

ABC
1Miêu tảThời gian hiển thịĐịnh dạng
2Số giờ50
3Số giờ cùng phút50:40:mm
4Số giờ, phút, giây50:40:30:mm:ss
5Số phút3040
6Sốphút cùng giây3040:30:ss
7Số giây182430

Để phần nhiều khoảng thời gian được hiển thị làm việc trên dễ hiểu hơn đối với người dùng, chúng ta cũng có thể bổ sung đối chọi vị thời hạn bằng những từ viết tắt tương ứng, ví dụ:

ABC
1Miêu tảThời gian hiển thịĐịnh dạng
2Số giờ và phút50 giờ với 40 phút “số giờ đồng hồ và” milimet “số phút”
3Số giờ, phút, giây50h.40m.30s “h.” milimet “m.” ss “s.”
4Số phút3040 phút “số phút”
5Số phút và giây3040 phút và 30 giây “số phút và” ss “số giây”
6Số giây182430 giây “số giây”

Lưu ý. Mặc dù phần lớn khoảng thời hạn trên trông y hệt như chuỗi ký tự, nhưng lại chúng vẫn luôn là giá trị số, cũng chính vì định dạng văn bản số vào Excel chỉ đổi khác về bề ngoài chứ bản chất không cụ đổi. Bởi vì thế, bạn có thể tự vày cộng tốt trừ hầu hết khoảng thời hạn đã được format một bí quyết bình thường, hãy tham chiếu bọn chúng trong cách làm và sử dụng trong những phép tính khác.

Cách cùng trừ thời gian lớn hơn 24 giờ, 60 phút, 60 giây

Để cộng khoảng thời hạn mong ao ước cho một khoảng thời hạn cho sẵn, hãy mang số giờ, phút, tốt giây mà bạn muốn cộng cho đơn vị thời hạn tương ứng vào một ngày (24 giờ, 1440 phút, 86400 giây), rồi cùng số yêu quý cho thời gian bắt đầu.

Cộng hơn 24 giờ.

Thời gian bước đầu + (N/24)

Cộng rộng 60 phút.

Thời gian bước đầu + (N/1440)

Cộng rộng 60 giây.

Thời gian bước đầu + (N/86400)

Trong đó, N là số giờ, phút, hay giây mà bạn muốn cộng.

Đây là một trong vài ví dụ cách làm trên thực tế:

Để cộng 45 giờ cho thời gian bắt đầu trong ô A2:

=A2+(45/24)

Để cùng 100 phút đến thời gian bước đầu trong ô A2:

=A2+(100/1440)

Để cộng 200 giây cho thời gian bắt đầu trong ô A2:

=A2+(200/86400)

Hay là, bạn có thể nhập khoảng thời hạn cần cộng vào một ô riêng biệt rồi tham chiếu ô kia trong công thức bao gồm dạng như trong hình ảnh chụp màn hình dưới đây:

Để trừ khoảng thời gian trong Excel, hãy sử dụng công thức tựa như nhưng hãy chũm dấu cộng bởi dấu trừ nhé!

Trừ hơn 24 giờ

Thời gian bước đầu – (N/24)

Trừ rộng 60 phút.

Xem thêm: Mua Quà Đầy Tháng Cho Bé Gái Tặng Gì Ý Nghĩa Mà Sang? Gợi Ý 25 Món Quà Tặng Đầy Tháng Ý Nghĩa Cho Bé

Thời gian bước đầu – (N/1440)

Trừ hơn 60 giây

Thời gian ban đầu – (N/86400)

Ảnh chụp màn hình dưới đây hiển thị những kết quả:

Lưu ý:

Nếu khoảng thời hạn được giám sát và đo lường có dạng số thập phân, hãy thực hiện định dạng thời gian/ngày nhằm định dạng ô.Nếu sau thời điểm sử dụng định dạng và ô hiển thị #####, thì thường thì ô không đủ rộng để hiển thị giá trị thời gian trong ngày. Để cách xử lý đ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 chuột hay kéo khung bên buộc phải của cột.

Đó là toàn bộ cách để các bạn cũng có thể cách cùng trừ thời gian trong excel. Giả dụ còn bất kể thắc mắc gì thì đừng ngại ngần cmt xuống dưới và để được giải đáp sớm nhất có thể nhé!