Cách trừ thời gian trong excel

0.1 Cách tính chênh lệch thời gian vào Excel0.2 Cách tính với hiển thị thời hạn âm vào Excel0.3 Cộng/trừ thời gian vào Excel

Trong bài viết này, Học Excel Online đã gợi ý gần như công thức hữu dụng để cộng/trừ thời hạn, chênh lệch thời gian, đếm giờ; cách cùng trừ thời gian 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 vào Excel

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

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

Công thức dễ dàng và đơn giản tuyệt nhất nhằm tính chênh lệch thời gian là:

=End time – Start time


*

*

Tùy vào cấu tạo tài liệu, chênh lệch thời gian thực tế rất có thể có không ít dạng không giống nhau, ví dụ:

Công thứcGiải thích
=A2-B2Tính tân oán chênh lệch thời hạn thân nhị quý hiếm vào ô A2 cùng B2
=TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”)Tính tân oán chênh lệch thời gian thân hai thời gian chũm 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 hạn giữa nhì quý hiếm trong ô A2 với B2, không tính chênh lệch ngày tháng nếu ô tính cất cả giá trị tháng ngày và thời gian

Trong hệ thống Excel, thời hạn thể hiện ở phần thập phân, bạn sẽ bao gồm tác dụng nlỗi sau:


*

1 – phương pháp tính thời gian vào Excel


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

Mã thời gianGiải thích
hSố tiếng trôi qua, ví dụ 4
h:mmSố tiếng và số phút ít trôi qua, ví dụ 4:10
h:mm:ssSố tiếng, số phút ít và số giây trôi qua, ví dụ 4:10:đôi mươi.

Để vận dụng format thời gian, nhấn Ctrl + 1 để msống hộp thoại Format Cells, chọn Custom trường đoản cú Category các mục và gõ mã thời hạn vào hộp Type.

Bây tiếng, giúp thấy bí quyết tính chênh lệch thời gian cùng mã thời hạn vận động ra làm sao trong bảng tính đích thực. Với Start time trong cột A với End time trong cột B, bạn có thể coppy bí quyết 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 theo định hình thời hạn trong những cột:


*

2 – cách tính thời gian trong Excel


Chụ ý. Nếu thời hạn chênh lệch được hiển đô thị #####, nghĩa là ô cất bí quyết không đủ rộng lớn để hiển thị không còn thời hạn hoặc kết quả phnghiền tính là 1 quý 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ác để tính chênh lệch thời hạn vào Excel là sử dụng hàm TEXT .

Tính tân oán chênh lệch giờ đồng hồ thân nhì thời gian: =TEXT(B2-A2, “h”)Tính tân oán chênh lệch giờ đồng hồ với phút ít giữa hai thời gian: =TEXT(B2-A2, “h:mm”)Tính toán thù chênh lệch giờ đồng hồ, phút với giây giữa nhì thời gian: =TEXT(B2-A2, “h:mm:ss”)
*

3 – cách tính thời hạn vào Excel


Chú ý.

Kết trái của hàm TEXT luôn là vnạp năng lượng bản. Các cực hiếm text được căn uống lề trài trong số cột C, D, E vào hình bên trên. Trong vài trường hợp nhất thiết, điều này hoàn toàn có thể là 1 hạn chế vị các bạn sẽ không thể áp dụng công dụng này nhằm thường xuyên tính tân oán.

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

Để hiển thị chênh lệch giữa nhị thời gian bởi một đơn vị (giờ đồng hồ, phút ít, giây), bạn phải thực hiện những phnghiền tính sau.

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

=(End time – Start time) * 24

Giả sử thời hạn bắt đầu trong ô A2 và thời hạn xong xuôi trong ô B2, bạn hãy sử dụng phnghiền tính dễ dàng là B2-A2 nhằm tính tân oán chênh lệch thời hạn, tiếp nối nhân cùng với 24.

=(B2-A2) * 24

Để có số Complete hours, sử dụng hàm INT để làm tròn đến số nguim hần nhất:

=INT((B2-A2) * 24)


*

4 – cách tính thời gian trong Excel


 

Tổng số phút ít giữa nhì 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 với 1440

=(End time – Start time) * 1440

Công thức có thể đến hiệu quả âm hoặc dương, hiệu quả âm xẩy ra lúc thời hạn ngừng to hơn thời gian ban đầu như hàng 5:

=(B2-A2)*1440


*

5 – cách tính thời hạn vào Excel


 

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

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

=(End time – Start time) * 86400

Trong ví dụ, cách làm sẽ như sau:

=(B2-A2)* 86400


*

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


Chụ ý. Định dạng General buộc phải được cấu hình thiết lập mang lại ô tính cất bí quyết tính chênh lệch thời hạn để tác dụng hiển thị đúng.

