Trong nội dung bài viết này, Học Excel Online sẽ chỉ dẫn phần nhiều cách làm có lợi nhằm cộng/trừ thời hạn, chênh lệch thời gian, đếm giờ…
Cách tính chênh lệch thời gian vào Excel
Công thức 1. Trừ thời gian
Nhỏng các bạn đang biết, thời gian vào Excel thường xuyên được định hình số thập phân nhằm trông tương đương thời gian. Vì bọn chúng là số, phải chúng ta có thể cộng trừ thời hạn nhỏng những quý giá số khác.Bạn đang xem: Cách tính thời gian
Công thức dễ dàng và đơ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 tài liệu, chênh lệch thời hạn thực tiễn rấ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 tân oán chênh lệch thời hạn thân hai cực hiếm vào ô A2 cùng B2 |
=TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”) | Tính toán thù chênh lệch thời hạn giữa nhì thời hạn thế thể |
=TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) – TIME(HOUR(B2), MINUTE(B2), SECOND(B2)) | Tính tân oán chênh lệch thời hạn giữa hai giá trị trong ô A2 cùng B2, quanh đó chênh lệch tháng ngày trường hợp ô tính chứa cả quý hiếm tháng ngày và thời gian |
Trong hệ thống Excel, thời hạn biểu hiện ở phần thập phân, bạn sẽ bao gồm tác dụng nhỏng sau:
Phần thập phân vào cột D đúng dẫu vậy không tồn tại chân thành và ý nghĩa. Để giúp bọn chúng báo tin có lợi hơn, bạn cũng có thể vận dụng định hình thời hạn với một trong các mã sau đây:
Mã thời gian | Giải thích |
h | Số tiếng trôi qua, ví dụ 4 |
h:mm | Số giờ đồng hồ với số phút ít trôi qua, ví dụ 4:10 |
h:mm:ss | Số giờ đồng hồ, số phút cùng số giây trôi qua, ví dụ 4:10:trăng tròn. Bạn đang xem: Cách trừ giờ phút giây trong excel |
Để vận dụng format thời gian, dìm Ctrl + 1 để mở vỏ hộp thoại Format Cells, lựa chọn Custom trường đoản cú Category danh mục và gõ mã thời gian vào vỏ hộp Type.
Bây giờ, để thấy cách làm tính chênh lệch thời gian cùng mã thời gian chuyển động ra sao vào bảng tính đích thực. Với Start time vào cột A và End time vào cột B, bạn có thể coppy cách làm vào cột C và E:
=$B2-$A2
Thời gian chênh lệch được hiển thị không giống nhau tùy vào định dạng thời hạn trong mỗi cột:
Chú ý. Nếu thời hạn chênh lệch được hiển đô thị #####, tức là ô đựng phương pháp không đủ rộng nhằm hiển thị hết thời hạn hoặc kết quả phnghiền 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 kỹ thuật khác nhằm tính chênh lệch thời gian trong Excel là thực hiện hàm TEXT .
Tính tân oán chênh lệch tiếng thân nhị thời gian: =TEXT(B2-A2, “h”)Tính toán chênh lệch tiếng và phút giữa nhị thời gian: =TEXT(B2-A2, “h:mm”)Tính tân oán chênh lệch giờ đồng hồ, phút ít với giây thân hai thời gian: =TEXT(B2-A2, “h:mm:ss”)Crúc ý.
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 uống lề trài trong các cột C, D, E trong hình bên trên. Trong vài tình huống khăng khăng, vấn đề đó rất có thể là 1 trong hạn chế bởi vì bạn sẽ cấp thiết thực hiện kết quả này nhằm tiếp tục tính tân oán.Công thức 3. Tính giờ, phút, giây giữa nhì thời gian
Để hiển thị chênh lệch thân hai thời gian bằng một đơn vị (tiếng, phút, giây), bạn phải thực hiện những phxay tính sau.
Phnghiền tính giờ đồng hồ thân hai thời gian
=(End time – Start time) * 24
Giả sử thời hạn bước đầu vào ô A2 cùng thời gian xong xuôi trong ô B2, các bạn hãy sử dụng phép tính đơn giản là B2-A2 để tính tân oán chênh lệch thời gian, tiếp đến nhân cùng với 24.
=(B2-A2) * 24
Để gồm số Complete hours, thực hiện hàm INT để triển khai tròn cho số nguyên hần nhất:
=INT((B2-A2) * 24)
Tổng số phút giữa nhị thời gian:
Để tính số phút giữa nhị thời gian, nhân chênh lệch thời hạn cùng với 1440
=(End time – Start time) * 1440
Công thức có thể đến công dụng âm hoặc dương, kết quả âm xẩy ra Khi thời gian xong to hơn thời gian bước đầu nhỏng mặt hàng 5:
=(B2-A2)*1440
Tổng số giây thân nhì thời gian:
Để tính số giây giữa nhì thời gian, nhân chênh lệch thời gian cùng với 86400
=(End time – Start time) * 86400
Trong ví dụ, phương pháp sẽ nhỏng sau:
=(B2-A2)* 86400
Chú ý. Định dạng General yêu cầu đượ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 và một đơn vị thời hạn với làm lơ các đơn vị khác
Để tính chênh lệch thân hai thời gian trong và một đơn vị chức năng, bỏ qua các đơn vị chức năng thời hạn khác, hãy sử dụng một trong những hàm sau:
Chênh lệch tiếng, bỏ lỡ phút và giây:
=HOUR(B2-A2)
Chênh lệch phút ít, bỏ qua mất tiếng và giây:
=MINUTE(B2-A2)
Chênh lệch giây, bỏ qua tiếng và phút:
=SECOND(B2-A2)
khi áp dụng hàm HOUR, MINUTE, SECOND, hãy hãy nhờ rằng kết quả không được vượt vượt 24 giờ, 60 phút hoặc 60 giây.
Crúc ý. Nếu thời gian ngừng to hơn thời hạn bắt đầu, rõ ràng nếu như kết quả là số âm, công thức vẫn hiển thị lỗi #NUM!
Công thức 5. Tính thời gian trôi qua kể từ thời hạn ban đầu đến bây giờ
Để tính thời hạn vẫn trôi qua, chúng ta cũng có thể áp dụng hàm Now cùng Time. Trường đúng theo khoảng chừng thời hạn trôi qua dưới 24 tiếng, bạn chỉ cần áp dụng hàm NOW để đưa hiệu quả ngày tháng và thời gian ngày nay, sau đó trừ đi ngày và thời hạn bước đầu.
Giả sử ô A2 chứa thời gian bước đầu, phương pháp =NOW()-A2 đến tác dụng sau với cột B được định dạng thời gian phù hợp, trong ví dụ này nó gồm dạng h:mm
Trong ngôi trường hợp khoảng chừng thời hạn trôi qua quá thừa 24 giờ đồng hồ, sử dụng một trong những format thời gian sau, ví dụ d ‘days’ h:mm:ss như vào hình sau:
=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) – A2
Chụ ý. Khoảng thời gian trôi qua sẽ không được cập nhật theo thời hạn thực tế, nó chỉ cập nhật Khi bảng tính được mlàm việc lại hoặc tính toán lại. Để cập nhật cách làm, thừa nhận Shift + F9 nhằm tính lại trang tính hoặc thừa nhận F9 nhằm 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 áp dụng các hàm HOUR, MINUTE cùng SECOND để lấy kết 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 ghnghiền những hàm vào cùng một phương pháp thuộc những chuỗi text nlỗi sau :
=INT(B2-A2) & ” days, ” & HOUR(B2-A2) & ” hours, ” và MINUTE(B2-A2) và ” minutes and ” và 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) & ” days, “,””) và IF(HOUR(B2-A2)>0, HOUR(B2-A2) & ” hours, “,””) và IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) và ” minutes & “,””) và IF(SECOND(B2-A2)>0, SECOND(B2-A2) & ” seconds”,””)
Trong khi, bạn có thể tính tân oán chênh lệch thời gian bằng cách lấy thời hạn bắt đầu trừ thời hạn kết thúc (ví dụ = B2-A2) rồi tùy chỉnh thiết lập định hình sau mang đến ô tính:
d “days,” h “hours,” m “minutes and” s “seconds”
Cách tính cùng hiển thị thời hạn âm vào Excel
khi tính tân oán chênhh lệch thờ gian, chúng ta có thể phát hiện lỗi ##### do độ chênh lệch này âm. Tuy nhiên, cũng có nhiều cách để hiển thị tác dụng âm vào Excel.
Pmùi hương pháp 1. Txuất xắc đổi hệ thông ngày tháng của Excel thành 1904 date system
Vào File > Options > Advanced, kéo thanh khô cuốn xuống When calculating this workbook cùng đánh dấu chọn Use 1904 date system.
Nhấp OK nhằm lưu giữ thiết lập mới, giá trị âm sẽ được hiển thị nhỏng sau:
Phương thơm pháp 2. Tính toán thù thời hạn âm bởi công thức
Nếu không muốn áp dụng phương thức 1, bạn cũng có thể áp dụng công thức 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 phương pháp sẽ đánh giá chênh lệch thời hạn (A2-B2) có lớn hơn 0 hay là không và nó gồm hiện tại hiệu quả hay không. Nếu chênh lệch bé nhiều hơn 0, công thức trước tiên và tính trị hoàn hảo rồi thêm dấu trừ. Công thức máy nhì mang lại công dụng giống như bởi định hình thời hạn âm “-h:: mm”.
Crúc ý. Hãy đừng quên phương thức đầu tiên đã tạo ra tác dụng âm là cực hiếm số âm, kết quả hàm TEXT luôn là chuỗi văn uống phiên bản cùng quan yếu áp dụng vào phép toán thù hoặc công thức khác.
Cộng/trừ thời hạn trong Excel
Về cơ bản, bao gồm 2 giải pháp cộng/trừ thời hạn vào Excel:
Sử dụng hàm TIMESử dụng phnghiền tân oán dựa vào khoảng thời gian (24), số phút ít (1440), số giây (86400) vào một ngày.Hàm TIME(hour, minute, second) giúp việc tính tân oán thuận tiện rộng, tuy nhiên nó không cho phép bài toán cộng trừ hơn 23h, 59 phút ít hoặc 59 giây. Nếu bạn muốn tính toán thù khoảng chừng thời hạn to hơn, hãy áp dụng các phnghiền toán tiếp sau đây.
Tsi mê khảo ngay: Giáo trình Excel 2020
Cộng/trừ tiếng vào thời gian núm thể
Hàm thời hạn để thêm vào đó dưới 24 giờ
=Start time + TIME(N hours, 0, 0)
lấy một ví dụ, trong ô A2 là thời gian ban đầu, bạn có nhu cầu thêm 2 giờ vào đó, cách làm đã là:
=A2 + TIME(2, 0, 0)
Công thức nhằm cùng bất kỳ khoảng thời gian như thế nào (dưới/trên 24 giờ)
=Start time + (N hours/24)
Ví dụ, để cộng thêm 28 giờ đồng hồ vào ô A2, nhập cách làm sau:
=A2 + (28/24)
Để trừ đi một số trong những giờ đồng hồ, sử dụng bí quyết tương tự nlỗi nạm vết + bằng vệt –
lấy ví dụ như, trừ ô A2 mang lại 3h, nhập một trong số bí quyết sau:
=A2-(3/24)
=A2-TIME(3,0,0)
Nếu bạn trừ số giờ to hơn 23, thực hiện công thức trước tiên.
Cách cộng/trừ phút ít vào thời gian cố gắng thể
Cộng/trừ dưới 60 phút
Sử dụng hàm TIME để cộng trừ số phút ít bạn muốn vào tyêu thích số thứ 2:
=Start time + TIME(0, N minutes, 0)
Đây là 1 trong những vài công thức thực tiễn nhằm tính số phút trong Excel:
Công trăng tròn phút vào A2: =A2 + TIME(0,trăng tròn,0)
Trừ A2 đến 30 phút: =A2 – TIME(0,30,0)
Cộng trừ trên 60 phút
Trong phnghiền tính của người sử dụng, phân chia số phút đến 1440 – số phút ít trong một ngày và thêm thời gian bước đầu vào :
=Start time + (N minutes / 1440)
Để trừ số phút ít, vắt lốt + 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 hạn cụ thể
Để thêm vào đó số giây ít hơn 60 vào thời hạn ví dụ, thực hiện hàm TIME:
=Start time + TIME(0, 0, N seconds)
Để cộng thêm 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ách làm giống như nhưng mà nỗ lực dấu + thành vệt -.
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 đến 900 giây: =A2 – (900/86400)Cách cùng thời hạn vào ExcelPhép cộng Excel thường xuyên thực hiện hàm SUM, với vận dụng định hình cân xứng đến kết quả.
Giả sử chúng ta bao gồm một vài thời hạn vào cột B với bạn có nhu cầu cùng bọn chúng cùng nhau. quý khách hàng viết bí quyết SUM nhỏng sau =SUM(B2:B4), công dụng hiển thị theo định hình khoác định như hh:mm:ss
Trong một vài ba trường đúng theo, định dạng thời gian mặc định hoàn toàn có thể ổn định, dẫu vậy nhiều khi bạn muốn hiển thị tổng thời hạn dưới dạng phút ít cùng giây, hoặc chỉ hiển thị giây. Lúc kia, chúng ta không phải phép tính nào cả, các bạn chỉ cần định hình lại thời hạn cho ô tính cất công thức SUM.
Nhấp đề xuất vào ô tính và chọn Format Cells từ bỏ danh sách tùy chọn, dấn Ctrl + 1 để msinh sống hộp thoại Format Cells. Chọn Custom từ Category các mục và gõ một trong số định dạng sau vào vỏ hộp Type:
Hiển thị thời hạn dưới dạng phút ít với giây: :ss
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 tân oán tác dụng hơn 24 giờ, chúng ta cũng có thể thực hiện cách làm SUM, cùng chọn một trong số format sau:
Định dạng | Hiển thị | Giải thích |
:mm | 30:10 | Giờ với 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 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 |
Để coi hiệu quả định dạng vào trang tính thực, coi hình dưới đây (bí quyết 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 đến số dương. Nếu tác dụng tính toán thù là số âm, tức là các bạn lấy quý hiếm nhỏ dại rộng trừ cho quý hiếm to hơn, tác dụng vẫn hiển thị #####.
Bài viết coi nhiều