Trong bài viết này, Học Excel Online sẽ khuyên bảo 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 cộng trừ thời gian trong excel.
Bạn đang xem: Tính số giờ trong excel
Cách tính chênh lệch thời gian trong ExcelCách tính cùng hiển thị thời hạn âm trong ExcelCộng/trừ thời gian trong ExcelCách cộng thời hạn trong ExcelCách hiển thị khoảng chừ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 hạn trong Excel
Công thức 1. Trừ thời gian
Như chúng ta đã biết, thời hạn trong Excel hay được format số thập phân nhằm trông giống như thời gian. Do chúng là số, nên chúng ta cũng có thể cộng trừ thời hạn như những giá trị số khác.
Công thức đơn giản và 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 gian thực tế có thể có nhiều 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 gian giữa hai cực 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 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 quý giá trong ô A2 cùng B2, ngoại trừ chênh lệch ngày tháng ví như ô tính chứa cả quý hiếm ngày tháng với thời gian |
Trong hệ thống Excel, thời gian biểu thị ở phần thập phân, bạn sẽ có hiệu quả như sau:
Phần thập phân vào cột D đúng nhưng không tồn tại ý nghĩa. Để gó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ố giờ trôi qua, lấy 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 cùng số giây trôi qua, lấy ví dụ như 4:10:20. |
Để áp dụng định dạng thời gian, nhận Ctrl + 1 để mở vỏ hộp thoại Format Cells, chọn Custom từ Category menu và gõ mã thời hạn vào hộp Type.
Bây giờ, nhằm xem cách làm tính chênh lệch thời gian và mã thời gian vận động như cầm cố nào vào bảng tính thực sự. Với Start time vào cột A và End time vào cột B, bạn có thể sao chép bí quyết 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 hạn 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 hạn hoặc tác dụng 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 hạn bằng hàm TEXT
Một kỹ thuật khác nhằm tính chênh lệch thời hạn trong Excel là sử 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ờ 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ới 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 là văn bản. Những giá trị text được căn lề trài trong các cột C, D, E vào hình trên. Vào vài trường hợp nhất định, điều này rất có thể là một tinh giảm vì các bạn sẽ không thể sử dụng kết quả này để liên 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 thân hai thời hạn bằng một đơn vị chức năng (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 bước đầu trong ô A2 và thời gian xong xuôi trong ô B2, 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ối nhân cùng với 24.
=(B2-A2) * 24
Để có số Complete hours, sử dụng hàm INT để làm cho 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 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 rất có thể cho công dụng âm hoặc dương, tác dụng âm xẩy ra khi thời gian hoàn thành lớn hơn thời gian bắt đầu như 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ụ, phương pháp sẽ như sau:
=(B2-A2)* 86400
Chú ý. Định dạng General nên được tùy chỉnh cho ô tính chứa bí quyết 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 cùng một 1-1 vị thời hạn và vứt qua những đơn vị khác
Để tính chênh lệch thân hai thời gian trong thuộc một đối kháng vị, bỏ qua những đơn vị thời gian khác, hãy sử dụng một trong các hàm sau:
Chênh lệch giờ, bỏ lỡ phút và giây:
=HOUR(B2-A2)
Chênh lệch phút, bỏ lỡ giờ với giây:
=MINUTE(B2-A2)
Chênh lệch giây, làm lơ giờ cùng phút:
=SECOND(B2-A2)
Khi áp 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.
Chú ý. Nếu thời gian dứt lớn hơn thời hạn bắt đầu, cụ thể nếu công dụng 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 tính từ lúc thời gian bước đầ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 đúng theo khoảng thời hạn trôi qua dưới 24 giờ, bạn chỉ cần áp dụng hàm NOW để lấy hiệu quả ngày tháng và thời hạn hiện tại, sau đó 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ách làm =NOW()-A2 cho tác dụng sau cùng 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 phù hợp khoảng thời gian trôi qua vượt thừa 24 giờ, áp dụng một trong các định dạng thời hạn sau, ví dụ như d ‘days’ h:mm:ss như trong hình sau:
Nếu thời gian ban đầu chỉ gồm giờ phút mà không tồn tại ngày, bạn phải sử dụng hàm Time để tính toán khoảng thời hạn trôi đi một cách chính xác. Ví dụ, bí quyết 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 cập nhật theo thời gian thực tế, nó chỉ update khi bảng tính được mở lại hoặc 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ấn 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 gian “XX days, XX hours, XX minutes & XX seconds”
Bạn sử dụng những hàm HOUR, MINUTE và SECOND để lấy kết quả thời gian tương tứng cùng 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 những chuỗi text như sau :
=INT(B2-A2) & ” days, ” & HOUR(B2-A2) & ” hours, ” & MINUTE(B2-A2) & ” minutes & ” & SECOND(B2-A2) và ” seconds”
Để ẩn cực hiếm 0, lồng bốn 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, “,””) và IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) & ” minutes & “,””) & 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 hoàn thành (ví dụ = B2-A2) rồi tùy chỉnh 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 chênhh lệch bái gian, bạn cũng có thể bắt gặp gỡ lỗi ##### vì độ chênh lệch này âm. Mặc dù nhiên, cũng đều có nhiều cách để hiển thị kết quả âm trong Excel.
Phương pháp 1. Chuyển đổi 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à lưu lại chọn Use 1904 date system.
Nhấp OK để lưu thiết đặt mới, giá trị âm sẽ tiến hành hiển thị như sau:
Phương pháp 2. đo lường và thống kê thời gian âm bằng 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 phương pháp 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ông thức 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ó có hiện kết quả hay không. Ví như chênh lệch bé hơn 0, công thức đầu tiên sẽ tính trị hoàn hảo rồi thêm vệt trừ. Cách làm thứ nhì cho kết quả tương tự bởi định dạng thời gian âm “-h:: mm”.
Chú ý. Hãy ghi nhớ rằng phương thức đầu tiên mang đến ra tác dụng âm là giá trị số âm, kết 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: Thông Số Kỹ Thuật Sky A850 ( Vega R3 ), Điện Thoại Sky A850 Vega R3
Cộng/trừ thời hạn trong Excel
Về cơ bản, tất cả 2 giải 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ờ (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 thuận lợi hơn, tuy nhiên 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 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 bên dưới đây.
Tham khảo ngay: Giáo trình Excel 2020
Cộng/trừ tiếng vào thời hạn cụ thể
Hàm thời hạn để cộng thêm dưới 24 giờ
=Start time + TIME(N hours, 0, 0)
Ví dụ, vào ô A2 là thời gian bắt đầu, bạn muốn thêm 2 tiếng vào đó, phương pháp 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ụ, để cộng thêm 28 giờ vào ô A2, nhập cách làm sau:
=A2 + (28/24)
Để trừ đi một trong những giờ, thực hiện công thức tương tự như như thay dấu + bằng dấu –
Ví dụ, trừ ô A2 mang đến 3 giờ, nhập một trong số công thức sau:
=A2-(3/24)
=A2-TIME(3,0,0)
Nếu các 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 có nhu cầu vào tham số vật dụng 2:
=Start time + TIME(0, N minutes, 0)
Đây là một trong vài công thức thực tế để tính số phút trong Excel:
Công 20 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ừ bên trên 60 phút
Trong phép tính của bạn, phân tách số phút đến 1440 – số phút vào một ngày và thêm thời gian bắt đầ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 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 to hơn 59, sử 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 vắt 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 đến 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 ExcelPhép cộng Excel thường sử 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 bọn chúng với nhau. Chúng ta viết phương pháp SUM như sau =SUM(B2:B4), công dụng hiển thị theo format 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, cơ mà đôi khi bạn có nhu cầu hiển thị tổng thời gian dưới dạng phút với giây, hoặc chỉ hiển thị giây. Khi đó, bạn không đề nghị 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 phương pháp SUM.
Nhấp bắt buộc vào ô tính và chọn Format Cells từ list tùy chọn, dấn Ctrl + 1 để mở hộp thoại Format Cells. Lựa chọn Custom từ Category menu và gõ một trong những định dạng sau vào hộp Type:
Hiển thị thời gian dưới dạng phút cùng giây:
Hiển thị thời gian dưới dạng giây:
Kết quả thật sau:
Cách cùng qua 24 giờ trong Excel
Để tính toán tác dụng hơn 24 giờ, bạn có thể sử dụng cách làm SUM, và lựa chọn 1 trong những định dạng sau:
| Định dạng | Hiển thị | Giải thích |
| 30:10 | Giờ và phút | |
| 30:10:20 | Giờ, phút cùng giây | |
| 30 hours, 10 minutes, 20 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 and 20 seconds |
Để xem hiệu 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)
Chú ý. Định dạng thời gian chỉ vận dụng cho số dương. Nếu tác dụng tính toán là số âm, nghĩa là chúng ta lấy giá bán trị nhỏ dại hơn trừ đến giá trị khủng hơn, công dụng sẽ hiển thị #####.
Cách hiển thị khoảng thời gian to hơn 24 giờ, 60 phút, 60 giây?
Để hiện thị một khoảng tầm 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 mà mã đơn vị thời hạn tương ứng bên trong dấu ngoặc vuông, chẳng hạn như
Lớn hơn 24 giờ:
Lớn hơn 60 phút:
Lớn hơn 60 giây:
Ảnh chụp screen dưới đây cho thấy thêm định dạng thời gian “lớn hơn 24 giờ” trên thực tế:
Dưới đó là một vài định hình khác. Bạn 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 đối kháng vị thời gian chuẩn.
| Miêu tả | Mã định dạng |
| Tổng số giờ | |
| Số giờ và phút | |
| Số giờ, phút, giây | |
| Tổng số phút | |
| Số phút với giây | |
| Tổng số giây |
Khi được áp dụng cho tài liệu mẫu của họ (Thời gian tổng cộng 50:40 trong ảnh chụp screen trên), mọi định dạng thời hạn này vẫn cho tác dụng 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à giây | 3040:30 | |
| 7 | Số giây | 182430 |
Để phần đa khoảng thời gian được hiển thị sinh hoạt trên dễ hiểu hơn so với người dùng, bạn 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ới 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 và giây | 3040 phút và 30 giây | |
| 6 | Số giây | 182430 giây |
Lưu ý. Mặc dù phần đa khoảng thời gian trên trông y hệt như chuỗi ký kết tự, cơ mà chúng vẫn chính là giá trị số, cũng chính vì định dạng văn bản số vào Excel chỉ đổi khác về bề ngoài chứ bản chất không rứa đổi. Vì thế, chúng ta có thể tự vày cộng giỏi trừ hầu như khoảng thời hạn đã được định dạng một giải pháp bình thường, hãy tham chiếu chúng trong cách làm và sử dụng trong số 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 muốn cho một khoảng thời gian cho sẵn, hãy đem số giờ, phút, tốt giây mà bạn muốn cộng cho đơn vị thời hạn tương ứng trong một ngày (24 giờ, 1440 phút, 86400 giây), rồi cộng số mến cho thời hạn bắt đầu.
Cộng rộng 24 giờ.Thời gian bắt đầu + (N/24)
Cộng rộng 60 phút.Thời gian ban đầu + (N/1440)
Cộng rộng 60 giây.Thời gian ban đầu + (N/86400)
Trong đó, N là số giờ, phút, tốt giây mà bạn có nhu cầu cộng.
Đây là một vài ví dụ bí quyết trên thực tế:
Để cộng 45 giờ đến thời gian ban đầu trong ô A2:
=A2+(45/24)
Để cộng 100 phút mang lại thời gian bước đầu trong ô A2:
=A2+(100/1440)
Để cộng 200 giây đến thời gian ban đầu trong ô A2:
=A2+(200/86400)
Hay là, bạn có thể nhập khoảng thời hạn cần cộng vào một ô lẻ tẻ rồi tham chiếu ô kia trong công thức gồm dạng như trong ảnh chụp màn hình dưới đây:
Để trừ khoảng thời hạn trong Excel, hãy sử dụng công thức tựa như nhưng hãy nỗ lực dấu cộng bằng dấu trừ nhé!
Trừ hơn 24 giờThời gian ban đầu – (N/24)
Trừ hơn 60 phút.Thời gian ban đầu – (N/1440)
Trừ hơn 60 giâyThời gian bắt đầu – (N/86400)
Ảnh chụp màn hình dưới đây hiển thị các kết quả:
Lưu ý:
Nếu khoảng thời gian được giám sát có dạng số thập phân, hãy áp dụng định dạng thời gian/ngày để định dạng ô.Nếu sau thời điểm sử dụng định dạng với ô hiển thị #####, thì thường thì ô không được rộng nhằm hiển thị giá chỉ 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 khung bên buộc phải của cột.Đó là toàn bộ phương pháp để các bạn có thể cách cộng trừ thời hạn trong excel. Trường hợp còn bất cứ thắc mắc gì thì đừng ngại ngần cmt xuống dưới để được giải đáp sớm nhất nhé!
