Trong bài viết này, Học Excel Online sẽ lý giải những cách làm hữu ích để cộng/trừ thời gian, chênh lệch thời gian, đếm giờ; giải pháp cộng trừ thời hạn trong 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 trong ExcelCách tính và hiển thị thời gian âm vào 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 to 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 gian trong Excel hay được format số thập phân để trông kiểu như thời gian. Vày chúng là số, nên bạn có thể cộng trừ thời gian 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 trúc dữ liệu, chênh lệch thời gian thực tế gồm thể có không ít dạng không giống nhau, ví dụ:

Công thứcGiải thích
=A2-B2Tí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, ko kể chênh lệch ngày tháng nếu như ô tính cất cả giá trị ngày tháng với thời gian

Trong khối hệ thống Excel, thời gian bộc lộ ở phần thập phân, các bạn sẽ có tác dụng như sau:

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

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

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

Để áp dụng định dạng thời gian, dìm Ctrl + 1 để mở vỏ hộp thoại Format Cells, lựa chọn Custom từ Category menu 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 hạn và mã thời gian hoạt động như nắm nào vào bảng tính thực sự. Với Start time vào cột A cùng End time trong cột B, chúng ta 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 biệt tùy vào định dạng thời gian trong từng cột:

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

Chú ý. Nếu thời hạn chênh lệch được hiển tỉnh thành #####, nghĩa là ô chứa phương pháp không đủ rộng nhằm hiển thị hết thời hạn hoặc công dụng phép tính là 1 trong 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 nghệ 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 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 thân 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”)
*
3 – phương pháp tính thời hạn trong Excel

Chú ý.

Kết trái của hàm TEXT luôn 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 vào hình trên. Vào vài tình huống nhất định, điều này hoàn toàn có thể là một tinh giảm vì 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 thân 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), chúng ta 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 ban đầu trong ô A2 với thời gian ngừng trong ô B2, bạn hãy thực hiện phép tính đơn giản là B2-A2 để đo lường và thống kê chênh lệch thời gian, tiếp nối nhân cùng với 24.

=(B2-A2) * 24

Để gồm số Complete hours, thực hiện hàm INT để làm cho tròn cho số nguyên hần nhất:

=INT((B2-A2) * 24)

*
4 – giải pháp tính thời hạn trong Excel

 

Tổng số phút giữa 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 rất có thể cho tác dụng âm hoặc dương, công dụng âm xẩy ra khi thời gian kết thúc lớn rộng thời gian bắt đầu như hàng 5:

=(B2-A2)*1440

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

 

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 hạn với 86400

=(End time – Start time) * 86400

Trong ví dụ, bí quyết sẽ như sau:

=(B2-A2)* 86400

*
6 – bí quyết tính thời hạn trong Excel

Chú ý. Định dạng General nên được tùy chỉnh thiết lập cho ô tính chứa cách làm tính chênh lệch thời gian để hiệu quả hiển thị đúng.

Công thức 4. Tính chênh lệch thời hạn trong thuộc một đơn vị thời hạn và quăng quật qua các đơn vị khác

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

Chênh lệch giờ, bỏ lỡ phút với 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ờ và phút:

=SECOND(B2-A2)

Khi sử dụng hàm HOUR, MINUTE, SECOND, hãy lưu giữ rằng tác dụng không được vượt quá 24 giờ, 60 phút hoặc 60 giây.

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

Chú ý. Nếu thời gian hoàn thành lớn hơn thời gian bắt đầu, cụ thể nếu hiệu quả là số âm, cách làm 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 bước đầ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à Time. Trường vừa lòng khoảng thời gian 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 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 điểm bắt đầu, cách làm =NOW()-A2 cho tác dụng sau với cột B được định dạng thời hạn phù hợp, trong ví dụ này nó có dạng h:mm

*
8 – bí quyết tính thời gian trong Excel

 

Trong trường thích hợp khoảng thời gian trôi qua vượt thừa 24 giờ, áp dụng một trong những định dạng thời hạn sau, lấy ví dụ như d ‘days’ h:mm:ss như trong hình sau:

*
9 – bí quyết tính thời gian trong Excel

Nếu thời gian bước đầu chỉ gồm giờ phút mà không có ngày, bạn cần sử dụng hàm Time để giám sát và đo lường khoảng thời hạn trôi đi một cách thiết yếu xác. Ví dụ, cách làm sau trả về khoảng thời hạn từ ô A2 đến giờ.

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