Công thức 4. Tính chênh lệch thời hạn vào và một đơn vị thời gian và bỏ lỡ các đơn vị chức năng khác

Để tính chênh lệch thân nhị thời gian vào và một đơn vị, bỏ qua các đơn vị thời hạn khác, hãy sử dụng một trong số 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 ít, bỏ lỡ tiếng và giây:

=MINUTE(B2-A2)

Chênh lệch giây, làm lơ giờ với phút:

=SECOND(B2-A2)

Lúc áp dụng hàm HOUR, MINUTE, SECOND, hãy hãy nhớ là công dụng không được vượt thừa 24 tiếng, 60 phút ít hoặc 60 giây.


*

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


Crúc ý. Nếu thời hạn ngừng lớn hơn thời hạn bước đầu, cụ thể giả dụ tác dụng là số âm, công thức đang hiển thị lỗi #NUM!

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

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

Giả sử ô A2 chứa thời gian ban đầu, phương pháp =NOW()-A2 mang lại kết quả sau với cột B được format thời hạn tương xứng, vào ví dụ này nó gồm dạng h:mm


*

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


 

Trong ngôi trường vừa lòng khoảng chừng thời hạn trôi qua vượt quá 24 giờ, thực hiện một trong những định hình ít ngày sau, ví dụ d ‘days’ h:mm:ss nlỗi trong hình sau:


*

9 – cách tính thời gian trong Excel


Nếu thời gian bắt đầu chỉ gồm giờ phút ít cơ mà không tồn tại ngày, bạn cần áp dụng hàm Time để tính tân oán khoảng chừng thời gian trôi đi một giải pháp chính xác. ví dụ như, công thức sau trả về khoảng tầm thời hạn trường đoản cú ô A2 mang lại giờ đồng hồ.

Xem thêm: Data-Driven Là Gì

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


*

10- phương pháp tính thời hạn vào Excel


Chú ý. Khoảng thời gian trôi qua sẽ không còn được update theo thời gian thực tiễn, nó chỉ update lúc bảng tính được mnghỉ ngơi lại hoặc tính tân oán lại. Để cập nhật cách làm, nhấn Shift + F9 nhằm tính lại trang tính hoặc dấn F9 để tính lại toàn cục 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 và XX seconds”

Quý khách hàng áp dụng các hàm HOUR, MINUTE cùng SECOND để mang kết quả thời hạn tương tứng và hàm INT để tính chênh lệch ngày.Sau đó, các bạn lồng ghép các hàm vào cùng một bí quyết cùng những chuỗi text nlỗi sau :

=INT(B2-A2) và ” days, ” và HOUR(B2-A2) và ” hours, ” & MINUTE(B2-A2) và ” minutes and ” và SECOND(B2-A2) và ” seconds”

 

*

Để ẩn cực hiếm 0, lồng tứ hàm IF vào đó:

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

 

*

Hình như, chúng ta cũng có thể tính toán thù chênh lệch thời hạn bằng cách lấy thời hạn bước đầu trừ thời hạn dứt (ví dụ = B2-A2) rồi thiết lập cấu hình format sau cho ô tính:

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


*

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


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

khi tính toán chênhh lệch thờ gian, chúng ta có thể phát hiện lỗi ##### vị độ chênh lệch này âm. Tuy nhiên, cũng có nhiều cách để hiển thị tác dụng âm trong Excel.

Phương thơm pháp 1. Ttốt đổ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 xuống When calculating this workbook và ghi lại lựa chọn Use 1904 date system.


*

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


Nhấp OK nhằm giữ thiết đặt new, giá trị âm sẽ tiến hành hiển thị nhỏng sau:

*

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

Nếu không thích sử dụng phương pháp 1, bạn có thể áp dụng cách làm sau:

=IF(A2-B2>0, A2-B2, “-” & TEXT(ABS(A2-B2),”h:mm”))

=IF(A2-B2>0, A2-B2, TEXT(ABS(A2-B2),”-h:mm”))

Cả nhị cách làm đang kiểm soát chênh lệch thời hạn (A2-B2) gồm lớn hơn 0 hay không với nó có hiện nay hiệu quả hay không. Nếu chênh lệch nhỏ nhiều hơn 0, phương pháp trước tiên công thêm trị hoàn hảo nhất rồi thêm vết trừ. Công thức thiết bị nhị mang đến công dụng tương tự như bởi định hình thời gian âm “-h:: mm”.

*

Chụ ý. Hãy nhớ là cách thức đầu tiên tạo ra tác dụng âm là quý giá số âm, hiệu quả hàm TEXT luôn là chuỗi vnạp năng lượng bản cùng bắt buộc thực hiện vào phxay toán hoặc phương pháp khác.

Cộng/trừ thời hạn trong Excel

Về cơ bản, bao gồm 2 biện pháp cộng/trừ thời gian vào Excel:

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

