Trong bài viết này, Học Excel Online sẽ chỉ dẫn phần đông công thức hữu dụng nhằm cộng/trừ thời hạn, chênh lệch thời gian, đếm giờ; cách cùng trừ thời hạn vào excel.

Bạn đang xem: Công thức trừ thời gian trong excel


Cách tính chênh lệch thời gian vào ExcelCách tính và hiển thị thời gian âm vào ExcelCộng/trừ thời hạn vào ExcelCách cộng thời gian trong ExcelCách hiển thị khoảng thời gian lớn hơn 24 giờ, 60 phút, 60 giây?Cách cùng trừ thời hạn lớn hơn 24 tiếng, 60 phút, 60 giây

Cách tính chênh lệch thời hạn vào Excel

Công thức 1. Trừ thời gian

Như các bạn đang biết, thời hạn 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ì chúng là số, buộc phải chúng ta cũng có thể cộng trừ thời gian nhỏng các cực hiếm số khác.

Công thức đơn giản và dễ dàng tuyệt nhất nhằm tính chênh lệch thời gian là:

=End time – Start time

Tùy vào kết cấu tài liệu, chênh lệch thời gian thực tiễn hoàn toàn có thể có tương đối nhiều dạng không giống nhau, ví dụ:

Công thứcGiải thích
=A2-B2Tính toán thù chênh lệch thời hạn thân hai cực hiếm vào ô A2 và B2
=TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”)Tính toán chênh lệch thời gian thân nhì 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 hạn thân hai giá trị trong ô A2 và B2, quanh đó chênh lệch tháng ngày nếu như ô tính đựng cả quý hiếm ngày tháng với thời gian

Trong khối hệ thống Excel, thời gian thể hiện ở chỗ thập phân, bạn sẽ gồm kết quả như sau:

*
1 – phương pháp tính thời hạn trong Excel

Phần thập phân trong cột D đúng tuy vậy không có ý nghĩa sâu sắc. Để giúp chúng đưa thông tin hữu ích hơn, bạn có thể vận dụng định dạng thời hạn với 1 trong những mã sau đây:

Mã thời gianGiải thích
hSố giờ trôi qua, ví dụ 4
h:mmSố tiếng cùng số phút ít trôi qua, ví dụ 4:10
h:mm:ssSố tiếng, số phút cùng số giây trôi qua, ví dụ 4:10:20.

Để áp dụng định dạng thời hạn, dìm Ctrl + 1 để mnghỉ ngơi hộp thoại Format Cells, lựa chọn Custom từ bỏ Category các mục với gõ mã thời hạn vào vỏ hộp Type.

Bây giờ đồng hồ, giúp xem cách làm tính chênh lệch thời hạn với mã thời gian hoạt động như thế nào vào bảng tính thực thụ. Với Start time vào cột A cùng End time trong cột B, chúng ta có thể xào nấu 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 biệt tùy theo định dạng thời gian trong mỗi cột:

*
2 – phương pháp tính thời gian vào Excel

Crúc ý. Nếu thời gian chênh lệch được hiển thị thành #####, nghĩa là ô cất cách làm không được rộng nhằm hiển thị hết thời hạn hoặc hiệu quả phnghiền tính là một trong những quý giá âm.

Công thức 2. Tính chênh lệch thời hạn bởi hàm TEXT

Một kỹ thuật khác nhằm tính chênh lệch thời gian vào 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 cùng phút giữa nhị thời gian: =TEXT(B2-A2, “h:mm”)Tính toán thù chênh lệch tiếng, phút cùng giây giữa nhị thời gian: =TEXT(B2-A2, “h:mm:ss”)
*
3 – cách tính thời gian trong Excel

Chụ ý.

Kết quả của hàm TEXT luôn luôn là văn uống bạn dạng. Các quý giá text được căn lề trài trong số cột C, D, E vào hình bên trên. Trong vài ba trường hợp một mực, điều đó rất có thể là 1 tinh giảm bởi bạn sẽ bắt buộc sử dụng công dụng này nhằm tiếp tục tính tân oán.

Công thức 3. Tính tiếng, phút, giây thân hai thời gian

Để hiển thị chênh lệch giữa hai thời hạn bằng một đơn vị (tiếng, phút ít, giây), bạn cần triển khai những phnghiền tính sau.

Phép tính giờ đồng hồ giữa nhì thời gian

=(End time – Start time) * 24

