Hàm tính giờ trong excel

0.1 phương pháp 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 trong Excel

Trong bài viết này, Học Excel Online sẽ gợi ý những công thức hữu ích nhằm 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 hạn trong Excel

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

Như bạn đã biết, thời hạn trong Excel thường xuyên được định dạng số thập phân nhằm trông tương đương thời gian. Do chúng là số, nên chúng ta có thể cộng trừ thời gian như những giá trị số khác.Bạn sẽ xem: cách tính thời gian

Công thức đơn giản dễ dàng 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 dữ liệu, chênh lệch thời hạn thực tế gồm thể có tương đối nhiều dạng khác nhau, ví dụ:

Công thức	Giải thích
=A2-B2	Tính toán chênh lệch thời gian giữa hai giá trị trong ô A2 cùng 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 hạn giữa hai cực hiếm trong ô A2 và B2, ngoại trừ chênh lệch ngày tháng nếu ô tính chứa cả quý giá ngày tháng cùng thời gian

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

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

Mã thời gian	Giải thích
h	Số tiếng trôi qua, lấy ví dụ như 4
h:mm	Số giờ và số phút trôi qua, ví dụ như 4:10
h:mm:ss	Số 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 giờ trong excel

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

Bây giờ, nhằm xem bí quyết tính chênh lệch thời hạn và mã thời gian hoạt động như vắt nào trong bảng tính thực sự. Cùng với Start time trong cột A và End time vào cột B, bạn cũng có thể sao chép cách làm 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 gian trong từng cột:

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

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

Một nghệ thuật khác nhằm tính chênh lệch thời hạn trong Excel là thực hiện 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ờ cùng 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 quả 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 những 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 có thể là một tinh giảm vì bạn sẽ không thể sử dụng công dụng này để thường xuyên 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 giữa hai thời hạn bằng một đơn vị (giờ, phút, giây), chúng ta cần tiến hành 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ắt đầu trong ô A2 cùng thời gian ngừng trong ô B2, bạn hãy thực hiện phép tính đơn giản là B2-A2 để đo lường chênh lệch thời gian, kế tiếp nhân cùng với 24.

=(B2-A2) * 24

Để có số Complete hours, áp dụng hàm INT để làm tròn mang đến số nguyên hần nhất:

=INT((B2-A2) * 24)

 

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

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

=(End time – Start time) * 1440

Công thức có thể cho tác dụng âm hoặc dương, kết quả âm xẩy ra khi thời gian kết thúc lớn rộng thời gian ban đầ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 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 cấu hình thiết lập cho ô tính chứa cách làm 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 cùng một đơn vị thời gian và vứt qua những đơn vị khác

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

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

=HOUR(B2-A2)

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

=MINUTE(B2-A2)

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

=SECOND(B2-A2)

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

Chú ý. Nếu thời gian dứt lớn hơn thời hạn bắt đầu, cụ thể nếu hiệu quả 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 tính từ lúc thời gian bắt đầu đến bây giờ

Để tính thời gian đã trôi qua, bạn cũng có thể sử dụng hàm Now và Time. Trường phù hợp khoảng thời gian trôi qua dưới 24 giờ, bạn chỉ cần sử dụng hàm NOW nhằm lấy hiệu quả ngày mon và thời gian 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, công thức =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 một ví dụ này nó bao gồm dạng h:mm

 

Trong trường hợp khoảng thời hạn trôi qua vượt thừa 24 giờ, thực hiện một trong các định dạng thời hạn sau, lấy ví dụ như d ‘days’ h:mm:ss như trong 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òn được cập nhật theo thời hạn thực tế, nó chỉ update khi bảng tính được lật lại hoặc thống kê giám sát lại. Để update công thức, dìm Shift + F9 để tính lại trang tính hoặc thừa nhận F9 để tính lại tổng thể bảng tính.

Xem thêm: Thông Tin Tuyển Sinh Trường Đại Học Tài Chính - Marketing Xét Học Bạ 2021

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 những hàm HOUR, MINUTE và SECOND nhằm lấy công dụng thời gian tương tứng và hàm INT nhằm tính chênh lệch ngày.Sau đó, bạn lồng ghép những hàm vào cùng một bí quyết cùng những chuỗi text như sau :

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

 

Để ẩn giá trị 0, lồng tư hàm IF vào đó:

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

 

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

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

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

Khi đo lường và thống kê chênhh lệch bái gian, bạn cũng có thể bắt chạm mặt 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 hóa 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à ghi lại chọn Use 1904 date system.

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

Phương pháp 2. Thống kê giám sát thời gian âm bằng công thức

Nếu không muốn sử dụng cách thức 1, bạn cũng có thể sử dụng bí quyết 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 cách làm sẽ bình chọn chênh lệch thời hạn (A2-B2) có lớn hơn 0 hay không và nó có hiện hiệu quả hay không. Nếu như chênh lệch nhỏ hơn 0, công thức đầu tiên sẽ tính trị tuyệt vời nhất rồi thêm vệt trừ. Bí quyết thứ hai cho công dụng tương tự bởi đị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 kết quả âm là quý giá số âm, kết quả hàm TEXT luôn là chuỗi văn phiên bản và ko thể thực hiện 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 bí quyết 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ố tiếng (24), số phút (1440), số giây (86400) trong một ngày.

Hàm TIME(hour, minute, second) giúp câu hỏi tính toán thuận tiện hơn, tuy vậy nó không được cho phép 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 những phép toán dưới đây.

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

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

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

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

Ví dụ, trong ô A2 là thời gian bắt đầ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 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ách làm sau:

=A2 + (28/24)

Để trừ đi một số trong những giờ, áp dụng công thức tương tự như chũm 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 muốn vào tham số vật dụng 2:

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

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

Công đôi mươi phút vào A2: =A2 + TIME(0,20,0)

Trừ A2 cho 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 đến 1440 – số phút vào một ngày với thêm thời gian ban đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút, nuốm 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 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, 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ông thức tương tự nhưng ráng dấu + thành lốt -.

Trên trang tính Excel, phương pháp 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 mang lại 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ử các 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. Bạn viết cách làm SUM như sau =SUM(B2:B4), tác dụng hiển thị theo format 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 hoàn toàn có thể ổn, dẫu vậy đôi khi bạn có nhu cầu 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 buộc phải phép tính nào cả, bạn chỉ cần định dạng lại thời hạn cho ô tính chứa phương pháp SUM.

Nhấp đề xuất vào ô tính và chọn Format Cells từ danh sách tùy chọn, dìm Ctrl + 1 để mở vỏ hộp thoại Format Cells. Lựa chọn Custom từ Category danh sách 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 và giây: :ss

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

Kết đúng thật 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 phương pháp SUM, và lựa chọn một trong các định dạng sau:

Định dạng	Hiển thị	Giải thích
:mm	30:10	Giờ và phút
:mm:ss	30:10:20	Giờ, phút với giây
“hours”, mm “minutes”, ss “seconds”	30 hours, 10 minutes, trăng tròn seconds
d h:mm:ss	1 06:10:20	Ngày, giờ, phút với giây
d “day” h:mm:ss	1 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, 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ỉ áp dụng cho số dương. Nếu kết quả tính toán là số âm, nghĩa là chúng ta lấy giá chỉ trị bé dại hơn trừ mang đến giá trị mập hơn, tác dụng sẽ hiển thị #####.

Bài viết coi nhiều