Hàm cộng giờ trong excel

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

Trong nội dung bài viết này, Học Excel Online sẽ trả lời những cách làm hữu ích để cộng/trừ thời gian, chênh lệch thời gian, đế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 đã biết, thời gian trong Excel thường xuyên được format số thập phân để trông tương tự thời gian. Vì chưng chúng là số, nên chúng ta cũng có thể cộng trừ thời gian như các giá trị số khác.Bạn đã xem: phương pháp tính thời gian

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

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


*

Phần thập phân vào cột D đúng nhưng không có ý nghĩa. Để góp chúng cung cấp tin có ích hơn, chúng ta cũng có thể áp 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ố tiếng trôi qua, lấy ví dụ như 4
h:mmSố giờ cùng số phút trôi qua, ví dụ như 4:10
h:mm:ssSố giờ, số phút cùng số giây trôi qua, lấy ví dụ 4:10:20.

Bạn đang xem: Hàm cộng giờ trong excel

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

Bây giờ, nhằm xem phương pháp tính chênh lệch thời hạn và mã thời gian chuyển động như thay 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ũng có thể sao chép phương pháp vào cột C cùng 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 từng cột:


*

Chú ý. Nếu thời gian chênh lệch được hiển tỉnh thành #####, nghĩa là ô chứa công thức không đủ rộng để hiển thị hết thời hạn hoặc kết quả phép tính là một trong những 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 nhằm tính chênh lệch thời hạn trong Excel là áp 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ới phút thân hai thời gian: =TEXT(B2-A2, “h:mm”)Tính toán chênh lệch giờ, phút cùng 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. Những giá trị text được căn lề trài trong các cột C, D, E trong hình trên. Trong vài trường hợp nhất định, điều này hoàn toàn có thể là một giảm bớt vì các bạn sẽ không thể sử dụng tác dụng này để liên tiếp tính toán.

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

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

Phép tính giờ giữa hai thời gian

=(End time – Start time) * 24

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

=(B2-A2) * 24

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

=INT((B2-A2) * 24)

 

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 hoàn toàn có thể cho tác dụng âm hoặc dương, kết quả âm xảy ra khi thời gian dứt lớn hơn thời gian bước đầu như mặt hàng 5:

=(B2-A2)*1440

 

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ụ, công thức sẽ như sau:

=(B2-A2)* 86400


Chú ý. Định dạng General nên được thiết lập cho ô tính chứa công thức tính chênh lệch thời hạn để hiệu quả hiển thị đúng.

Công thức 4. Tính chênh lệch thời gian trong thuộc một đối chọi vị thời hạn và vứt qua các đơn vị không giống

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

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

=HOUR(B2-A2)

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

=MINUTE(B2-A2)

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

=SECOND(B2-A2)

Khi thực hiện hàm HOUR, MINUTE, SECOND, hãy nhớ rằng kết quả không được vượt vượt 24 giờ, 60 phút hoặc 60 giây.


Chú ý. Nếu thời gian dứt lớn hơn thời gian bắt đầu, ví dụ nếu tác 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 gian đã trôi qua, chúng ta cũng có thể sử dụng hàm Now với 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 thực hiện hàm NOW để lấy hiệu quả ngày tháng và thời gian hiện tại, sau đó trừ đi ngày và thời hạn bắt đầu.

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

 

Trong trường hòa hợp khoảng thời hạn trôi qua vượt vượt 24 giờ, thực hiện một trong những định dạng thời hạn 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 gian trôi qua sẽ không được cập nhật theo thời gian thực tế, nó chỉ update khi bảng tính được mở ra hoặc đo lường lại. Để update công thức, dấn Shift + F9 để tính lại trang tính hoặc dấn F9 để tính lại tổng thể bảng tính.

Xem thêm: View Page Source Là Gì, Tổng Hợp Đầy Đủ Kiến Thức Về Source Code

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

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

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

 

Ngoài ra, bạn cũng có thể tính toán chênh lệch thời gian bằng cách lấy thời gian bước đầu trừ thời gian dứt (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”


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

Khi tính toán chênhh lệch bái gian, bạn cũng có thể bắt gặp lỗi ##### vì độ chênh lệch này âm. Mặc dù nhiên, cũng có nhiều cách để hiển thị công dụng âm trong Excel.

Phương pháp 1. Biến đổ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.


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


Phương pháp 2. Giám sát thời gian âm bởi công thức

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


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

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

Về cơ bản, bao gồm 2 phương pháp cộng/trừ thời gian trong Excel:

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

Hàm TIME(hour, minute, second) giúp vấn đề tính toán thuận tiện 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 hoặc 59 giây. Nếu bạn có nhu cầu tính toán khoảng thời gian lớn hơn, hãy sử dụng các phép toá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 gian để thêm vào đó dưới 24 giờ

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

Ví dụ, vào ô A2 là thời hạn bắt đầu, bạn muốn thêm 2 tiếng đồng hồ vào đó, công thức sẽ là:

=A2 + TIME(2, 0, 0)

 

Công thức để cộng bất kỳ số giờ 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 công thức sau:

=A2 + (28/24)


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

Ví dụ, trừ ô A2 cho 3 giờ, nhập một trong các 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, áp dụng 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ừ bên dưới 60 phút

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

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

Đây là một vài công thức thực tiễn để tính số phú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ừ trên 60 phút

Trong phép tính của bạn, phân chia số phút mang lại 1440 – số phú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, rá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 ít hơn 60 vào thời hạn cụ thể, sử dụng hàm TIME:

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

Để cộng thêm số giây lớn 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 giống như nhưng nạm dấu + thành vệt -.

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 mang lại 40 giây: =A2 – TIME(0,0,40)Trừ ô A2 đến 900 giây: =A2 – (900/86400)Cách cộng thời gian trong Excel

Phép cộng Excel thường áp dụng hàm SUM, và áp dụng định dạng phù hợp cho kết quả.

Giả sử bạn có một vài thời gian trong cột B và bạn có nhu cầu cộng chúng với nhau. Các bạn viết bí quyết SUM như sau =SUM(B2:B4), kết quả hiển thị theo định dạng mặc định như hh:mm:ss

 

Trong một vài ngôi trường hợp, định dạng thời gian mặc định có thể ổn, cơ mà đôi khi bạn muốn hiển thị tổng thời gian dưới dạng phút và giây, hoặc chỉ hiển thị giây. Lúc đó, bạn không buộc phải phép tính nào cả, bạn chỉ việc định dạng lại thời hạn cho ô tính chứa bí quyết SUM.

Nhấp nên vào ô tính và lựa chọn Format Cells từ list tùy chọn, dìm Ctrl + 1 để mở vỏ hộp thoại Format Cells. Chọn Custom từ Category danh mục và gõ một trong những định dạng sau vào vỏ hộp Type:

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

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

Kết quả như sau:


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

Để tính toán kết quả hơn 24 giờ, bạn cũng có thể sử dụng phương pháp SUM, và chọn 1 trong những định dạng sau:

Định dạngHiển thịGiải thích
:mm30:10Giờ với phút
:mm:ss30:10:20Giờ, phút cùng 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à 20 seconds

Để xem tác dụng định dạng vào trang tính thực, xem hình dưới đâ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ỉ vận dụng cho số dương. Nếu hiệu quả tính toán là số âm, nghĩa là các bạn lấy giá trị nhỏ dại hơn trừ mang đến giá trị phệ hơn, tác dụng sẽ hiển thị #####.


Bài viết coi nhiều