Giả sử thời gian ban đầu trong ô A2 với thời hạn dứt vào ô B2, bạn hãy sử dụng phép tính dễ dàng và đơn giản là B2-A2 nhằm tính toán thù chênh lệch thời hạn, sau đó nhân cùng với 24.

=(B2-A2) * 24

Để bao gồm số Complete hours, sử dụng hàm INT để gia công tròn cho số nguyên hần nhất:

=INT((B2-A2) * 24)

*
4 – phương pháp tính thời gian vào Excel

 

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 gian cùng với 1440

=(End time – Start time) * 1440

Công thức rất có thể mang lại công dụng âm hoặc dương, kết quả âm xẩy ra Khi thời hạn hoàn thành lớn hơn thời gian bước đầu nhỏng mặt hàng 5:

=(B2-A2)*1440

*
5 – phương pháp tính thời gian vào Excel

 

Tổng số giây giữa nhị thời gian:

Để tính số giây giữa nhị thời gian, nhân chênh lệch thời hạn cùng với 86400

=(End time – Start time) * 86400

Trong ví dụ, phương pháp sẽ nlỗi sau:

=(B2-A2)* 86400

*
6 – phương pháp tính thời gian vào Excel

Chú ý. Định dạng General cần được tùy chỉnh thiết lập đến ô tính đựng phương pháp tính chênh lệch thời gian nhằm tác dụng hiển thị đúng.

Công thức 4. Tính chênh lệch thời hạn vào cùng một đơn vị chức năng thời hạn với làm lơ các đơn vị chức năng không giống

Để tính chênh lệch thân nhị thời hạn trong và một đơn vị chức năng, làm lơ những đơn vị thời gian khác, hãy thực hiện một trong những hàm sau:

Chênh lệch tiếng, bỏ lỡ phút ít với giây:

=HOUR(B2-A2)

Chênh lệch phút, làm lơ tiếng cùng giây:

=MINUTE(B2-A2)

Chênh lệch giây, bỏ qua giờ cùng phút:

=SECOND(B2-A2)

Lúc thực hiện hàm HOUR, MINUTE, SECOND, hãy hãy nhờ rằng hiệu quả không được vượt vượt 24 tiếng, 60 phút hoặc 60 giây.

*
7 – phương pháp tính thời gian trong Excel

Chú ý. Nếu thời hạn chấm dứt lớn hơn thời hạn ban đầu, ví dụ ví như tác dụng là số âm, phương pháp vẫn hiển thị lỗi #NUM!

Công thức 5. Tính thời hạn trôi qua kể từ thời hạn bắt đầu đến bây giờ

Để tính thời hạn đang trôi qua, bạn cũng có thể sử dụng hàm Now và Time. Trường thích hợp khoảng thời hạn trôi qua dưới 24 tiếng, bạn chỉ cần thực hiện hàm NOW để đưa công dụng ngày tháng với thời hạn hiện thời, tiếp nối trừ đi ngày với thời hạn ban đầu.

Giả sử ô A2 cất thời gian ban đầ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 hạn cân xứng, trong ví dụ này nó có dạng h:mm

*
8 – cách tính thời gian trong Excel

 

Trong trường hợp khoảng thời gian trôi qua thừa thừa 24 giờ, áp dụng một trong những format thời gian sau, ví dụ d ‘days’ h:mm:ss nlỗi trong hình sau:

*
9 – phương pháp tính thời hạn trong Excel

Nếu thời hạn bắt đầu chỉ gồm giờ phút ít mà lại không có ngày, bạn cần áp dụng hàm Time để tính toán thù khoảng tầm thời gian trôi đi một giải pháp chính xác. lấy ví dụ, công thức sau trả về khoảng tầm thời hạn tự ô A2 mang lại giờ đồng hồ.

=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) – A2

*
10- cách tính thời gian vào Excel

Crúc ý. Khoảng thời gian trôi qua sẽ không được update theo thời gian thực tiễn, nó chỉ update Lúc bảng tính được mở lại hoặc tính tân oán lại. Để cập nhật bí quyết, thừa nhận Shift + F9 nhằm tính lại trang tính hoặc thừa nhận F9 để tính lại cục 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 & XX seconds”

Quý Khách thực hiện các hàm HOUR, MINUTE SECOND để mang hiệu quả thời gian tương tứng và hàm INT nhằm tính chênh lệch ngày.Sau đó, chúng ta lồng ghép những hàm vào và một phương pháp thuộc những chuỗi text như sau :

