Cách trừ thời gian trong excel

     

Trong bài viết này, Học Excel Online sẽ giải đáp những bí quyết 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 gian trong excel.

Bạn đang xem: Cách trừ 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 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 gian trong Excel

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

Như chúng ta đã biết, thời hạn trong Excel thường xuyên được format số thập phân để trông kiểu như thời gian. Vị chúng là số, nên bạn cũng có thể cộng trừ thời hạn 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 kết cấu dữ liệu, chênh lệch thời hạn thực tế bao gồm thể có không ít dạng khác 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 giá trị trong ô A2 với B2
=TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”)Tính toán chênh lệch thời hạn giữa hai thời gian 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, không tính chênh lệch ngày tháng nếu như ô tính chứa cả quý hiếm ngày tháng cùng thời gian

Trong hệ thống Excel, thời gian biểu hiện ở phần thập phân, bạn sẽ có công dụng như sau:

*
1 – giải pháp tính thời gian trong Excel

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

Mã thời gianGiải thích
hSố giờ đồng hồ 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ụ như 4:10:20.

Để áp dụng định dạng thời gian, nhận Ctrl + 1 để mở vỏ hộp thoại Format Cells, chọn Custom từ Category menu và gõ mã thời gian vào vỏ hộp Type.

Bây giờ, nhằm xem phương pháp tính chênh lệch thời gian và mã thời gian chuyển động như cụ nào vào bảng tính thực sự. Với Start time vào cột A cùng End time vào cột B, chúng ta có thể sao chép công thức vào cột C với 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 gian trong từng cột:

*
2 – biện pháp tính thời gian trong Excel

Chú ý. Nếu thời hạn chênh lệch được hiển đô thị #####, tức thị ô chứa phương pháp không đủ rộng để hiển thị hết thời gian hoặc kết quả 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 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 hai thời gian: =TEXT(B2-A2, “h”)Tính toán chênh lệch giờ và phút thân hai thời gian: =TEXT(B2-A2, “h:mm”)Tính toán chênh lệch giờ, phút cùng giây giữa hai thời gian: =TEXT(B2-A2, “h:mm:ss”)
*
3 – phương pháp tính thời gian trong Excel

Chú ý.

Kết quả 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 trong hình trên. Vào vài tình huống nhất định, điều này rất có thể là một tiêu giảm vì bạn sẽ không thể sử dụng hiệu quả này để thường xuyên 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 thân hai thời hạn bằng một đơn vị (giờ, phút, giây), các bạn cần tiến hành các phép tính sau.

Phép tính giờ giữa hai thời gian

=(End time – Start time) * 24

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

=(B2-A2) * 24

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

=INT((B2-A2) * 24)

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

 

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

=(End time – Start time) * 1440

Công thức rất có thể cho hiệu quả âm hoặc dương, tác dụng âm xảy ra khi thời gian ngừng lớn rộng thời gian ban đầu như mặt hàng 5:

=(B2-A2)*1440

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

 

Tổng số giây thân 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ụ, cách làm sẽ như sau:

=(B2-A2)* 86400

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

Chú ý. Định dạng General nên được thiết lập cấu hình cho ô tính chứa phương pháp 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 hạn trong cùng một 1-1 vị thời hạn và vứt 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 solo vị, vứt qua những đơn vị thời gian khác, hãy áp dụng một trong những hàm sau:

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

=HOUR(B2-A2)

Chênh lệch phút, bỏ qua giờ với giây:

=MINUTE(B2-A2)

Chênh lệch giây, làm 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 công 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 ngừng lớn hơn thời hạn bắt đầu, ví dụ 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 gian trôi qua kể từ thời gian bắt đầu đến bây giờ

Để tính thời hạn đã trôi qua, chúng ta cũng có thể sử dụng hàm Now và Time. Trường đúng theo khoảng thời gian trôi qua dưới 24 giờ, bạn chỉ cần sử dụng hàm NOW để lấy tác dụng ngày mon và thời hạn hiện tại, kế tiếp trừ đi ngày và thời gian bắt đầu.