Hàm TIME(hour, minute, second) góp câu hỏi tính toán thuận lợi rộng, mặc dù nó ko được cho phép Việc cộng trừ hơn 23h, 59 phút ít hoặc 59 giây. Nếu bạn có nhu cầu tính toán khoảng tầm thời hạn to hơn, hãy sử dụng những phxay toán tiếp sau đây.

Tsay mê khảo ngay: Giáo trình Excel 2020

Cộng/trừ tiếng vào thời gian cầm cố thể

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

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

lấy một ví dụ, vào ô A2 là thời gian bước đầu, bạn có nhu cầu thêm 2 tiếng đồng hồ vào đó, công thức sẽ là:

=A2 + TIME(2, 0, 0)

 

*

Công thức nhằm cùng ngẫu nhiên số giờ như thế nào (dưới/trên 24 giờ)

=Start time + (N hours/24)

Ví dụ, để cộng thêm 28 tiếng vào ô A2, nhập công thức sau:

=A2 + (28/24)

*

Để trừ đi một số giờ, áp dụng công thức tương tự như nlỗi cụ lốt + bằng dấu –

ví dụ như, trừ ô A2 đến 3h, nhập một trong số cách làm sau:

=A2-(3/24)

=A2-TIME(3,0,0)

Nếu chúng ta trừ thời gian lớn hơn 23, sử dụng bí quyết thứ nhất.

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

Cộng/trừ 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 tsay đắm số vật dụng 2:

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

Đây là 1 trong những vài ba công thức thực tiễn nhằm tính số phút ít vào Excel:

Công trăng tròn phú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ừ bên trên 60 phút

Trong phxay tính của người sử dụng, phân chia số phút đến 1440 – số phút ít trong một ngày cùng thêm thời hạn bước đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút ít, cố 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 vắt thể

Để cộng thêm số giây thấp hơn 60 vào thời gian cụ thể, áp dụng hàm TIME:

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

Để cộng thêm số giây to hơn 59, sử dụng công thức:

=Start time + (N seconds / 86400)

Để trừ số giây, tận dụng phương pháp tựa như tuy vậy cố kỉnh vết + thành lố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 đến 40 giây: =A2 – TIME(0,0,40)Trừ ô A2 đến 900 giây: =A2 – (900/86400)Cách cộng thời gian vào Excel

Phép cộng Excel thường sử dụng hàm SUM, và vận dụng định hình cân xứng mang lại công dụng.

Giả sử các bạn bao gồm một vài ba thời hạn vào cột B và bạn muốn cộng chúng với nhau. Bạn viết phương pháp SUM nhỏng sau =SUM(B2:B4), hiệu quả hiển thị theo định dạng mặc định nlỗi hh:mm:ss

 

*

Trong một vài trường đúng theo, format thời gian mang định hoàn toàn có thể ổn định, cơ mà thỉnh thoảng bạn có nhu cầu hiển thị tổng thời gian bên dưới dạng phút cùng giây, hoặc chỉ hiển thị giây. Lúc đó, các bạn ko đề xuất phnghiền tính nào cả, chúng ta chỉ việc định dạng lại thời hạn mang đến ô tính cất phương pháp SUM.

Nhấp nên vào ô tính cùng lựa chọn Format Cells từ bỏ danh sách tùy lựa chọn, dấn Ctrl + 1 nhằm mlàm việc hộp thoại Format Cells. Chọn Custom tự Category danh mục cùng gõ một trong số định dạng sau vào vỏ hộp Type:

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

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

Kết quả như sau:

*

Cách cùng qua 24 giờ vào Excel

Để tính tân oán tác dụng hơn 24 tiếng, chúng ta cũng có thể áp dụng cách làm SUM, với chọn 1 trong những định hình sau:

Định dạngHiển thịGiải thích
:mm30:10Giờ và phút
:mm:ss30:10:20Giờ, phút cùng giây
“hours”, milimet “minutes”, ss “seconds”30 hours, 10 minutes, đôi mươi seconds
d h:mm:ss1 06:10:20Ngày, giờ đồng hồ, phút ít với 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 & trăng tròn seconds

Để coi tác dụng định dạng trong trang tính thực, coi hình dưới đây (bí quyết SUM được nhtràn vào ô A9 tới ô A13:

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

Chú ý. Định dạng thời hạn chỉ vận dụng mang đến 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ỏ rộng trừ mang đến giá trị lớn hơn, công dụng vẫn hiển thị #####.

*

Đó là cục bộ phương pháp để các chúng ta cũng có thể phương pháp cộng trừ thời gian trong excel. Nếu còn bất cứ vướng mắc gì thì đừng hổ hang nngay sát cmt xuống bên dưới sẽ được đáp án nkhô nóng độc nhất nhé!