*
10- biện pháp tính thời hạn trong Excel

Chú ý. Khoảng thời hạn trôi qua sẽ không còn được update theo thời gian thực tế, nó chỉ cập nhật khi bảng tính được mở ra hoặc giám sát lại. Để update công thức, dìm Shift + F9 để tính lại trang tính hoặc dìm F9 để 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 và XX seconds”

Bạn sử dụng các hàm HOUR, MINUTE SECOND để lấy 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 các hàm vào thuộc một cách làm cùng những chuỗi text như sau :

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

 

*

Để ẩn quý giá 0, lồng tứ hàm IF vào đó:

=IF(INT(B2-A2)>0, INT(B2-A2) và ” 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”,””)

 

*

Ngoài ra, chúng ta cũng có thể tính toán chênh lệch thời gian bằng phương pháp lấy thời gian ban đầu trừ thời gian xong xuôi (ví dụ = B2-A2) rồi tùy chỉnh thiết lập định dạng sau mang đến ô tính:

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

*
11 – biện pháp tính thời hạn trong Excel

Cách tính với hiển thị thời gian âm trong Excel

Khi tính toán chênhh lệch cúng gian, bạn có thể bắt gặp lỗi ##### vì độ chênh lệch này âm. Mặc dù nhiên, cũng có nhiều phương pháp để hiển thị kết quả âm vào 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à đánh dấu chọn Use 1904 date system.

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

Nhấp OK để lưu setup mới, giá trị âm sẽ tiến hành hiển thị như sau:

*

Phương pháp 2. Thống kê giám sát thời gian âm bởi công thức

Nếu không muốn sử dụng phương pháp 1, bạn cũng có thể sử dụng cách làm 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 bí quyết sẽ khám nghiệm chênh lệch thời gian (A2-B2) có lớn hơn 0 hay là không và nó tất cả hiện kết quả hay không. Trường hợp chênh lệch bé hơn 0, công thức thứ nhất sẽ tính trị hoàn hảo nhất rồi thêm vết trừ. Công thức thứ hai cho hiệu quả tương tự bởi định dạng thời hạn âm “-h:: mm”.

*

Chú ý. Hãy lưu giữ rằng cách thức đầu tiên cho ra kết quả âm là quý giá số âm, hiệu quả hàm TEXT luôn luôn là chuỗi văn bản và không thể thực hiện trong phép toán hoặc phương pháp khác.

Xem thêm:

Cộng/trừ thời hạn trong Excel

Về cơ bản, tất cả 2 cách cộng/trừ thời gian trong Excel:

Sử dụng hàm TIMESử dụng phép toán dựa trên 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 việc tính toán thuận tiện hơn, mặc dù nó không cho phép việc cộng trừ hơn 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 hạn lớn hơn, hãy sử dụng những phép toán dưới đây.

Tham khảo ngay: Giáo trình Excel 2020

Cộng/trừ giờ 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ụ, vào ô A2 là thời hạn bắt đầu, bạn có nhu cầu thêm 2 giờ vào đó, phương pháp sẽ là:

=A2 + TIME(2, 0, 0)

 

*

Công thức nhằm cộng ngẫu nhiên 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ách làm sau:

=A2 + (28/24)

*

Để trừ đi một số giờ, thực hiện công thức tương tự như như nạm dấu + bằng dấu –

Ví dụ, trừ ô A2 mang đến 3 giờ, nhập một trong các 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, áp dụng 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ừ 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ố trang bị 2:

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

Đây là một vài công thức thực tiễn để tính số phút trong Excel:

Công 20 phút vào A2: =A2 + TIME(0,20,0)

Trừ A2 cho 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 mang đến 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ú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 hạn cụ thể

Để thêm vào đó số giây thấp hơn 60 vào thời hạn cụ thể, sử dụng 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ông thức tương tự nhưng rứa dấu + thành vệt -.

Trên trang tính Excel, bí quyết 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 lại 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 Excel

Phép cộng Excel thường sử dụng hàm SUM, và vận dụng định dạng tương xứng cho kết quả.

Giả sử bạn có một vài thời gian trong cột B và bạn muốn cộng bọn chúng với nhau. Bạn viết công thức SUM như sau =SUM(B2:B4), hiệu quả hiển thị theo format mặc định như hh:mm:ss

 

*

Trong một vài trường hợp, định dạng thời gian mặc định hoàn toàn có thể ổn, tuy thế đôi khi bạn muốn hiển thị tổng thời gian dưới dạng phút và giây, hoặc chỉ hiển thị giây. Lúc đó, các 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 bí quyết SUM.

