Cách cộng trừ thời gian trong excel

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

Trong bài viết này, Học Excel Online sẽ chỉ dẫn phần nhiều cách làm hữu dụng nhằm cộng/trừ thời hạn, chênh lệch thời gian, đếm giờ…

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

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

Như bạn đã biết, thời hạn vào Excel thường được format số thập phân để trông kiểu như thời hạn. Vì bọn chúng là số, bắt buộc chúng ta cũng có thể cộng trừ thời hạn nlỗi những cực hiếm số khác.Quý khách hàng đã xem: Cách tính thời gian

Công thức đơn giản dễ dàng duy 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 tiễn có thể có tương đối nhiều dạng khác biệt, ví dụ:

Công thứcGiải thích
=A2-B2Tính toán chênh lệch thời hạn thân nhị giá trị vào ô A2 và B2
=TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”)Tính toán thù chênh lệch thời hạn giữa nhì thời gian cố thể
=TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) – TIME(HOUR(B2), MINUTE(B2), SECOND(B2))Tính toán thù chênh lệch thời gian giữa nhì quý hiếm vào ô A2 và B2, ngoại trừ chênh lệch tháng ngày ví như ô tính cất cả giá trị tháng ngày và thời gian

Trong khối hệ thống Excel, thời hạn bộc lộ ở đoạn thập phân, bạn sẽ có tác dụng nhỏng sau:


*

Phần thập phân vào cột D đúng tuy nhiên không tồn tại ý nghĩa sâu sắc. Để góp bọn chúng cung cấp tin bổ ích rộng, bạn cũng có thể vận dụng định dạng thời gian với một trong số mã sau đây:

Mã thời gianGiải thích
hSố giờ đồng hồ 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ố giờ, số phút với số giây trôi qua, ví dụ 4:10:trăng tròn.

Bạn đang xem: Cách cộng trừ thời gian trong excel

Để áp dụng định hình thời hạn, dấn Ctrl + 1 nhằm mngơi nghỉ vỏ hộp thoại Format Cells, lựa chọn Custom từ bỏ Category danh sách và gõ mã thời hạn vào hộp Type.

Bây tiếng, để xem phương pháp tính chênh lệch thời hạn và mã thời gian hoạt động ra sao vào bảng tính thực thụ. Với Start time vào cột A và End time vào cột B, chúng ta cũng có thể xào luộc 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 thuộc theo format thời hạn trong mỗi cột:


*

Chụ ý. Nếu thời hạn chênh lệch được hiển tỉnh thành #####, tức là ô cất cách làm không được rộng lớn nhằm hiển thị hết thời gian hoặc kết quả phnghiền tính là 1 trong giá trị âm.

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

Một nghệ thuật không giống để 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ồ thân nhì thời gian: =TEXT(B2-A2, “h”)Tính toán thù 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 chênh lệch tiếng, phút ít với giây thân hai thời gian: =TEXT(B2-A2, “h:mm:ss”)


*

Chú ý.

Kết trái của hàm TEXT luôn luôn là văn bản. Các quý giá text được căn lề trài trong số cột C, D, E trong hình bên trên. Trong vài tình huống một mực, vấn đề đó có thể là 1 tinh giảm bởi bạn sẽ cần yếu thực hiện hiệu quả này nhằm tiếp tục tính tân oán.

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

Để hiển thị chênh lệch giữa nhì thời hạn bằng một đơn vị chức năng (giờ, phút ít, giây), bạn phải tiến hành các phxay tính sau.

Phxay tính giờ đồng hồ giữa nhì thời gian

=(End time – Start time) * 24

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

=(B2-A2) * 24

Để bao gồm số Complete hours, sử dụng hàm INT để làm tròn cho số nguim hần nhất:

=INT((B2-A2) * 24)

 

Tổng số phút thân nhị thời gian:

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

=(End time – Start time) * 1440

Công thức rất có thể mang lại công dụng âm hoặc dương, tác dụng âm xảy ra Lúc thời gian dứt lớn hơn thời gian bước đầu như hàng 5:

=(B2-A2)*1440

 

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

Để tính số giây giữa nhị 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ỏng sau:

=(B2-A2)* 86400


Chú ý. Định dạng General đề xuất được tùy chỉnh mang đến ô tính đựng công thức 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 gian trong và một đơn vị chức năng thời hạn cùng bỏ qua các đơn vị chức năng không giống

Để tính chênh lệch giữa nhì thời hạn vào và một đơn vị chức năng, bỏ qua mất những đơn vị thời hạn không giống, hãy thực hiện một trong số hàm sau:

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

=HOUR(B2-A2)

Chênh lệch phút ít, làm lơ tiếng cùng giây:

=MINUTE(B2-A2)

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

=SECOND(B2-A2)

khi sử dụng hàm HOUR, MINUTE, SECOND, hãy hãy nhớ là hiệu quả ko được quá thừa 24 tiếng, 60 phút ít hoặc 60 giây.


Chụ ý. Nếu thời hạn dứt to hơn thời gian ban đầu, ví dụ giả dụ tác dụng là số âm, bí quyết vẫn hiển thị lỗi #NUM!

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

Để tính thời gian sẽ trôi qua, chúng ta có thể thực hiện hàm Now với Time. Trường phù hợp khoảng tầm thời hạn trôi qua dưới 24 giờ đồng hồ, bạn chỉ việc thực hiện hàm NOW để mang hiệu quả tháng ngày cùng thời hạn hiện tại, sau đó trừ đi ngày với thời gian ban đầu.

