Trong bài viết này, Học Excel Online sẽ trả lời những phương pháp 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 hạn trong excel.
Bạn đang xem: Công thức tính giờ trong excel
Cách tính chênh lệch thời hạn trong ExcelCách tính và hiển thị thời gian âm vào ExcelCộng/trừ thời hạn trong ExcelCách cộng thời gian trong ExcelCách hiển thị khoảng thời gian lớn hơn 24 giờ, 60 phút, 60 giây?Cách cùng trừ thời gian lớn hơn 24 giờ, 60 phút, 60 giây
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 hay được định dạng số thập phân nhằm trông tương tự thời gian. Bởi vì chúng là số, nên bạn cũng có thể cộng trừ thời hạn như các giá trị số khác.Công thức đơn giản dễ dàng nhất để tính chênh lệch thời hạn là:
=End time – Start time
Tùy vào kết cấu dữ liệu, chênh lệch thời gian thực tế có thể có không ít dạng không giống nhau, ví dụ:
| Công thức | Giải thích |
| =A2-B2 | Tính toán chênh lệch thời hạn giữa hai quý hiếm trong ô A2 với 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 cực hiếm trong ô A2 cùng B2, kế bên chênh lệch ngày tháng ví như ô tính đựng cả quý hiếm ngày tháng với thời gian |
Trong hệ thống Excel, thời gian bộc lộ ở phần thập phân, bạn sẽ có công 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 thông tin có ích hơn, chúng ta có thể áp dụng định dạng thời gian với một trong số 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ờ cùng số phút trôi qua, lấy ví dụ 4:10 |
| h:mm:ss | Số giờ, số phút và số giây trôi qua, ví dụ như 4:10:20. |
Để vận dụng định dạng thời gian, nhấn Ctrl + 1 để mở hộp thoại Format Cells, chọn Custom từ Category các mục và gõ mã thời hạn vào vỏ hộp Type.
Bây giờ, nhằm xem phương pháp tính chênh lệch thời gian và mã thời gian hoạt động như nạm nào trong bảng tính thực sự. Cùng với Start time vào cột A với End time vào cột B, chúng ta có thể sao chép cách làm 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 vào định dạng thời gian trong mỗi cột:
Chú ý. Nếu thời hạn chênh lệch được hiển thị thành #####, tức thị ô chứa bí quyết không đầy đủ rộng nhằm hiển thị hết thời gian 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 hạn bằng hàm TEXT
Một nghệ thuật khác nhằm tính chênh lệch thời gian trong Excel là áp dụng hàm TEXT .
giám sát chênh lệch giờ thân hai thời gian: =TEXT(B2-A2, “h”) giám sát và đo lường chênh lệch giờ với 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”)
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 vào hình trên. Trong vài trường hợp nhất định, điều này rất có thể là một hạn chế vì các bạn sẽ không thể sử dụng tác dụng này để tiếp tục 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ị chức năng (giờ, phút, giây), các 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 ban đầu trong ô A2 và thời gian xong trong ô B2, bạn hãy sử dụng phép tính đơn giản dễ dàng là B2-A2 để giám sát chênh lệch thời gian, kế tiếp nhân cùng với 24.
=(B2-A2) * 24
Để có số Complete hours, thực hiện hàm INT để có tác dụng tròn đến 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 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 hoàn toàn có thể cho hiệu quả âm hoặc dương, tác dụng âm xảy ra khi thời gian dứt lớn rộng thời gian bắt đầ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 hai 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ư 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 phương pháp tính chênh lệch thời gian để công dụng hiển thị đúng.
Công thức 4. Tính chênh lệch thời gian trong thuộc một 1-1 vị thời gian và vứt qua các đơn vị không giống
Để tính chênh lệch thân hai thời hạn trong thuộc một 1-1 vị, quăng quật qua các đơn vị thời hạn khác, hãy sử dụng một trong những hàm sau:
Chênh lệch giờ, làm lơ phút và giây:
=HOUR(B2-A2)
Chênh lệch phút, bỏ qua giờ với giây:
=MINUTE(B2-A2)
Chênh lệch giây, bỏ lỡ giờ cùng phút:
=SECOND(B2-A2)
Khi thực hiện hàm HOUR, MINUTE, SECOND, hãy lưu giữ rằng công dụng không được vượt thừa 24 giờ, 60 phút hoặc 60 giây.
Chú ý. Nếu thời gian ngừng lớn hơn thời gian bắt đầu, cụ thể nếu tác dụng là số âm, phương pháp 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, chúng ta 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 kết 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 điểm bắt đầu, phương pháp =NOW()-A2 cho công dụng sau với cột B được định dạng thời gian phù hợp, trong ví dụ như này nó có dạng h:mm
Trong trường thích hợp khoảng thời gian trôi qua vượt quá 24 giờ, thực hiện một trong các định dạng thời hạn sau, ví dụ d ‘days’ h:mm:ss như vào hình sau:
Nếu thời gian bắt đầu chỉ bao gồm 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 thiết yếu xác. Ví dụ, phương pháp sau trả về khoảng thời gian từ ô A2 đến giờ.
=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) – A2
Chú ý. Khoảng thời hạn trôi qua sẽ không còn được update theo thời gian thực tế, nó chỉ cập nhật khi bảng tính được mở ra hoặc giám sát và đo lường lại. Để update công thức, dìm 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 hạn “XX days, XX hours, XX minutes & XX seconds”
Bạn sử dụng các hàm HOUR, MINUTE và SECOND nhằm lấy hiệu quả thời gian tương tứng với hàm INT để tính chênh lệch ngày.Sau đó, chúng ta lồng ghép những hàm vào thuộc một bí quyết cùng những chuỗi text như sau :
=INT(B2-A2) và ” days, ” và HOUR(B2-A2) & ” hours, ” và MINUTE(B2-A2) & ” 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) & ” days, “,””) & IF(HOUR(B2-A2)>0, HOUR(B2-A2) & ” hours, “,””) và IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) & ” minutes và “,””) & IF(SECOND(B2-A2)>0, SECOND(B2-A2) và ” seconds”,””)
Ngoài ra, chúng ta cũng có thể tính toán chênh lệch thời gian bằng phương pháp lấy thời gian ban đầu trừ thời gian chấm dứt (ví dụ = B2-A2) rồi tùy chỉnh thiết lập định dạng sau mang đế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 trong Excel
Khi giám sát chênhh lệch thờ 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ị tác dụng âm trong Excel.
Xem thêm: 37+ Bài Luyện Nói Tiếng Anh Cơ Bản Xứ, 17 Cách Luyện Nói Tiếng Anh Đơn Giản Và Hiệu Quả
Phương pháp 1. Chuyể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ý hiếm âm sẽ được hiển thị như sau:
Phương pháp 2. Thống kê giám sát thời gian âm bởi công thức
Nếu không thích sử dụng cách thức 1, bạn 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ả hai công thức sẽ kiểm tra chênh lệch thời gian (A2-B2) có lớn hơn 0 hay là không và nó có hiện công dụng hay không. Trường hợp chênh lệch nhỏ thêm hơn 0, công thức thứ nhất sẽ tính trị hoàn hảo và tuyệt vời nhất rồi thêm vết trừ. Phương pháp thứ nhị cho kết quả tương tự bởi định dạng thời hạn âm “-h:: mm”.
Chú ý. Hãy nhớ rằng cách thức đầu tiên mang lại ra công dụng âm là giá trị số âm, tác dụng hàm TEXT luôn luôn là chuỗi văn bản và không thể thực hiện 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, tất cả 2 bí quyết cộng/trừ thời hạn trong Excel:
sử dụng hàm TIME sử 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 vấn đề tính toán dễ dàng hơn, tuy nhiên nó không cho phép việc cộng trừ rộng 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 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ừ giờ 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ụ, trong ô A2 là thời hạn bắt đầu, bạn muốn thêm 2 giờ vào đó, cách làm sẽ là:
=A2 + TIME(2, 0, 0)
Công thức nhằm cộng bất kỳ số giờ như thế nào (dưới/trên 24 giờ)
=Start time + (N hours/24)
Ví dụ, để thêm vào đó 28 giờ đồng hồ vào ô A2, nhập phương pháp sau:
=A2 + (28/24)
Để trừ đi một số giờ, sử dụng công thức tựa như như nuố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 các bạn trừ số giờ to hơn 23, sử 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 có nhu cầu vào tham số máy 2:
=Start time + TIME(0, N minutes, 0)
Đây là 1 trong những vài công thức thực tiễn để tính số phút trong Excel:
Công trăng tròn phút vào A2: =A2 + TIME(0,20,0)
Trừ A2 cho 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 bạn, phân chia số phút đến 1440 – số phút trong một ngày với thêm thời gian ban đầu vào :
=Start time + (N minutes / 1440)
Để trừ số phút, nạ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 gian cụ thể
Để thêm vào đó số giây ít hơn 60 vào thời gian cụ thể, thực hiện hàm TIME:
=Start time + TIME(0, 0, N seconds)
Để thêm vào đó 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 cầm dấu + thành vế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 mang lại 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 ExcelPhép cộng Excel thường sử dụng hàm SUM, và áp dụng định dạng cân xứng 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ách làm SUM như sau =SUM(B2:B4), công dụng 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 hạn mặc định hoàn toàn có thể ổn, tuy nhiên đôi khi bạn có nhu cầu hiển thị tổng thời hạn dưới dạng phút với giây, hoặc chỉ hiển thị giây. Lúc đó, bạn không đề nghị 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 bí quyết SUM.
Nhấp đề nghị vào ô tính và lựa chọn Format Cells từ danh sách tùy chọn, thừa nhận Ctrl + 1 để mở vỏ hộp thoại Format Cells. Lựa chọn Custom từ Category các 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:
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ũng có thể sử dụng cách làm SUM, và chọn 1 trong những định dạng sau:
| Định dạng | Hiển thị | Giải thích |
| 30:10 | Giờ cùng phút | |
| 30:10:20 | Giờ, phút với giây | |
| 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 và 20 seconds |
Để xem công dụng định dạng trong trang tính thực, coi hình dưới đây (công thức SUM được nhập vào ô A9 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 kết quả tính toán là số âm, nghĩa là bạn lấy giá chỉ trị nhỏ dại hơn trừ đến giá trị to hơn, tác dụng sẽ hiển thị #####.
Cách hiển thị khoảng tầm thời gian to hơn 24 giờ, 60 phút, 60 giây?
Để hiện lên một khoảng chừng thời gian lớn hơn 24 giờ, 60 phút, 60 giây, hãy sử dụng custom time format nơi nhưng mà mã 1-1 vị thời gian tương ứng phía trong dấu ngoặc vuông, ví dụ điển hình như| Miêu tả | Mã định dạng |
| Tổng số giờ | |
| Số giờ với phút | |
| Số giờ, phút, giây | |
| Tổng số phút | |
| Số phút và giây | |
| Tổng số giây |
| A | B | C | |
| 1 | Miêu tả | Thời gian hiển thị | Định dạng |
| 2 | Số giờ | 50 | |
| 3 | Số giờ cùng phút | 50:40 | |
| 4 | Số giờ, phút, giây | 50:40:30 | |
| 5 | Số phút | 3040 | |
| 6 | Sốphút cùng giây | 3040:30 | |
| 7 | Số giây | 182430 |
| A | B | C | |
| 1 | Miêu tả | Thời gian hiển thị | Định dạng |
| 2 | Số giờ và phút | 50 giờ với 40 phút | |
| 3 | Số giờ, phút, giây | 50h.40m.30s | |
| 4 | Số phút | 3040 phút | |
| 5 | Số phút cùng giây | 3040 phút cùng 30 giây | |
| 6 | Số giây | 182430 giây |