Giả sử ô A2 chứa thời gian bắt đầu, bí quyết =NOW()-A2 cho kết quả sau cùng với cột B được định dạng thời hạn phù hợp, trong lấy một ví dụ này nó gồm dạng h:mm

*
8 – biện pháp tính thời gian trong Excel

 

Trong trường vừa lòng khoảng thời hạn trôi qua vượt thừa 24 giờ, thực hiện một trong các định dạng thời hạn sau, ví dụ d ‘days’ h:mm:ss như vào hình sau:

*
9 – bí quyết tính thời hạn 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 để đo lường khoảng thời gian trôi đi một cách chủ yếu xác. Ví dụ, bí quyết sau trả về khoảng thời hạn từ ô A2 cho giờ.

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

*
10- cách tính thời gian trong Excel

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

Bạn sử dụng những hàm HOUR, MINUTE SECOND nhằm lấy hiệu quả thời gian tương tứng cùng hàm INT nhằm tính chênh lệch ngày.Sau đó, bạn lồng ghép những hàm vào cùng một công thức cùng các chuỗi text như sau :

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

 

*

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

=IF(INT(B2-A2)>0, INT(B2-A2) & ” days, “,””) và IF(HOUR(B2-A2)>0, HOUR(B2-A2) và ” hours, “,””) và 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 bắt đầu trừ thời gian xong xuôi (ví dụ = B2-A2) rồi thiết lập định dạng sau cho ô tính:

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

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

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

Khi giám sát và đo lường chênhh lệch bái gian, bạn cũng có thể bắt chạm mặt lỗi ##### vì độ chênh lệch này âm. Tuy nhiên, cũng đều có nhiều cách để hiển thị tác dụng âm vào Excel.

Phương pháp 1. Biến đổi hệ thông tháng ngày 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.

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

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

*

Phương pháp 2. đo lường thời gian âm bằng công thức

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

*

Chú ý. Hãy ghi nhớ rằng phương pháp đầu tiên mang lại ra công dụng âm là giá trị số âm, kết quả hàm TEXT luôn luôn là chuỗi văn bạn dạng và không thể áp dụng trong phép toán hoặc công thức khác.

Xem thêm: Dạy Cách Vẽ Khuôn Mặt Người Bằng Bút Chì, Cách Để Vẽ Gương Mặt

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

Về cơ bản, gồm 2 bí quyết 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 việc tính toán thuận tiện hơn, tuy nhiên nó không có thể chấp nhận được 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 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 để cộng thêm dưới 24 giờ

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

Ví dụ, vào ô A2 là thời gian bắt đầu, bạn muốn thêm 2 giờ vào đó, bí quyết sẽ là:

=A2 + TIME(2, 0, 0)

 

*

Công thức nhằm 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 tiếng vào ô A2, nhập bí quyết sau:

=A2 + (28/24)

*

Để trừ đi một vài giờ, sử dụng công thức tương tự như ráng dấu + bởi dấu –

Ví dụ, trừ ô A2 đến 3 giờ, nhập một trong những 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, sử 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ừ dưới 60 phút

Sử dụng hàm TIME để cùng trừ số phút bạn muốn vào tham số đồ vật 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 trong Excel:

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

Trừ A2 mang lại 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 vào một ngày cùng thêm thời gian ban đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút, nỗ lực dấu + thành lố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ể

Để thêm vào đó 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)

Để 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ựa như nhưng thế dấu + thành dấu -.

Trên trang tính Excel, phương pháp 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 áp dụng 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 chúng với nhau. Chúng ta viết công thức SUM như sau =SUM(B2:B4), công dụng hiển thị theo định dạng mặc định như hh:mm:ss

 

*

