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