Nhấp phải vào ô tính và chọn Format Cells từ list tùy chọn, dìm Ctrl + 1 để mở 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 và 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 giờ đồng hồ trong Excel

Để tính toán tác dụng hơn 24 giờ, chúng ta có thể sử dụng phương pháp SUM, và lựa chọn một trong các định dạng sau:

Định dạngHiển thịGiải thích
:mm30:10Giờ cùng phút
:mm:ss30:10:20Giờ, phút với giây
“hours”, mm “minutes”, ss “seconds”30 hours, 10 minutes, trăng tròn seconds
d h:mm:ss1 06:10:20Ngày, giờ, phút và 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

Để xem kết quả định dạng trong trang tính thực, xem hình tiếp sau đâ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ỉ áp 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á bán trị nhỏ tuổi hơn trừ đến giá trị phệ hơn, kết quả sẽ hiển thị #####.

*

Cách hiển thị khoảng tầm thời gian lớn hơn 24 giờ, 60 phút, 60 giây?

Để hiện thị một khoảng tầm thời gian to hơn 24 giờ, 60 phút, 60 giây, hãy sử dụng custom time format nơi mà lại mã solo vị thời hạn tương ứng nằm trong dấu ngoặc vuông, ví dụ điển hình như , , xuất xắc . Sau đây là quá trình hướng dẫn đưa ra tiết:

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

Lớn rộng 24 giờ: :mm:ss xuất xắc :mm

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

Lớn rộng 60 giây:

Ảnh chụp màn hình dưới đây cho biết thêm định dạng thời hạn “lớn rộng 24 giờ” bên trên thực tế:

Dưới đây là một vài định dạng khác. Chúng ta cũng có thể sử dụng nó để hiển thị những khoảng chừng thời gian lớn hơn chiều dài solo vị thời hạn chuẩn.

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

Khi được áp dụng cho tài liệu mẫu của chúng ta (Thời gian tổng số 50:40 trong hình ảnh chụp màn hình hiển thị trên), phần đông định dạng thời hạn này vẫn cho hiệu quả như sau:

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

Để các khoảng thời hạn được hiển thị sống trên dễ hiểu hơn đối với người dùng, bạn cũng có thể bổ sung đối chọi vị thời gian 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ố giờ cùng phút50 giờ cùng 40 phút “số giờ và” mm “số phút”
3Số giờ, phút, giây50h.40m.30s “h.” milimet “m.” ss “s.”
4Số phút3040 phút “số phút”
5Số phút cùng giây3040 phút và 30 giây “số phút và” ss “số giây”
6Số giây182430 giây “số giây”

Lưu ý. Mặc dù rất nhiều khoảng thời hạn trên trông giống như chuỗi ký kết tự, dẫu vậy chúng vẫn chính là giá trị số, cũng chính vì định dạng chữ số vào Excel chỉ biến đổi về hình thức chứ thực chất không cố gắng đổi. Bởi vì thế, bạn có thể tự vị 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 chúng trong phương pháp 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 mong cho một khoảng thời hạn cho sẵn, hãy lấy số giờ, phút, hay giây mà bạn có nhu cầu cộng cho đối chọi vị thời gian tương ứng vào một ngày (24 giờ, 1440 phút, 86400 giây), rồi cộng số yêu mến cho thời gian 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 bắt đầu + (N/1440)

Cộng rộng 60 giây.

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

Trong đó, N là số giờ, phút, hay giây mà bạn có nhu cầu cộng.

Đây là 1 trong vài ví dụ cách làm trên thực tế:

Để cộng 45 giờ cho thời gian ban đầu trong ô A2:

=A2+(45/24)

Để cộng 100 phút cho thời gian bắt đầ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à, bạn có thể nhập khoảng thời gian cần cộng vào trong 1 ô đơn lẻ rồi tham chiếu ô kia trong công thức tất cả 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 áp dụng công thức tương tự như nhưng hãy nắm dấu cộng bằng dấu trừ nhé!

Trừ rộng 24 giờ

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

Trừ rộng 60 phút.

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

Trừ rộng 60 giây

Thờ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 đo lường và 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ì ô cảm thấy không được rộng để hiển thị giá bán trị thời gian 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 phương pháp nhấp đúp chuột hay kéo form bên buộc phải 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. 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 và để được giải đáp nhanh nhất có thể nhé!