Trong một vài trường hợp, định dạng thời hạn mặc định hoàn toàn có thể ổn, dẫu vậy đô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 đó, bạn không nên phép tính như thế nào cả, bạn chỉ việc định dạng lại thời gian cho ô tính chứa cách làm SUM.

Nhấp bắt buộc vào ô tính và lựa chọn Format Cells từ danh sách tùy chọn, nhận Ctrl + 1 để mở vỏ hộp thoại Format Cells. Lựa 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 cùng 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 toán công dụng hơn 24 giờ, chúng ta có thể sử dụng công thức SUM, và chọn 1 trong các định dạng sau:

Định dạngHiển thịGiải thích
:mm30:10Giờ và phút
:mm:ss30:10:20Giờ, phút với giây
“hours”, mm “minutes”, ss “seconds”30 hours, 10 minutes, 20 seconds
d h:mm:ss1 06:10:20Ngày, giờ, phút cùng 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 tác dụng đị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ỉ áp dụng cho số dương. Nếu hiệu quả tính toán là số âm, nghĩa là các bạn lấy giá chỉ trị nhỏ hơn trừ đến giá trị béo hơn, kết quả sẽ hiển thị #####.

*

Cách hiển thị khoảng thời gian to 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à mã đối kháng vị thời hạn tương ứng phía bên trong dấu ngoặc vuông, ví dụ điển hình như , , tuyệt . Tiếp sau đây là quá trình hướng dẫn bỏ ra tiết:

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

Lớn rộng 24 giờ: :mm:ss tốt :mm

Lớn rộng 60 phút: :ss

Lớn rộng 60 giây:

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

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

Miêu tảMã định dạng
Tổng số giờ
Số giờ với 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 thực hiện cho tài liệu mẫu của họ (Thời gian tổng số 50:40 trong ảnh chụp màn hình trên), đều định dạng thời gian này đang cho hiệu quả như sau:

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

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

Lưu ý. Mặc dù phần nhiều khoảng thời hạn trên trông y hệt như chuỗi ký tự, tuy nhiên chúng vẫn luôn là giá trị số, bởi vì định dạng văn bản số trong Excel chỉ biến hóa về vẻ ngoài chứ thực chất không rứa đổi. Vì chưng thế, chúng ta cũng có thể tự vì cộng giỏi trừ mọi khoảng thời gian đã được định hình một bí quyết bình thường, hãy tham chiếu chúng trong công thức và sử dụng trong các 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 ao ước cho một khoảng thời gian cho sẵn, hãy lấy số giờ, phút, xuất xắc giây mà bạn có nhu cầu cộng cho solo 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 quý 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 hơn 60 giây.

Thời gian bước đầu + (N/86400)

Trong đó, N là số giờ, phút, tuyệt giây mà bạn muốn 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 bước đầu trong ô A2:

=A2+(45/24)

Để cộng 100 phút mang đến thời gian bắt đầu trong ô A2:

=A2+(100/1440)

Để cùng 200 giây đến thời gian ban đầu trong ô A2:

=A2+(200/86400)

Hay là, bạn có thể nhập khoảng thời hạn cần cộng vào trong 1 ô riêng biệt rồi tham chiếu ô đó trong công thức gồm dạng như trong ảnh chụp screen dưới đây:

Để trừ khoảng thời gian trong Excel, hãy thực hiện công thức tương tự như nhưng hãy nuố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.

Xem thêm: Cách Làm Kem Ký Ngày Xưa - Tổng Hợp 39 Cách Làm Kem Ký Tại Nhà Đơn Giản Ngon

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

Trừ rộng 60 giây

Thời gian bước đầu – (N/86400)

Ảnh chụp màn hình dưới đây hiển thị các kết quả:

Lưu ý:

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

Đó là toàn bộ phương pháp để các chúng ta có thể cách cộng trừ thời gian trong excel. Ví như còn bất cứ thắc mắc gì thì chớ ngại ngần cmt xuống dưới sẽ được giải đáp sớm nhất nhé!