Table of Contents
Công Thức Tính Giờ Trong Excel, Cộng Trừ, Tính Thời, Cộng Hoặc Trừ Thời Gian
0.1 Phương pháp tính chênh lệch thời gian trong Excel0.2 Phương pháp tính và hiển thị thời gian âm trong Excel0.3 Cộng/trừ thời gian trong Excel
Trong nội dung này, Học Excel Online sẽ hướng dẫn những công thức hữu ích để cộng/trừ thời gian, chênh lệch thời gian, đếm giờ; cách cộng trừ thời gian trong excel.
Đang xem: Công thức tính giờ trong excel
Phương pháp tính chênh lệch thời gian trong Excel
Công thức 1. Trừ thời gian
Như bạn đã biết, thời gian trong Excel thường được định dạng số thập phân để trông giống thời gian. Vì chúng là số, nên bạn có thể cộng trừ thời gian như các giá trị số khác.
Công thức đơn giản nhất để tính chênh lệch thời gian là:
=End time – Start time
Tùy vào cấu trúc dữ liệu, chênh lệch thời gian thực tiễn có thể có 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 và 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 gian giữa hai giá trị trong ô A2 và B2, không tính chênh lệch ngày tháng nếu ô tính chứa cả giá trị ngày tháng và 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:
1 – phương pháp tính thời gian trong Excel
Phần thập phân trong cột D đúng nhưng vô nghĩa. Để giúp chúng phân phối thông tin có ích hơn, bạn có thể ứng dụng định dạng thời gian với một trong các mã sau đây:
Mã thời gian
Giải thích
h
Số giờ trôi qua, ví dụ 4
h:mm
Số giờ và số phút trôi qua, ví dụ 4:10
h:mm:ss
Số giờ, số phút và số giây trôi qua, ví dụ 4:10:20.
Để ứng dụng định dạng thời gian, nhấn Ctrl + 1 để mở hộp thoại Format Cells, chọn Custom từ Category list và gõ mã thời gian vào hộp Type.
Hiện tại, để xem công thức tính chênh lệch thời gian và mã thời gian hoạt động như vậy nào trong bảng tính thực sự. Với Start time trong cột A và End time trong cột B, bạn có thể sao chép công thức vào cột C và 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 mỗi cột:
2 – phương pháp tính thời gian trong Excel
Note. Nếu thời gian chênh lệch được hiển thị thành #####, nghĩa là ô chứa công thức không đủ rộng để 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 kỹ thuật khác để tính chênh lệch thời gian trong Excel là sử 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à phút giữa hai thời gian: =TEXT(B2-A2, “h:mm”)Tính toán chênh lệch giờ, phút và giây giữa hai thời gian: =TEXT(B2-A2, “h:mm:ss”)
3 – phương pháp tính thời gian trong Excel
Note.
Kết quả của hàm TEXT luôn là văn bản. Các 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 tình huống nhất định, điều này có thể là một hạn chế vì bạn sẽ không thể sử dụng kết quả này để tiếp tục tính toán.
Công thức 3. Tính giờ, phút, giây giữa hai thời gian
Để hiển thị chênh lệch giữa hai thời gian bằng một nhà cung cấp (giờ, phút, giây), bạn cần thực hiện 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 khởi đầu trong ô A2 và thời gian kết thúc trong ô B2, bạn hãy sử dụng phép tính đơn giản là B2-A2 để tính toán chênh lệch thời gian, sau đó nhân với 24.
=(B2-A2) * 24
Để có số Complete hours, sử dụng hàm INT để làm tròn đến số nguyên hần nhất:
=INT((B2-A2) * 24)
4 – phương pháp tính thời gian trong Excel
Tổng số phút giữa hai thời gian:
Để tính số phút giữa 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 kết quả âm hoặc dương, kết quả âm xảy ra khi thời gian kết thúc to hơn thời gian khởi đầu như hàng 5:
=(B2-A2)*1440
5 – phương pháp tính thời gian trong Excel
Tổng số giây giữa hai thời gian:
Để tính số giây giữa 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
6 – phương pháp tính thời gian trong Excel
Note. Đị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 gian để kết quả hiển thị đúng.
Công thức 4. Tính chênh lệch thời gian trong cùng một nhà cung cấp thời gian và bỏ qua các nhà cung cấp khác
Để tính chênh lệch giữa hai thời gian trong cùng một nhà cung cấp, bỏ qua các nhà cung cấp thời gian khác, hãy sử dụng một trong các hàm sau:
Chênh lệch giờ, bỏ qua phút và giây:
=HOUR(B2-A2)
Chênh lệch phút, bỏ qua giờ và giây:
=MINUTE(B2-A2)
Chênh lệch giây, bỏ qua giờ và phút:
=SECOND(B2-A2)
Khi sử dụng hàm HOUR, MINUTE, SECOND, hãy nhớ rằng kết quả không được vượt quá 24 giờ, 60 phút hoặc 60 giây.
7 – phương pháp tính thời gian trong Excel
Note. Nếu thời gian kết thúc to hơn thời gian khởi đầu, cụ thể nếu kết quả là số âm, công thức 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 khởi đầu đến hiện thời
Để tính thời gian đã trôi qua, bạn có thể sử dụng hàm Now và Time. Trường 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 để lấy kết quả ngày tháng và thời gian hiện tại, sau đó trừ đi ngày và thời gian khởi đầu.
Giả sử ô A2 chứa thời điểm khởi đầu, công thức =NOW()-A2 cho kết quả sau với cột B được định dạng thời gian thích hợp, trong ví dụ này nó có dạng h:mm
8 – phương pháp tính thời gian trong Excel
Trong trường hợp khoảng thời gian trôi qua vượt quá 24 giờ, sử dụng một trong các định dạng thời gian sau, ví dụ d ‘days’ h:mm:ss như trong hình sau:
9 – phương pháp tính thời gian trong Excel
Nếu thời gian khởi đầu chỉ có giờ phút mà không có ngày, bạn cần sử dụng hàm Time để tính toán khoảng thời gian trôi đi một cách đúng đắn. Ví dụ, công thức sau trả về khoảng thời gian từ ô A2 đến giờ.
Xem thêm: 3 Món Ăn Dặm Từ Chuối Trộn Sữa Công Thức, Hướng Dẫn Cách Chế Biến Chuối Cho Bé Ăn Dặm
=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) – A2
10- phương pháp tính thời gian trong Excel
Note. Khoảng thời gian trôi qua sẽ không được update theo thời gian thực tiễn, nó chỉ update khi bảng tính được mở lại hoặc tính toán lại. Để update công thức, nhấn Shift + F9 để tính lại trang tính hoặc nhấn F9 để tính lại toàn bộ 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 and XX seconds”
Bạn sử dụng các hàm HOUR, MINUTE và SECOND để lấy kết quả thời gian tương tứng và hàm INT để tính chênh lệch ngày.Sau đó, bạn lồng ghép các hàm vào cùng một công thức cùng các chuỗi text như sau :
=INT(B2-A2) & ” days, ” & HOUR(B2-A2) & ” hours, ” & MINUTE(B2-A2) & ” minutes and ” & SECOND(B2-A2) & ” seconds”
Để ẩn giá trị 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) & ” minutes and “,””) & IF(SECOND(B2-A2)>0, SECOND(B2-A2) & ” seconds”,””)
Ngoài ra, bạn có thể tính toán chênh lệch thời gian bằng cách lấy thời gian khởi đầu trừ thời gian kết thúc (ví dụ = B2-A2) rồi thiết lập định dạng sau cho ô tính:
d “days,” h “hours,” m “minutes and” s “seconds”
11 – phương pháp tính thời gian trong Excel
Phương pháp tính và hiển thị thời gian âm trong Excel
Khi tính toán chênhh lệch thờ gian, bạn có thể bắt gặp lỗi ##### vì độ chênh lệch này âm. Tuy nhiên, cũng có nhiều phương pháp để hiển thị kết quả âm trong Excel.
Vào File > Options > Advanced, kéo thanh cuốn xuống When calculating this workbook và đánh dấu chọn Use 1904 date system.
12 – phương pháp tính thời gian trong Excel
Nhấp OK để lưu setup mới, giá trị âm sẽ được hiển thị như sau:
Phương pháp 2. Tính toán thời gian âm bằng công thức
Nếu không muốn sử dụng phương pháp 1, bạn có thể sử dụng công thức sau:
=IF(A2-B2andgt;0, A2-B2, “-” & TEXT(ABS(A2-B2),”h:mm”))
=IF(A2-B2andgt;0, A2-B2, TEXT(ABS(A2-B2),”-h:mm”))
Cả hai công thức sẽ xác minh chênh lệch thời gian (A2-B2) có to hơn 0 hay không và nó có hiện kết quả hay không. Nếu chênh lệch nhỏ hơn 0, công thức trước tiên sẽ tính trị tuyệt đối 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 gian âm “-h:: mm”.
Note. Hãy nhớ rằng phương pháp trước tiên cho ra kết quả âm là giá trị số âm, kết quả hàm TEXT luôn là chuỗi văn bản và không thể sử dụng trong phép toán hoặc công thức khác.
Cộng/trừ thời gian trong Excel
Về cơ bản, có 2 cách cộng/trừ thời gian trong Excel:
Sử dụng hàm TIMESử dụng phép toán dựa trên số giờ (24), số phút (1440), số giây (86400) trong một ngày.
Hàm TIME(hour, minute, second) giúp việc tính toán dễ dàng hơn, tuy nhiên nó không cho phép việc cộng trừ hơn 23 giờ, 59 phút hoặc 59 giây. Nếu bạn muốn tính toán khoảng thời gian to 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ờ 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 khởi đầu, bạn muốn thêm 2 giờ 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ờ vào ô A2, nhập công thức sau:
=A2 + (28/24)
Để trừ đi một số giờ, sử dụng công thức tương tự như thay 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ờ to hơn 23, sử dụng công thức trước tiên.
Cách cộng/trừ phút vào thời gian 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ố thứ 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 trong Excel:
Công 20 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, chia số phút cho 1440 – số phút trong một ngày và thêm thời gian khởi đầu vào :
=Start time + (N minutes / 1440)
Để trừ số phút, thay dấu + thành dấu -. 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ụ thể
Để cộng thêm số giây ít hơn 60 vào thời gian cụ thể, sử 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 công thức tương tự nhưng thay dấu + thành dấu -.
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 cho 40 giây: =A2 – TIME(0,0,40)Trừ ô A2 cho 900 giây: =A2 – (900/86400)Cách cộng thời gian trong Excel
Phép cộng Excel thường sử dụng hàm SUM, và ứng dụng định dạng thích 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 muốn cộng chúng với nhau. Bạn viết công thức 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 trường hợp, định dạng thời gian mặc định có thể ổn, nhưng thỉnh thoảng 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. Khi đó, bạn không cần phép tính nào cả, bạn chỉ cần định dạng lại thời gian cho ô tính chứa công thức 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 để mở hộp thoại Format Cells. Chọn Custom từ Category list và gõ một trong các đị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 quả như sau:
Cách cộng qua 24 giờ trong Excel
Để tính toán kết quả hơn 24 giờ, bạn có thể sử dụng công thức SUM, và 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à giây
“hours”, mm “minutes”, ss “seconds”
30 hours, 10 minutes, 20 seconds
d h:mm:ss
1 06:10:20
Ngày, giờ, phút và 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 and 20 seconds
Để xem kết quả định dạng trong trang tính thực, xem hình dưới đây (công thức SUM được nhập vào ô A9 tới ô A13:
=SUM($B$2:$B$4)
Note.
Xem thêm: Công Thức Cảm Ứng Điện Từ – Tổng Hợp Công Thức Tính Từ Trường Vật Lý 11
Định dạng thời gian chỉ ứng dụng cho số dương. Nếu kết quả tính toán là số âm, nghĩa là bạn lấy giá trị nhỏ hơn trừ cho giá trị to hơn, kết quả sẽ hiển thị #####.
Đó là toàn bộ phương pháp để các bạn có thể cách cộng trừ thời gian trong excel. Nếu còn bất kể thắc mắc gì thì đừng ngại ngần cmt xuống dưới để được giải đáp nhanh nhất nhé!
Xem thêm nội dung thuộc thể loại: Công thức
Post navigation