=INT(B2-A2) & ” days, ” & HOUR(B2-A2) & ” hours, ” và MINUTE(B2-A2) & ” minutes và ” & SECOND(B2-A2) & ” seconds”

 

*

Để ẩn quý 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, “,””) & IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) và ” minutes & “,””) & IF(SECOND(B2-A2)>0, SECOND(B2-A2) & ” seconds”,””)

 

*

Trong khi, bạn cũng có thể tính tân oán chênh lệch thời gian bằng phương pháp rước thời hạn bước đầu trừ thời gian dứt (ví dụ = B2-A2) rồi tùy chỉnh thiết lập format sau cho ô tính:

d “days,” h “hours,” m “minutes and” s “seconds”

*
11 – phương pháp tính thời gian trong Excel

Cách tính với hiển thị thời hạn âm vào Excel

Lúc tính toán thù 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ó rất nhiều phương pháp để hiển thị hiệu quả âm trong Excel.

Phương thơm pháp 1. Txuất xắc thay đổi hệ thông ngày tháng của Excel thành 1904 date system

Vào File > Options > Advanced, kéo tkhô cứng cuốn xuống When calculating this workbook với đánh dấu lựa chọn Use 1904 date system.

*
12 – cách tính thời hạn trong Excel

Nhấp OK nhằm giữ thiết lập mới, quý hiếm âm sẽ được hiển thị như sau:

*

Pmùi hương pháp 2. Tính tân oán thời gian âm bằng công thức

Nếu không thích thực hiện cách thức 1, bạn cũng có thể áp 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ả nhì bí quyết sẽ soát sổ chênh lệch thời gian (A2-B2) tất cả to hơn 0 hay là không cùng nó gồm hiện nay công dụng hay không. Nếu chênh lệch bé thêm hơn 0, công thức đầu tiên công thêm trị hoàn hảo và tuyệt vời nhất rồi thêm vết trừ. Công thức đồ vật nhì mang đến hiệu quả tương tự như bởi định dạng thời gian âm “-h:: mm”.

*

Crúc ý. Hãy nhớ là phương thức thứ nhất cho ra kết quả âm là quý giá số âm, kết quả hàm TEXT luôn luôn là chuỗi vnạp năng lượng bạn dạng với cần yếu thực hiện trong phép toán hoặc công thức khác.

Xem thêm: Iphone 6 Plus / 6S Plus Ốp Lưng Chống Sốc Iphone 6 Plus /X, Ốp Lưng Iphone 6/6S Plus

Cộng/trừ thời hạn vào Excel

Về cơ bản, bao gồm 2 bí quyết cộng/trừ thời hạn trong Excel:

Sử dụng hàm TIMESử dụng phnghiền toán thù dựa vào số giờ (24), số phút (1440), số giây (86400) vào một ngày.

Hàm TIME(hour, minute, second) giúp vấn đề tính toán thù dễ ợt rộng, mặc dù nó ko 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 to hơn, hãy áp dụng các phnghiền toán thù dưới đây.

Tsay mê khảo ngay: Giáo trình Excel 2020

Cộng/trừ giờ đồng hồ vào thời gian cầm cố thể

Hàm thời gian để cộng thêm bên dưới 24 giờ

=Start time + TIME(N hours, 0, 0)

lấy ví dụ, vào ô A2 là thời gian bước đầu, bạn có nhu cầu thêm 2 giờ vào kia, bí quyết sẽ là:

=A2 + TIME(2, 0, 0)

 

*