Giả sử ô A2 đựng thời gian ban đầu, cách làm =NOW()-A2 mang đến hiệu quả sau cùng với cột B được định dạng thời gian phù hợp, vào ví dụ này nó có dạng h:mm

 

Trong ngôi trường đúng theo khoảng thời hạn trôi qua quá thừa 24 tiếng, thực hiện một trong số định hình thời gian sau, ví dụ d ‘days’ h:mm:ss nhỏng trong hình sau:


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


Chụ ý. Khoảng thời gian trôi qua sẽ không còn được cập nhật theo thời hạn thực tiễn, nó chỉ update Lúc bảng tính được msinh sống lại hoặc tính tân oán lại. Để update cách làm, dìm Shift + F9 nhằm tính lại trang tính hoặc nhận F9 nhằm tính lại toàn bộ bảng tính.

Xem thêm: Hướng Dẫn Các Cách Chuyển Thư Vào Folder Trong Outlook, Di Chuyển Thư Mục Email

Công thức 6. Hiển thị chênh lệch thời gian “XX days, XX hours, XX minutes và XX seconds”

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

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

 

Để ẩn giá trị 0, lồng bốn 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 “,””) và IF(SECOND(B2-A2)>0, SECOND(B2-A2) & ” seconds”,””)

 

Dường như, chúng ta có thể tính tân oán chênh lệch thời hạn bằng cách đem thời hạn bước đầu trừ thời hạn hoàn thành (ví dụ = B2-A2) rồi tùy chỉnh format sau đến ô tính:

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


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

khi tính toán thù chênhh lệch thờ gian, chúng ta cũng có thể phát hiện lỗi ##### do độ chênh lệch này âm. Tuy nhiên, cũng có không ít cách để hiển thị hiệu quả âm vào Excel.

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

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


Nhấp OK để giữ thiết lập bắt đầu, quý hiếm âm sẽ tiến hành hiển thị nhỏng sau:


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

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


Chụ ý. Hãy hãy nhờ rằng phương thức đầu tiên cho ra hiệu quả âm là giá trị số âm, tác dụng hàm TEXT luôn là chuỗi văn uống bạn dạng cùng quan yếu sử dụng vào phnghiền toán hoặc phương pháp khác.

Cộng/trừ thời gian vào Excel

Về cơ bạn dạng, tất cả 2 cách 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 trên thời gian (24), số phút ít (1440), số giây (86400) vào một ngày.

Hàm TIME(hour, minute, second) giúp bài toán tính toán thù dễ dàng hơn, tuy nhiên nó không được cho phép bài toán cùng trừ rộng 23h, 59 phút hoặc 59 giây. Nếu bạn muốn tính toán khoảng tầm thời hạn to hơn, hãy thực hiện các phxay toán tiếp sau đây.

Tđắm đuối khảo ngay: Giáo trình Excel 2020

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

Hàm thời hạn để thêm vào đó dưới 24 giờ

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

lấy một ví dụ, vào ô A2 là thời hạn bước đầu, bạn muốn thêm 2 tiếng đồng hồ vào đó, cách làm đang là:

=A2 + TIME(2, 0, 0)

 

Công thức để cộng bất kỳ khoảng thời gian như thế nào (dưới/trên 24 giờ)

=Start time + (N hours/24)

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

=A2 + (28/24)


Để trừ đi một số giờ đồng hồ, áp dụng phương pháp tương tự như như gắng dấu + bằng dấu –

lấy một ví dụ, trừ ô A2 mang đến 3 giờ, nhập một trong số bí quyết 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 bí quyết thứ nhất.

Cách cộng/trừ phút vào thời gian chũm 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 muốn vào tyêu thích số đồ vật 2:

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

Đây là một trong những vài ba phương pháp thực tế 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,20,0)

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

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

Trong phép tính của công ty, phân tách số phút mang lại 1440 – số phút ít vào một ngày cùng thêm thời gian ban đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút, thay vệt + 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 gian cố gắng thể

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

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

Để thêm vào đó số giây lớn hơn 59, sử dụng công thức:

=Start time + (N seconds / 86400)

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

Trên trang tính Excel, công thức 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 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

Phxay cùng Excel thường áp dụng hàm SUM, và áp dụng format tương xứng mang đến hiệu quả.

Giả sử chúng ta có một vài ba thời hạn 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), công dụng hiển thị theo format khoác định nlỗi hh:mm:ss

 

Trong một vài ngôi trường đúng theo, định dạng thời gian mặc định rất có thể ổn, tuy vậy đôi lúc bạn có nhu cầu hiển thị tổng thời gian bên dưới dạng phút và giây, hoặc chỉ hiển thị giây. khi đó, bạn ko bắt buộc phxay tính như thế nào cả, các bạn chỉ việc định dạng lại thời hạn mang lại ô tính cất công thức SUM.

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

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

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

Kết quả thật sau:


Cách cùng qua 24 tiếng vào Excel

Để tính toán thù kết quả rộng 24 tiếng, chúng ta cũng có thể áp dụng công thức SUM, và lựa chọn một trong những định dạng sau:

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

Để xem hiệu quả định hình vào trang tính thực, xem hình sau đây (bí quyết SUM được nhtràn vào ô A9 cho tới ô A13:

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

Crúc ý. Định dạng thời hạn chỉ vận dụng đến số dương. Nếu công dụng tính toán là số âm, tức là chúng ta rước quý hiếm bé dại rộng trừ mang lại quý hiếm to hơn, kết quả đã hiển thị #####.


Bài viết xem nhiều