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

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

Cách tính chênh lệch thời gian trong Excel

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

Như các bạn sẽ biết, thời hạn vào Excel thường được format số thập phân để trông tương đương thời hạn. Vì chúng là số, đề nghị chúng ta cũng có thể cùng trừ thời hạn như các quý giá số khác.Bạn đang xem: Cách tính thời gian

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

Trong khối hệ thống Excel, thời gian biểu thị tại đoạn thập phân, bạn sẽ gồm hiệu quả nlỗi sau:


*

Phần thập phân trong cột D đúng tuy vậy không có chân thành và ý nghĩa. Để giúp chúng đưa tin có lợi rộng, bạn cũng có thể áp dụng format thời gian với một trong số mã sau đây:

Mã thời gianGiải thích
hSố tiếng trôi qua, ví dụ 4
h:mmSố giờ đồng hồ cùng 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:20.

Bạn đang xem: Hàm tính thời gian trong excel

Để áp dụng định dạng thời gian, nhận Ctrl + 1 để msinh sống vỏ hộp thoại Format Cells, lựa chọn Custom từ Category list với gõ mã thời gian vào vỏ hộp Type.

Bây tiếng, giúp thấy bí quyết tính chênh lệch thời hạn với mã thời hạn vận động ra sao vào bảng tính thực sự. Với Start time vào cột A và End time trong cột B, chúng ta cũng có thể coppy công thức vào cột C cùng E:

=$B2-$A2

Thời gian chênh lệch được hiển thị khác biệt tùy theo định hình thời gian trong mỗi cột:


*

Crúc ý. Nếu thời gian chênh lệch được hiển thị trấn #####, tức là ô đựng phương pháp cảm thấy không được rộng lớn để hiển thị hết thời hạn hoặc tác dụng phép tính là 1 trong những giá trị â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 để tính chênh lệch thời hạn vào Excel là thực hiện hàm TEXT .

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


*

Crúc ý.

Kết quả của hàm TEXT luôn là văn uống phiên bản. Các cực hiếm text được căn uống lề trài trong những cột C, D, E trong hình trên. Trong vài ba trường hợp cố định, điều này hoàn toàn có thể là 1 giảm bớt bởi vì bạn sẽ tất yêu áp dụng tác dụng này nhằm thường xuyên 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 giữa hai thời gian bởi một đơn vị (tiếng, phút, giây), bạn phải tiến hành các phxay tính sau.

Phxay tính tiếng giữa nhì thời gian

=(End time – Start time) * 24

Giả sử thời gian bắt đầu vào ô A2 và thời hạn kết thúc vào ô B2, các bạn hãy áp dụng phnghiền tính dễ dàng là B2-A2 để tính toán chênh lệch thời gian, sau đó nhân với 24.

=(B2-A2) * 24

Để tất cả số Complete hours, áp dụng hàm INT để triển khai tròn mang đến số nguim hần nhất:

=INT((B2-A2) * 24)

 

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

Để tính số phút ít thân nhị 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, kết quả âm xẩy ra lúc thời gian hoàn thành to hơn thời hạn bước đầu như sản phẩm 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ụ, cách làm đã nhỏng sau:

=(B2-A2)* 86400


Crúc ý. Định dạng General yêu cầu được tùy chỉnh thiết lập đến ô tính cất 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 vào và một đơn vị thời gian và bỏ qua những đơn vị chức năng khác

Để tính chênh lệch thân nhị thời gian vào cùng một đơn vị, bỏ lỡ những đơn vị chức năng thời hạn khác, hãy áp dụng một trong số hàm sau:

Chênh lệch tiếng, bỏ lỡ phút ít và giây:

=HOUR(B2-A2)

Chênh lệch phút ít, bỏ lỡ giờ và giây:

=MINUTE(B2-A2)

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

=SECOND(B2-A2)

khi áp dụng hàm HOUR, MINUTE, SECOND, hãy đừng quên tác dụng ko được vượt thừa 24 giờ, 60 phút hoặc 60 giây.


Crúc ý. Nếu thời gian kết thúc lớn hơn thời gian bắt đầu, ví dụ nếu công dụng là số âm, bí quyết đã hiển thị lỗi #NUM!

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

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

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

 

Trong ngôi trường đúng theo khoảng tầm thời gian trôi qua vượt quá 24 giờ đồng hồ, sử dụng một trong các định dạng không bao lâu sau, ví dụ d ‘days’ h:mm:ss như vào hình sau:


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


Chú ý. Khoảng thời hạn trôi qua sẽ không được cập nhật theo thời hạn thực tiễn, nó chỉ cập nhật lúc bảng tính được msống lại hoặc tính tân oán lại. Để cập nhật bí quyết, thừa nhận Shift + F9 nhằm tính lại trang tính hoặc dìm F9 nhằm 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 thực hiện những hàm HOUR, MINUTE với SECOND để lấy công dụng thời hạn tương tứng với hàm INT nhằm tính chênh lệch ngày.Sau kia, chúng ta lồng ghnghiền những hàm vào và một cách làm cùng các chuỗi text như sau :

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

 

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

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

 

Trong khi, chúng ta có thể tính toán thù chênh lệch thời gian bằng cách đem thời gian bắt đầu trừ thời gian xong (ví dụ = B2-A2) rồi cấu hình thiết lập định dạng sau đến ô tính:

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