Công thức nhằm cùng bất kỳ thời gian nào (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 tiếng vào ô A2, nhập phương pháp sau:

=A2 + (28/24)

*

Để trừ đi một vài giờ đồng hồ, áp dụng bí quyết tựa như nhỏng nỗ lực vết + bằng lốt –

lấy một ví dụ, trừ ô A2 cho 3h, nhập một trong số bí quyết 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ách làm trước tiên.

Cách cộng/trừ phút ít vào thời gian rứa thể

Cộng/trừ bên dưới 60 phút

Sử dụng hàm TIME nhằm cùng trừ số phút ít bạn có nhu cầu vào tđê mê số sản phẩm công nghệ 2:

=Start time + TIME(0, N minutes, 0)

Đây là 1 trong vài cách làm thực tế để tính số phút ít vào Excel:

Công trăng tròn phút í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ừ bên trên 60 phút

Trong phxay tính của chúng ta, chia số phút mang đến 1440 – số phút ít trong 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 ít, 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 hạn vậy thể

Để cộng thêm số giây ít 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, áp dụng công thức:

=Start time + (N seconds / 86400)

Để trừ số giây, tận dụng bí quyết tương tự như tuy nhiên cụ lốt + thành vệt -.

Trên trang tính Excel, bí quyết trông nhỏng 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 hạn vào Excel

Phxay cộng Excel thường xuyên thực hiện hàm SUM, với vận dụng định hình tương xứng đến kết quả.

Giả sử các bạn gồm một vài thời gian trong cột B cùng bạn có nhu cầu cộng bọn chúng với nhau. Quý Khách viết bí quyết SUM như sau =SUM(B2:B4), hiệu quả hiển thị theo định dạng khoác định nhỏng hh:mm:ss

 

*

Trong một vài trường hợp, định hình thời hạn mang định có thể ổn định, dẫu vậy nhiều lúc bạn muốn 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. Lúc kia, chúng ta không đề xuất phép tính nào cả, các bạn chỉ việc định hình lại thời gian đến ô tính chứa cách làm SUM.

Nhấp yêu cầu vào ô tính và chọn Format Cells từ bỏ danh sách tùy chọn, dìm Ctrl + 1 để mở hộp thoại Format Cells. Chọn Custom trường đoản cú Category danh sách với gõ một trong số định hình sau vào hộp Type:

Hiển thị thời gian bên dưới dạng phút ít cùng giây: :ss

Hiển thị thời gian dưới dạng giây:

Kết đúng như sau:

*

Cách cộng qua 24 tiếng trong Excel

Để tính toán tác dụng hơn 24 giờ, bạn có thể sử dụng công thức SUM, với lựa chọn 1 trong các format sau:

Định dạngHiển thịGiải thích
:mm30:10Giờ với phút
:mm:ss30:10:20Giờ, phút cùng giây
“hours”, milimet “minutes”, ss “seconds”30 hours, 10 minutes, trăng tròn seconds
d h:mm:ss1 06:10:20Ngày, giờ đồng hồ, phút ít với giây
d “day” h:mm:ss1 day 06:10:20
d “day,” h “hours,” m “minutes and” s “seconds”1 day, 6 hours, 10 minutes & 20 seconds

Để coi công dụng format vào trang tính thực, xem hình sau đây (cách làm SUM được nhập lệ ô A9 tới ô A13:

=SUM($B$2:$B$4)

Chụ ý. Định dạng thời gian chỉ vận dụng đến số dương. Nếu tác dụng tính toán là số âm, nghĩa là bạn đem giá trị nhỏ tuổi rộng trừ đến quý giá lớn hơn, công dụng vẫn hiển thị #####.

*

Cách hiển thị khoảng thời hạn lớn hơn 24 tiếng, 60 phút ít, 60 giây?

Để hiện thị lên một khoảng chừng thời gian lớn hơn 24 giờ đồng hồ, 60 phút, 60 giây, hãy sử dụng custom time format địa điểm nhưng mà mã đơn vị chức năng thời hạn tương ứng phía trong dấu ngoặc vuông, ví dụ điển hình như , , tuyệt . Dưới đó là công việc khuyên bảo chi tiết:

Chọn một giỏi các ô nhưng mà bạn có nhu cầu định dạng.Nhấp con chuột đề nghị vào các ô được lựa chọn rồi lại nhấn vào phải vào Format Cells, tốt bạn cũng có thể nhấn Ctrl + 1, làm việc này sẽ giúp msinh hoạt vỏ hộp thoại Format Cells.Trong Number tab, bên dưới Category, hãy chọn Custom rồi gõ một trong số những định dạng thời hạn tiếp sau đây trong vỏ hộp Type:

Lớn hơn 24 giờ: :mm:ss hay :mm

Lớn hơn 60 phút: :ss

Lớn hơn 60 giây:

Hình ảnh chụp screen dưới đây cho thấy định hình thời gian “to hơn 24 giờ” bên trên thực tế:

Dưới đấy là một vài ba định dạng khác. Chúng ta rất có thể sử dụng nó để hiển thị phần nhiều khoảng tầm thời hạn lớn hơn chiều dài đơn vị thời hạn chuẩn chỉnh.

Miêu tảMã định dạng
Tổng số giờ
Số giờ và phút:mm
Số giờ, phút ít, giây:mm:ss
Tổng số phút
Số phút ít cùng giây:ss
Tổng số giây

Khi được thực hiện cho tài liệu chủng loại của bọn họ (Thời gian tổng cộng 50:40 trong ảnh chụp screen trên), đầy đủ format thời hạn này vẫn đến hiệu quả nlỗi sau:

ABC
1Miêu tảThời gian hiển thịĐịnh dạng
2Số giờ50
3Số giờ đồng hồ với phút50:40:mm
4Số giờ đồng hồ, phút, giây50:40:30:mm:ss
5Số phút3040
6Sốphút với giây3040:30:ss
7Số giây182430

Để phần nhiều khoảng tầm thời hạn được hiển thị làm việc bên trên dễ nắm bắt hơn đối với người dùng, chúng ta có thể bổ sung cập nhật đơn vị chức năng thời hạn bằng các từ viết tắt tương ứng, ví dụ:

ABC
1Miêu tảThời gian hiển thịĐịnh dạng
2Số tiếng và phút50 tiếng và 40 phút “thời gian và” milimet “số phút”
3Số giờ đồng hồ, phút ít, giây50h.40m.30s “h.” mm “m.” ss “s.”
4Số phút3040 phút “số phút”
5Số phút ít cùng giây3040 phút và 30 giây “số phút ít và” ss “số giây”
6Số giây182430 giây “số giây”

Lưu ý. Mặc cho dù số đông khoảng tầm thời hạn trên trông y như chuỗi cam kết trường đoản cú, cơ mà chúng vẫn chính là quý giá số, chính vì định dạng văn bản số trong Excel chỉ đổi khác về bề ngoài chứ bản chất ko đổi khác. Vì nạm, chúng ta có thể tự do thoải mái cộng giỏi trừ số đông khoảng thời gian đã có được format một bí quyết thông thường, hãy tđắm say chiếu chúng trong công thức và sử dụng trong số phép tính khác.

Cách cùng trừ thời hạn lớn hơn 24 giờ, 60 phút, 60 giây

Để cùng khoảng tầm thời hạn ước muốn cho 1 khoảng chừng thời gian mang lại sẵn, hãy mang khoảng thời gian, phút, tốt giây mà lại bạn có nhu cầu cùng mang lại đơn vị chức năng thời gian tương ứng vào một ngày (24 giờ, 1440 phút ít, 86400 giây), rồi cùng số tmùi hương cho thời gian bước đầu.

Cộng hơn 24 giờ đồng hồ.

Thời gian bắt đầu + (N/24)

Cộng hơn 60 phút ít.

Thời gian bắt đầu + (N/1440)

Cộng hơn 60 giây.

Thời gian ban đầu + (N/86400)

Trong đó, N là khoảng thời gian, phút ít, hay giây mà bạn muốn cùng.

Đây là 1 trong vài ba ví dụ bí quyết bên trên thực tế:

Để cùng 45 giờ đến thời hạn bắt đầu trong ô A2:

=A2+(45/24)

Để cộng 100 phút ít mang lại thời hạn ban đầu trong ô A2:

=A2+(100/1440)

Để cộng 200 giây đến thời gian bước đầu vào ô A2:

=A2+(200/86400)

hoặc là, bạn có thể nhập khoảng chừng thời gian đề nghị cộng vào một ô cá biệt rồi tsi chiếu ô đó trong phương pháp bao gồm dạng nlỗi trong ảnh chụp màn hình dưới đây:

Để trừ khoảng chừng thời hạn vào Excel, hãy sử dụng phương pháp tương tự như tuy vậy hãy rứa vệt cùng bởi dấu trừ nhé!

Trừ rộng 24 giờ

Thời gian bắt đầu – (N/24)

Trừ hơn 60 phút ít.

Thời gian bắt đầu – (N/1440)

Trừ hơn 60 giây

Thời gian ban đầu – (N/86400)

Ảnh chụp màn hình hiển thị sau đây hiển thị những kết quả:

Lưu ý:

Nếu khoảng thời gian được tính toán gồm dạng số thập phân, hãy sử dụng format thời gian/ngày để format ô.Nếu sau khi thực hiện định dạng cùng ô hiển thị #####, thì thường thì ô không đủ rộng để hiển thị cực hiếm thời hạn trong ngày. Để cách xử lý vấn đề đó, hãy mở rộng chiều lâu năm ô bằng cách nhấp đúp con chuột giỏi kéo khung bên bắt buộc của cột.

Đó là tổng thể cách để những chúng ta cũng có thể phương pháp cùng trừ thời gian trong excel. Nếu còn bất kể thắc mắc gì thì đừng không tự tin ngần cmt xuống dưới sẽ được câu trả lời nhanh hao độc nhất vô nhị nhé!