Trong bài viết này, Học Excel Online sẽ chỉ dẫn những cách làm hữu ích nhằm 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: Hàm tính giờ phút trong excel
Cách tính chênh lệch thời hạn trong ExcelCách tính cùng hiển thị thời hạn â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 to hơn 24 giờ, 60 phút, 60 giây?Cách cùng trừ thời gian to 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ư chúng ta đã biết, thời gian trong Excel thường xuyên được format số thập phân để trông như thể thời gian. Do chúng là số, nên chúng ta có thể cộng trừ thời hạn như các giá trị số khác.
Công thức dễ dàng và đơn giản 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ế gồm thể có khá 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 hạn giữa hai quý giá 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 hạn 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 quý hiếm trong ô A2 và B2, xung quanh chênh lệch ngày tháng nếu như ô tính cất cả cực hiếm ngày tháng với thời gian |
Trong khối hệ thống Excel, thời gian thể hiện ở phần thập phân, các 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. Để góp chúng đưa thông tin có ích hơn, bạn cũng có thể áp dụng định dạng thời hạn với một trong các mã sau đây:
| Mã thời gian | Giải thích |
| h | Số giờ đồng hồ trôi qua, ví dụ 4 |
| h:mm | Số giờ cùng 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, lấy ví dụ như 4:10:20. |
Để áp 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 danh sách và gõ mã thời gian vào vỏ hộp Type.
Bây giờ, để xem bí quyết tính chênh lệch thời hạn và mã thời gian vận động như nỗ lực 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, chúng ta có thể sao chép bí quyết 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 hạn trong mỗi cột:
Chú ý. Nếu thời gian chênh lệch được hiển tỉnh thành #####, tức thị ô chứa cách làm không đầy đủ rộng để hiển thị hết thời gian hoặc hiệu quả phép tính là một trong 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ờ thân hai thời gian: =TEXT(B2-A2, “h”)Tính toán chênh lệch giờ cùng phút giữa 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. Các 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 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 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 giữa 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ờ thân hai thời gian
=(End time – Start time) * 24
Giả sử thời gian bước đầu trong ô A2 cùng 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 là B2-A2 để đo lường chênh lệch thời gian, tiếp đến nhân với 24.
=(B2-A2) * 24
Để gồm số Complete hours, áp dụng hàm INT để làm tròn mang lại 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 tác dụng âm hoặc dương, tác dụng âm xảy ra khi thời gian xong xuôi lớn hơn thời gian bắt đầu như mặt 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ụ, bí quyết sẽ như sau:
=(B2-A2)* 86400
Chú ý. Định dạng General nên được tùy chỉnh 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 hạn trong thuộc một đơn vị thời gian và bỏ qua những đơn vị khác
Để tính chênh lệch giữa hai thời hạn trong thuộc một solo vị, vứt qua những đơn vị thời gian khác, hãy sử dụng một trong những hàm sau:
Chênh lệch giờ, bỏ qua phút cùng giây:
=HOUR(B2-A2)
Chênh lệch phút, bỏ lỡ giờ cùng giây:
=MINUTE(B2-A2)
Chênh lệch giây, làm lơ giờ với phút:
=SECOND(B2-A2)
Khi thực hiện hàm HOUR, MINUTE, SECOND, hãy ghi nhớ rằng công dụng không được vượt vượt 24 giờ, 60 phút hoặc 60 giây.
Chú ý. Nếu thời gian chấm dứt lớn hơn thời hạn bắt đầu, ví dụ nếu kết quả là số âm, bí quyết 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 ban đầ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 cùng Time. Trường vừa lòng 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 nhằm lấy công dụng ngày tháng và thời hạn hiện tại, tiếp nối trừ đi ngày và thời gian bắt đầu.
Giả sử ô A2 chứa thời điểm bắt đầu, cách làm =NOW()-A2 cho công dụng sau với cột B được định dạng thời hạn phù hợp, trong lấy ví dụ như này nó gồm dạng h:mm
Trong trường thích hợp khoảng thời hạn trôi qua vượt quá 24 giờ, áp dụng một trong những định dạng thời gian sau, ví dụ như d ‘days’ h:mm:ss như vào hình sau:
Nếu thời gian bắt đầu chỉ tất cả giờ phút mà không có ngày, bạn cần sử dụng hàm Time để đo lường và tính toán khoảng thời hạn trôi đi một cách chủ yếu xác. Ví dụ, công thức sau trả về khoảng thời gian từ ô A2 mang lại giờ.
=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) – A2
Chú ý. Khoảng thời hạn trôi qua sẽ không được update theo thời hạn thực tế, nó chỉ cập nhật khi bảng tính được lật lại hoặc thống kê giám sát lại. Để cập nhật công thức, dìm Shift + F9 để tính lại trang tính hoặc dìm F9 để tính lại tổng thể 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 and XX seconds”
Bạn sử dụng những hàm HOUR, MINUTE và SECOND nhằm lấy kết quả thời gian tương tứng với 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 công thức cùng những chuỗi text như sau :
=INT(B2-A2) & ” days, ” và HOUR(B2-A2) và ” hours, ” và MINUTE(B2-A2) & ” minutes và ” và SECOND(B2-A2) và ” seconds”
Để ẩn cực hiếm 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) & ” 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, bạn 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 hoàn thành (ví dụ = B2-A2) rồi cấu hình thiết lập định dạng sau cho ô 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 giám sát và đo lường chênhh lệch cúng gian, bạn có thể bắt gặp mặt lỗi ##### vì độ chênh lệch này âm. Tuy nhiên, cũng có nhiều cách để hiển thị kết quả âm vào Excel.
Phương pháp 1. đổi khác hệ thông tháng ngày 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 cài đặt mới, quý giá â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ằng công thức
Nếu không muốn sử dụng cách thức 1, chúng ta có thể sử dụng phương pháp 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ách làm sẽ bình chọn 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 kết quả hay không. Giả dụ chênh lệch nhỏ nhiều hơn 0, công thức đầu tiên sẽ tính trị hoàn hảo và tuyệt vời nhất rồi thêm dấu trừ. Công thức thứ nhị cho kết quả tương tự bằng định dạng thời gian âm “-h:: mm”.
Chú ý. Hãy nhớ rằng phương pháp đầu tiên đến ra kết quả âm là quý giá số âm, hiệu quả hàm TEXT luôn là chuỗi văn bạn dạng và ko thể sử dụng trong phép toán hoặc cách làm khác.
Xem thêm: Hướng Dẫn Cách Đăng Ký Làm Tài Xế Grabbike Chi Tiết, Hướng Dẫn Đăng Ký Tài Xế Grabbike
Cộng/trừ thời hạn trong Excel
Về cơ bản, bao gồm 2 biện pháp cộng/trừ thời hạn 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) trong một ngày.Hàm TIME(hour, minute, second) giúp việc tính toán dễ ợt hơn, mặc dù nó không được 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 hạn 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ờ đồng hồ vào thời hạn cụ thể
Hàm thời hạn để 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 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 để 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 bí quyết sau:
=A2 + (28/24)
Để trừ đi một vài giờ, thực hiện công thức tựa như như cố gắng dấu + bằng dấu –
Ví dụ, trừ ô A2 mang đế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 chúng ta trừ số giờ to 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 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à 1 trong những vài công thức thực tế để tính số phút vào Excel:
Công đôi mươi phút vào A2: =A2 + TIME(0,20,0)
Trừ A2 đến 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 tách số phút mang lại 1440 – số phút vào một ngày với thêm thời gian bước đầu vào :
=Start time + (N minutes / 1440)
Để trừ số phút, cố gắng 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ể
Để thêm vào đó số giây thấp 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 lớn hơn 59, áp dụng 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ư nhưng chũm 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 mang đến 40 giây: =A2 – TIME(0,0,40)Trừ ô A2 đến 900 giây: =A2 – (900/86400)Cách cộng thời hạn trong ExcelPhép cùng Excel thường áp dụng hàm SUM, và vận dụng định dạng tương xứng cho kết quả.
Giả sử chúng ta có một vài thời hạn trong cột B và bạn muốn cộng bọn chúng với nhau. Bạn viết bí quyết SUM như sau =SUM(B2:B4), hiệu quả 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, tuy thế đôi khi bạn có nhu cầu hiển thị tổng thời hạn dưới dạng phút cùng giây, hoặc chỉ hiển thị giây. Khi đó, bạn không yêu cầu phép tính làm sao cả, bạn chỉ việc định dạng lại thời gian cho ô tính chứa phương pháp SUM.
Nhấp đề nghị vào ô tính và lựa chọn Format Cells từ danh sách 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 cùng giây:
Hiển thị thời hạn dưới dạng giây:
Kết đúng 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 một trong các đị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 cùng giây | |
| 30 hours, 10 minutes, đôi mươi seconds | ||
| d h:mm:ss | 1 06:10:20 | Ngày, giờ, phút cùng 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 kết quả định dạng vào trang tính thực, coi hình tiếp sau đâ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 gian 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ị nhỏ tuổi hơn trừ cho giá trị lớn hơn, hiệu quả sẽ hiển thị #####.
Cách hiển thị khoảng thời gian lớn hơn 24 giờ, 60 phút, 60 giây?
Để hiện thị lên một khoảng chừng thời gian to hơn 24 giờ, 60 phút, 60 giây, hãy sử dụng custom time format nơi cơ mà mã 1-1 vị thời gian tương ứng phía bên trong dấu ngoặc vuông, chẳng hạn như
Lớn rộng 24 giờ:
Lớn rộng 60 phút:
Lớn rộng 60 giây:
Ảnh chụp màn hình dưới đây cho thấy thêm định dạng thời gian “lớn rộng 24 giờ” trên thực tế:
Dưới đây là một vài định hình khác. Chúng ta cũng có thể sử dụng nó nhằm hiển thị những khoảng chừng thời gian to hơn chiều dài 1-1 vị thời gian chuẩn.
| 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 cùng giây | |
| Tổng số giây |
Khi được sử dụng cho dữ liệu mẫu của bọn họ (Thời gian tổng cộng 50:40 trong hình ảnh chụp màn hình trên), mọi định dạng thời hạn này vẫn cho kết quả như sau:
| A | B | C | |
| 1 | Miêu tả | Thời gian hiển thị | Định dạng |
| 2 | Số giờ | 50 | |
| 3 | Số giờ và phút | 50:40 | |
| 4 | Số giờ, phút, giây | 50:40:30 | |
| 5 | Số phút | 3040 | |
| 6 | Sốphút với giây | 3040:30 | |
| 7 | Số giây | 182430 |
Để phần nhiều khoảng thời gian được hiển thị ở trên dễ nắm bắt hơn so với người dùng, bạn cũng có thể bổ sung 1-1 vị thời hạn bằng các từ viết tắt tương ứng, ví dụ:
| A | B | C | |
| 1 | Miêu tả | Thời gian hiển thị | Định dạng |
| 2 | Số giờ và phút | 50 giờ cùng 40 phút | |
| 3 | Số giờ, phút, giây | 50h.40m.30s | |
| 4 | Số phút | 3040 phút | |
| 5 | Số phút với giây | 3040 phút cùng 30 giây | |
| 6 | Số giây | 182430 giây |
Lưu ý. Mặc dù đầy đủ khoảng thời gian trên trông hệt như chuỗi ký tự, dẫu vậy chúng vẫn luôn là giá trị số, chính vì định dạng chữ số trong Excel chỉ thay đổi về bề ngoài chứ bản chất không ráng đổi. Bởi thế, bạn cũng có thể tự vày cộng tốt trừ đầy đủ khoảng thời hạn đã được format một phương pháp bình thường, hãy tham chiếu bọn chúng trong phương pháp và sử dụng trong những phép tính khác.
Cách cùng trừ thời gian lớn hơn 24 giờ, 60 phút, 60 giây
Để cùng khoảng thời gian mong mong mỏi cho một khoảng thời gian cho sẵn, hãy đem số giờ, phút, xuất xắc giây mà bạn muốn cộng cho đơn vị thời hạn tương ứng vào một ngày (24 giờ, 1440 phút, 86400 giây), rồi cùng số yêu đương cho thời hạn bắt đầu.
Cộng rộng 24 giờ.Thời gian bắt đầu + (N/24)
Cộng hơn 60 phút.Thời gian bước đầu + (N/1440)
Cộng hơn 60 giây.Thời gian ban đầu + (N/86400)
Trong đó, N là số giờ, phút, giỏi giây mà bạn có nhu cầu cộng.
Đây là một trong những vài ví dụ phương pháp trên thực tế:
Để cùng 45 giờ mang lại thời gian bắt đầu trong ô A2:
=A2+(45/24)
Để cộng 100 phút đến thời gian bước đầu trong ô A2:
=A2+(100/1440)
Để cùng 200 giây cho thời gian bắt đầu trong ô A2:
=A2+(200/86400)
Hay là, bạn cũng có thể nhập khoảng thời hạn cần cộng vào trong 1 ô đơn lẻ rồi tham chiếu ô kia trong công thức tất cả dạng như trong ảnh chụp màn hình dưới đây:
Để trừ khoảng thời gian trong Excel, hãy áp dụng công thức tựa như nhưng hãy nạm dấu cộng bằng dấu trừ nhé!
Trừ hơn 24 giờThời gian bắt đầu – (N/24)
Trừ hơn 60 phút.Thời gian bước đầu – (N/1440)
Trừ rộng 60 giâyThời gian bắt đầu – (N/86400)
Ảnh chụp màn hình tiếp sau đây hiển thị những kết quả:
Lưu ý:
Nếu khoảng thời hạn được đo lường và thống kê có dạng số thập phân, hãy sử dụng định dạng thời gian/ngày nhằm định dạng ô.Nếu sau khoản thời gian sử dụng định dạng với ô hiển thị #####, thì thông thường ô không đủ rộng để hiển thị giá trị thời hạn trong ngày. Để xử lý điều này, hãy không ngừng mở rộng chiều nhiều năm ô bằng cách nhấp đúp loài chuột hay kéo size bên yêu cầu của cột.Đó là toàn bộ cách để các chúng ta cũng có thể cách cùng trừ thời gian trong excel. Ví như còn bất kể thắc mắc gì thì chớ ngại ngần cmt xuống dưới sẽ được giải đáp sớm nhất có thể nhé!