Cách tính và hiển thị thời gian âm trong Excel

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

Pmùi hương pháp 1. Tgiỏi đổi hệ thông ngày tháng 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ới lưu lại chọn Use 1904 date system.


Nhấp OK để giữ thiết đặt new, quý giá âm sẽ được hiển thị như sau:


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

Nếu không thích áp dụng phương pháp 1, chúng ta có thể sử 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ị phương pháp vẫn soát sổ chênh lệch thời gian (A2-B2) có lớn hơn 0 hay không cùng nó có hiện tại tác dụng hay là không. Nếu chênh lệch nhỏ hơn 0, phương pháp trước tiên và tính trị tuyệt vời và hoàn hảo nhất rồi thêm vết trừ. Công thức thiết bị hai mang lại tác dụng tựa như bởi format thời hạn âm “-h:: mm”.


Crúc ý. Hãy nhớ là cách thức đầu tiên tạo ra tác dụng âm là quý hiếm số âm, hiệu quả hàm TEXT luôn luôn là chuỗi văn phiên bản và tất yêu áp dụng vào phnghiền tân oán hoặc bí quyết không giống.

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

Về cơ bạn dạng, gồm 2 bí quyết cộng/trừ thời hạn trong Excel:

Sử dụng hàm TIMESử dụng phxay toán thù dựa vào thời gian (24), số phút í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 tân oán tiện lợi rộng, mặc dù nó không chất nhận được câu hỏi cùng trừ hơn 23 giờ, 59 phút ít hoặc 59 giây. Nếu bạn có nhu cầu tính toán thù khoảng thời gian lớn hơn, hãy sử dụng các phép toán thù dưới đây.

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

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

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

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

lấy một ví dụ, trong ô A2 là thời gian bắt đầu, bạn muốn thêm 2 tiếng vào kia, phương pháp sẽ là:

=A2 + TIME(2, 0, 0)

 

Công thức để cộng bất kỳ thời gian làm sao (dưới/trên 24 giờ)

=Start time + (N hours/24)

lấy ví dụ như, để thêm vào đó 28 tiếng vào ô A2, nhập phương pháp sau:

=A2 + (28/24)


Để trừ đi một số tiếng, sử dụng cách làm tương tự như như thế vệt + bằng dấu –

lấy ví dụ như, trừ ô A2 đến 3h, nhập một trong số công thức sau:

=A2-(3/24)

=A2-TIME(3,0,0)

Nếu chúng ta trừ thời gian to hơn 23, áp dụng công thức trước tiên.

Cách cộng/trừ phút ít vào thời gian núm 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 tham số sản phẩm công nghệ 2:

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

Đây là một vài ba phương pháp thực tế nhằm tính số phút ít vào Excel:

Công đôi mươi phút ít vào A2: =A2 + TIME(0,trăng tròn,0)

Trừ A2 mang đến 30 phút: =A2 – TIME(0,30,0)

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

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

=Start time + (N minutes / 1440)

Để trừ số phút ít, cố kỉnh vệt + 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 gian nuốm thể

Để cộng thêm số giây thấp hơn 60 vào thời gian ví dụ, 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 tối đa cách làm tựa như nhưng lại vắt lốt + thành dấu -.

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 cho 900 giây: =A2 – (900/86400)Cách cùng thời hạn trong Excel

Phnghiền cùng Excel thường xuyên áp dụng hàm SUM, cùng vận dụng format tương xứng mang lại kết quả.

Giả sử chúng ta bao gồm một vài ba thời hạn vào cột B cùng bạn muốn cộng chúng với nhau. quý khách viết bí quyết SUM nhỏng sau =SUM(B2:B4), tác dụng hiển thị theo định hình mặc định như hh:mm:ss

 

Trong một vài ngôi trường hợp, định hình thời gian khoác định có thể ổn, tuy nhiên nhiều lúc 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 kia, các bạn ko đề nghị phxay tính làm sao cả, các bạn chỉ việc format lại thời gian cho ô tính cất cách làm SUM.

Nhấp phải vào ô tính và chọn Format Cells tự danh sách tùy chọn, nhấn Ctrl + 1 nhằm mở hộp thoại Format Cells. Chọn Custom trường đoản cú Category danh mục và gõ một trong những định hình sau vào vỏ hộp Type:

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

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

Kết đúng như sau:


Cách cùng qua 24 tiếng trong Excel

Để tính toán kết quả hơn 24 tiếng, chúng ta có thể thực hiện phương pháp SUM, cùng lựa chọn một trong số đị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 í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 í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 và 20 seconds

Để xem tác dụng định dạng vào trang tính thực, xem hình dưới đây (phương pháp SUM được nhập vào ô A9 cho tới ô A13:

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

Chụ ý.

Xem thêm: 1 Dặm Bằng Bao Nhiêu Cây Số, 1 Dặm Bằng Bao Nhiêu M, Km, Cây Số

Định dạng thời hạn chỉ áp dụng đến số dương. Nếu công dụng tính toán là số âm, nghĩa là bạn lấy giá trị bé dại rộng trừ cho giá trị lớn hơn, hiệu quả sẽ hiển thị #####.


Bài viết xem nhiều