Công thức tính thời gian trong excel
Trong bài viết này, Học Excel Online đã khuyên bảo rất nhiều bí quyết có lợi để 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 thời gian 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 hạn trong ExcelCách cộng thời gian vào ExcelCách hiển thị khoảng tầm thời gian lớn hơn 24 giờ, 60 phút, 60 giây?Cách cộng trừ thời gian to hơn 24 tiếng, 60 phút í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ỏng chúng ta sẽ biết, thời hạn trong Excel thường được định dạng số thập phân nhằm trông như là thời gian. Vì bọn chúng là số, buộc phải bạn có thể cộng trừ thời gian như những quý hiếm số không giống.
Công thức đơn giản tuyệt 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 hạn thực tế hoàn toàn có thể có rất nhiều dạng khác nhau, ví dụ:
| Công thức | Giải thích |
| =A2-B2 | Tính toán thù chênh lệch thời gian thân nhị cực hiếm trong ô 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 thân hai thời hạn nuốm thể |
| =TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) – TIME(HOUR(B2), MINUTE(B2), SECOND(B2)) | Tính toán thù chênh lệch thời gian thân nhị quý giá trong ô A2 cùng B2, quanh đó chênh lệch tháng ngày ví như ô tính đựng cả giá trị ngày tháng với thời gian |
Trong hệ thống Excel, thời gian thể hiện tại vị trí thập phân, bạn sẽ tất cả công dụng như sau:
Phần thập phân vào cột D đúng nhưng lại không tồn tại chân thành và ý nghĩa. Để góp bọn chúng báo tin hữu ích rộng, bạn có thể áp dụng format thời gian với cùng 1 trong số mã sau đây:
| Mã thời gian | Giải thích |
| h | Số giờ trôi qua, ví dụ 4 |
| h:mm | Số tiếng với số phút trôi qua, ví dụ 4:10 |
| h:mm:ss | Số giờ đồng hồ, số phút ít cùng số giây trôi qua, ví dụ 4:10:đôi mươi. |
Để vận dụng định dạng thời hạn, nhận Ctrl + 1 nhằm mnghỉ ngơi vỏ hộp thoại Format Cells, chọn Custom từ bỏ Category các mục cùng gõ mã thời hạn vào hộp Type.
Bây tiếng, để xem bí quyết tính chênh lệch thời gian cùng mã thời gian chuyển động ra làm sao vào bảng tính thực sự. Với Start time trong cột A cùng End time vào cột B, chúng ta có thể xào nấu phương pháp 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 theo format thời gian trong mỗi cột:
Chụ ý. Nếu thời gian chênh lệch được hiển tỉnh thành #####, nghĩa là ô chứa phương pháp không đủ rộng để hiển thị không còn thời gian hoặc tác dụng phnghiền tính là 1 quý hiếm â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 để 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 nhì thời gian: =TEXT(B2-A2, “h”)Tính toán thù chênh lệch tiếng với phút thân hai thời gian: =TEXT(B2-A2, “h:mm”)Tính toán chênh lệch tiếng, 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à vnạp năng lượng phiên bản. Các quý hiếm text được căn uống lề trài trong những cột C, D, E trong hình trên. Trong vài trường hợp khăng khăng, vấn đề này rất có thể là một trong những tiêu giảm vì chưng bạn sẽ cần thiết sử dụng công dụng này nhằm liên tiếp tính toán.Công thức 3. Tính giờ đồng hồ, phút, giây thân nhị thời gian
Để hiển thị chênh lệch thân nhị thời hạn bằng một đơn vị chức năng (tiếng, phút ít, giây), bạn phải tiến hành các phép tính sau.
Phnghiền tính giờ đồng hồ thân nhì thời gian
=(End time – Start time) * 24
Giả sử thời gian ban đầu trong ô A2 và thời gian ngừng trong ô B2, bạn hãy sử dụng phxay tính dễ dàng và đơn giản là B2-A2 để tính toán chênh lệch thời hạn, tiếp nối nhân với 24.
=(B2-A2) * 24
Để gồm số Complete hours, áp dụng hàm INT để làm tròn đến số nguyên ổn hần nhất:
=INT((B2-A2) * 24)
Tổng số phút thân nhị thời gian:
Để tính số phút ít giữa nhị 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 có thể đến hiệu quả âm hoặc dương, tác dụng âm xảy ra Lúc thời hạn dứt lớn hơn thời hạn bắt đầu nhỏng hàng 5:
=(B2-A2)*1440
Tổng số giây thân nhị thời gian:
Để tính số giây thân nhì thời hạn, nhân chênh lệch thời hạn với 86400
=(End time – Start time) * 86400
Trong ví dụ, cách làm đang nhỏng sau:
=(B2-A2)* 86400
Crúc ý. Định dạng General đề nghị được tùy chỉnh đến ô tính đựng bí quyết tính chênh lệch thời gian để tác dụng hiển thị đúng.
Công thức 4. Tính chênh lệch thời gian vào cùng một đơn vị thời hạn và bỏ qua những đơn vị khác
Để tính chênh lệch thân nhị thời hạn trong cùng một đơn vị chức năng, bỏ qua mất những đơn vị thời gian không giống, hãy áp dụng một trong những hàm sau:
Chênh lệch giờ, bỏ qua mất phút với 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 giờ đồng hồ với phút:
=SECOND(B2-A2)
lúc sử dụng hàm HOUR, MINUTE, SECOND, hãy hãy nhờ rằng tác dụng ko được thừa vượt 24 giờ đồng hồ, 60 phút ít hoặc 60 giây.
Crúc ý. Nếu thời gian chấm dứt to hơn thời hạn bắt đầu, rõ ràng nếu như kết quả là số âm, phương pháp sẽ hiển thị lỗi #NUM!
Công thức 5. Tính thời hạn trôi qua Tính từ lúc thời hạn bắt đầu đến bây giờ
Để tính thời gian đang trôi qua, bạn cũng có thể sử dụng hàm Now cùng Time. Trường thích hợp khoảng chừng thời gian trôi qua dưới 24 tiếng, bạn chỉ việc sử dụng hàm NOW để đưa tác dụng tháng ngày với thời gian hiện nay, sau đó trừ đi ngày với thời gian ban đầu.
Giả sử ô A2 chứa thời khắc bắt đầu, phương pháp =NOW()-A2 cho tác dụng sau với cột B được format thời gian tương xứng, trong ví dụ này nó bao gồm dạng h:mm
Trong ngôi trường hòa hợp khoảng thời gian 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ụ d ‘days’ h:mm:ss nlỗi vào hình sau:
Nếu thời hạn bắt đầu chỉ tất cả giờ đồng hồ phút ít mà không tồn tại ngày, bạn cần thực hiện hàm Time nhằm tính toán thù khoảng chừng thời gian trôi đi một biện pháp đúng đắn. lấy ví dụ, phương pháp sau trả về khoảng tầm thời gian trường đoản cú ô A2 mang đế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 cập nhật theo thời hạn thực tiễn, nó chỉ update Khi bảng tính được mlàm việc lại hoặc tính toán lại. Để cập nhật bí quyết, thừa nhận Shift + F9 để tính lại trang tính hoặc nhận F9 để tính lại toàn 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 áp dụng những hàm HOUR, MINUTE cùng SECOND để đưa tác dụng thời gian tương tứng cùng hàm INT nhằm tính chênh lệch ngày.Sau kia, các 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 nlỗi sau :
=INT(B2-A2) & ” days, ” và HOUR(B2-A2) & ” hours, ” & MINUTE(B2-A2) & ” minutes and ” & 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, “,””) & IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) và ” minutes and “,””) và IF(SECOND(B2-A2)>0, SECOND(B2-A2) và ” seconds”,””)
Trong khi, bạn có thể tính tân oán chênh lệch thời hạn bằng phương pháp rước thời hạn ban đầu trừ thời hạn xong xuôi (ví dụ = B2-A2) rồi thiết lập cấu hình định dạng sau đế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
lúc tính tân oán chênhh lệch thờ gian, chúng ta có thể bắt gặp lỗi ##### bởi độ chênh lệch này âm. Tuy nhiên, cũng có nhiều phương pháp để hiển thị hiệu quả âm vào Excel.
Pmùi hương pháp 1. Thay đổi hệ thông tháng ngày của Excel thành 1904 date system
Vào File > Options > Advanced, kéo thanh khô cuốn nắn xuống When calculating this workbook với đánh dấu lựa chọn Use 1904 date system.
Nhấp OK nhằm lưu lại setup new, quý giá âm sẽ được hiển thị như 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 thích sử dụng cách thức 1, chúng ta cũng có thể sử dụng công thức 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ả nhị cách làm đang khám nghiệm chênh lệch thời gian (A2-B2) tất cả to hơn 0 hay không cùng nó gồm hiện nay kết quả hay là không. Nếu chênh lệch bé hơn 0, bí quyết trước tiên công thêm trị tuyệt đối rồi thêm vết trừ. Công thức lắp thêm nhì mang lại hiệu quả giống như bởi định hình thời hạn âm “-h:: mm”.
Chú ý. Hãy hãy nhớ là phương pháp đầu tiên tạo ra kết quả âm là cực hiếm số âm, hiệu quả hàm TEXT luôn là chuỗi vnạp năng lượng phiên bản và không thể thực hiện vào phxay tân oán hoặc phương pháp khác.
Xem thêm: Tôn Ngộ Không 2017 ) Hd Vietsub, Ngộ Không Kỳ Truyện
Cộng/trừ thời hạn vào Excel
Về cơ bạn dạng, gồm 2 biện pháp cộng/trừ thời hạn vào Excel:
Sử dụng hàm TIMESử dụng phép tân oán dựa vào số giờ (24), số phút í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ễ dàng rộng, mặc dù nó không được cho phép bài toán cùng trừ rộng 23 giờ, 59 phút hoặc 59 giây. Nếu bạn muốn tính tân oán khoảng thời hạn lớn hơn, hãy thực hiện các phnghiền toán tiếp sau đây.
Tham mê khảo ngay: Giáo trình Excel 2020
Cộng/trừ giờ vào thời hạn vậy thể
Hàm thời gian nhằm cộng thêm bên dưới 24 giờ
=Start time + TIME(N hours, 0, 0)
Ví dụ, trong ô A2 là thời gian bước đầu, bạn có nhu cầu thêm 2 tiếng đồng hồ vào đó, cách làm đã là:
=A2 + TIME(2, 0, 0)
Công thức nhằm cộng bất kỳ số giờ làm sao (dưới/bên trên 24 giờ)
=Start time + (N hours/24)
lấy ví dụ như, nhằm thêm vào đó 28 giờ đồng hồ vào ô A2, nhập cách làm sau:
=A2 + (28/24)
Để trừ đi một trong những giờ đồng hồ, thực hiện bí quyết giống như như nuốm lốt + bằng lốt –
ví dụ như, trừ ô A2 cho 3 giờ, nhập một trong những phương pháp sau:
=A2-(3/24)
=A2-TIME(3,0,0)
Nếu các bạn trừ khoảng thời gian lớn hơn 23, áp dụng cách làm đầu tiên.
Cách cộng/trừ phút ít vào thời hạn cầm thể
Cộng/trừ dưới 60 phút
Sử dụng hàm TIME để cùng trừ số phút ít bạn có nhu cầu vào tmê mẩn số máy 2:
=Start time + TIME(0, N minutes, 0)
Đây là một vài ba phương pháp thực tiễn nhằm tính số phút vào Excel:
Công trăng tròn 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 phxay tính của doanh nghiệp, phân tách số phút ít mang đến 1440 – số phút vào một ngày cùng thêm thời hạn bắt đầu vào :
=Start time + (N minutes / 1440)
Để trừ số phút ít, cố vế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 chũm thể
Để thêm vào đó số giây thấp hơn 60 vào thời gian rõ ràng, thực hiện hàm TIME:
=Start time + TIME(0, 0, N seconds)
Để thêm vào đó số giây lớn 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 bí quyết giống như tuy nhiên cố kỉnh lốt + thành dấu -.
Trên trang tính Excel, phương pháp trông nlỗi 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 gian trong ExcelPhnghiền cộng Excel thường xuyên áp dụng hàm SUM, cùng vận dụng format tương xứng mang lại tác dụng.
Giả sử bạn tất cả một vài thời hạn trong cột B cùng bạn muốn cùng chúng với nhau. Quý khách hàng viết phương pháp 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 ngôi trường phù hợp, định hình thời gian khoác định hoàn toàn có thể ổn, nhưng mà nhiều khi bạn muốn hiển thị tổng thời hạn dưới dạng phút và giây, hoặc chỉ hiển thị giây. Khi đó, chúng ta ko nên phxay tính nào cả, chúng ta chỉ việc format lại thời hạn mang đến ô tính chứa công thức SUM.
Nhấp đề xuất vào ô tính với lựa chọn Format Cells tự danh sách tùy lựa chọn, nhấn Ctrl + 1 nhằm mnghỉ ngơi vỏ hộp thoại Format Cells. Chọn Custom tự Category danh sách và gõ một trong các format sau vào vỏ hộp Type:
Hiển thị thời gian bên dưới dạng phút ít và giây:
Hiển thị thời gian dưới dạng giây:
Kết đúng như sau:
Cách cùng qua 24 giờ đồng hồ vào Excel
Để tính tân oán kết quả hơn 24 giờ, chúng ta cũng có thể thực hiện phương pháp SUM, với chọn một trong số format 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, đôi mươi seconds | ||
| d h:mm:ss | 1 06:10:20 | Ngày, giờ đồng hồ, phút í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 & đôi mươi seconds |
Để coi tác dụng định hình vào trang tính thực, coi hình dưới đây (công thức SUM được nhập lệ ô A9 tới ô A13:
=SUM($B$2:$B$4)
Crúc ý. Định dạng thời hạn chỉ áp dụng đến số dương. Nếu công dụng tính toán thù là số âm, tức là chúng ta mang giá trị bé dại hơn trừ đến giá trị lớn hơn, kết quả đang hiển thị #####.
Cách hiển thị khoảng chừng thời hạn to hơn 24 giờ, 60 phút ít, 60 giây?
Để hiện thị lên một khoảng chừng thời hạn to hơn 24 tiếng, 60 phút ít, 60 giây, hãy sử dụng custom time format khu vực nhưng mà mã đơn vị thời gian tương xứng bên trong lốt ngoặc vuông, ví dụ điển hình như
Lớn hơn 24 giờ:
Lớn rộng 60 phút:
Lớn hơn 60 giây:
Hình ảnh chụp màn hình hiển thị sau đây cho thấy thêm định hình thời gian “to hơn 24 giờ” trên thực tế:
Dưới đây là một vài định dạng khác. Chúng ta rất có thể thực hiện nó nhằm hiển thị hầu như khoảng thời hạn to hơn chiều nhiều năm đơn vị thời gian chuẩn.
| Miêu tả | Mã định dạng |
| Tổng số giờ | |
| Số giờ đồng hồ cùng phút | |
| Số tiếng, phút, giây | |
| Tổng số phút | |
| Số phút ít với giây | |
| Tổng số giây |
khi được sử dụng mang lại tài liệu mẫu mã của chúng ta (Thời gian tổng cộng 50:40 trong hình ảnh chụp screen trên), phần nhiều định hình thời hạn này đang cho hiệu quả nlỗi sau:
| A | B | C | |
| 1 | Miêu tả | Thời gian hiển thị | Định dạng |
| 2 | Số giờ | 50 | |
| 3 | Số giờ đồng hồ và phút | 50:40 | |
| 4 | Số tiếng, phút ít, giây | 50:40:30 | |
| 5 | Số phút | 3040 | |
| 6 | Sốphút ít cùng giây | 3040:30 | |
| 7 | Số giây | 182430 |
Để rất nhiều khoảng chừng thời gian được hiển thị ở trên dễ dàng nắm bắt rộng so với người dùng, bạn cũng có thể bổ sung đơn vị chức năng thời hạn bằng các tự viết tắt tương xứ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ờ đồng hồ và 40 phút | |
| 3 | Số giờ đồng hồ, phút ít, giây | 50h.40m.30s | |
| 4 | Số phút | 3040 phút | |
| 5 | Số phút với giây | 3040 phút ít và 30 giây | |
| 6 | Số giây | 182430 giây |
Lưu ý. Mặc dù đa số khoảng tầm thời gian trên trông hệt như chuỗi ký kết trường đoản cú, tuy thế chúng vẫn luôn là quý hiếm số, cũng chính vì định dạng văn bản số trong Excel chỉ biến đổi về hình thức chđọng thực chất không thay đổi. Vì vắt, bạn có thể tự do thoải mái cộng tốt trừ phần lớn khoảng chừng thời gian đã có định dạng một giải pháp bình thường, hãy tsi mê chiếu chúng vào công thức cùng áp dụng trong các phnghiền tính không giống.
Cách cộng trừ thời hạn lớn hơn 24 giờ đồng hồ, 60 phút ít, 60 giây
Để cộng khoảng tầm thời hạn mong muốn cho 1 khoảng tầm thời gian cho sẵn, hãy rước khoảng thời gian, phút, tuyệt giây cơ mà bạn có nhu cầu cộng mang đến đơn vị chức năng thời hạn tương ứng vào một ngày (24 tiếng, 1440 phút ít, 86400 giây), rồi cùng số thương thơm cho thời gian ban đầu.
Cộng hơn 24 tiếng.Thời gian bước đầu + (N/24)
Cộng rộng 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 số đó, N là số giờ, phút ít, tốt giây cơ mà bạn muốn cộng.
Đây là 1 trong những vài ví dụ bí quyết bên trên thực tế:
Để cộng 45 tiếng cho thời gian bước đầu vào ô 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 mang lại thời hạn bước đầu vào ô A2:
=A2+(200/86400)
Hoặc là, bạn cũng có thể nhập khoảng thời hạn phải cùng vào trong 1 ô lẻ tẻ rồi tyêu thích chiếu ô đó vào cách làm gồm dạng nhỏng vào ảnh chụp screen bên dưới đây:
Để trừ khoảng tầm thời hạn trong Excel, hãy thực hiện bí quyết tương tự như tuy vậy hãy rứa dấu cộng bởi vệt trừ nhé!
Trừ hơn 24 giờThời gian ban đầu – (N/24)
Trừ hơn 60 phút ít.Thời gian bước đầu – (N/1440)
Trừ hơn 60 giâyThời gian ban đầu – (N/86400)
Hình ả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 hạn được xem tân oán bao gồm dạng số thập phân, hãy áp dụng định dạng thời gian/ngày nhằm format ô.Nếu sau khi sử dụng định hình với ô hiển thị #####, thì thông thường ô không đủ rộng nhằm hiển thị giá trị thời hạn trong thời gian ngày. Để xử trí vấn đề này, hãy mở rộng chiều dài ô bằng cách nhấp lưu ban chuột tuyệt kéo khung bên yêu cầu của cột.Đó là toàn bộ cách để các bạn có thể phương pháp cộng trừ thời gian vào excel. Nếu còn bất cứ thắc mắc gì thì chớ hổ ngươi ngần cmt xuống bên dưới sẽ được câu trả lời nhanh hao tốt nhất nhé!
