CÁCH TRỪ THỜI GIAN TRONG EXCEL

0.1 Cách tính chênh lệch thời hạn trong Excel0.2 Cách tính và hiển thị thời hạn âm vào Excel0.3 Cộng/trừ thời hạn trong Excel

Trong bài viết này, Học Excel Online đã lý giải phần đa phương pháp có ích nhằm cộng/trừ thời hạn, chênh lệch thời hạn, đếm giờ; giải pháp cộng trừ thời gian vào excel.

Bạn đang xem: Cách trừ thời gian trong excel

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ỏng các bạn sẽ biết, thời gian trong Excel hay được format số thập phân để trông tương đương thời hạn. Vì chúng là số, yêu cầu bạn cũng có thể cùng trừ thời hạn như các giá trị số khác.

Công thức đơn giản độc nhất nhằm tính chênh lệch thời hạn là:

=End time – Start time


*

*

Tùy vào cấu trúc dữ liệu, chênh lệch thời hạn thực tiễn rất có thể có khá nhiều dạng khác nhau, ví dụ:

Công thứcGiải thích
=A2-B2Tính tân oán 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 gian thân hai thời hạn thế 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 giữa nhì quý hiếm vào ô A2 cùng B2, quanh đó chênh lệch ngày tháng nếu ô tính chứa cả giá trị tháng ngày với thời gian

Trong hệ thống Excel, thời gian thể hiện ở chỗ thập phân, bạn sẽ có hiệu quả nhỏng sau:


*

1 – cách tính thời gian 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. Để góp bọn chúng cung cấp thông tin hữu dụng hơn, chúng ta cũng có thể vận dụng format thời hạn với cùng một trong số mã sau đây:

Mã thời gianGiải thích
hSố tiếng trôi qua, ví dụ 4
h:mmSố giờ đồng hồ với số phút trôi qua, ví dụ 4:10
h:mm:ssSố tiếng, 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 gian, thừa nhận Ctrl + 1 nhằm msinh sống vỏ hộp thoại Format Cells, lựa chọn Custom từ bỏ Category list cùng gõ mã thời gian vào hộp Type.

Bây tiếng, giúp thấy bí quyết tính chênh lệch thời hạn và mã thời hạn hoạt động ra làm sao trong bảng tính thực sự. Với Start time vào cột A với End time trong cột B, bạn cũng có thể xào nấu cách làm vào cột C với E:

=$B2-$A2

Thời gian chênh lệch được hiển thị khác biệt tùy thuộc theo định dạng thời gian trong những cột:


*

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


Chú ý. Nếu thời gian chênh lệch được hiển thành thị #####, nghĩa là ô chứa phương pháp không đủ rộng lớn để hiển thị hết thời hạn hoặc hiệu quả phxay tính là một quý giá âm.

Công thức 2. Tính chênh lệch thời gian bằng 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à áp dụng hàm TEXT .

Tính tân oán chênh lệch giờ thân nhì thời gian: =TEXT(B2-A2, “h”)Tính toán chênh lệch giờ đồng hồ với phút thân hai thời gian: =TEXT(B2-A2, “h:mm”)Tính tân oán chênh lệch tiếng, phút và giây giữa hai thời gian: =TEXT(B2-A2, “h:mm:ss”)
*

3 – cách tính thời hạn vào Excel


Crúc ý.

Kết quả của hàm TEXT luôn luôn là vnạp năng lượng phiên bản. Các giá trị text được căn lề trài trong những cột C, D, E vào hình bên trên. Trong vài trường hợp nhất định, vấn đề đó có thể là một giảm bớt bởi vì bạn sẽ cấp thiết áp dụng hiệu quả này để thường xuyên tính toán.

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

Để hiển thị chênh lệch thân hai thời hạn bởi một đơn vị chức năng (tiếng, phút, giây), bạn phải thực hiện các phnghiền tính sau.

Phép tính tiếng giữa nhị thời gian

=(End time – Start time) * 24

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

=(B2-A2) * 24

Để gồm số Complete hours, áp dụng hàm INT để triển khai tròn mang đến số nguim hần nhất:

=INT((B2-A2) * 24)


*

4 – cách tính thời hạn vào Excel


 

Tổng số phút ít giữa nhị thời gian:

Để tính số phút ít giữa nhì thời hạn, nhân chênh lệch thời gian với 1440

=(End time – Start time) * 1440

Công thức hoàn toàn có thể mang đến hiệu quả âm hoặc dương, hiệu quả âm xảy ra Khi thời gian ngừng lớn hơn thời hạn bắt đầu như sản phẩm 5:

=(B2-A2)*1440


*

5 – cách tính thời gian vào Excel


 

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

Để tính số giây thân hai thời hạn, nhân chênh lệch thời gian với 86400

=(End time – Start time) * 86400

Trong ví dụ, công thức đã nhỏng sau:

=(B2-A2)* 86400


*

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


Chụ ý. Định dạng General phải được tùy chỉnh cấu hình đến ô tính chứa bí quyết 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 vào và một đơn vị thời gian cùng bỏ qua các đơn vị chức năng không giống

Để tính chênh lệch giữa nhị thời gian vào và một đơn vị, bỏ qua mất những đơn vị thời gian khác, hãy thực hiện một trong các hàm sau:

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

=HOUR(B2-A2)

Chênh lệch phút, bỏ lỡ giờ đồng hồ với giây:

=MINUTE(B2-A2)

Chênh lệch giây, bỏ lỡ giờ đồng hồ với phút:

=SECOND(B2-A2)

Lúc áp dụng hàm HOUR, MINUTE, SECOND, hãy đừng quên tác dụng ko được quá thừa 24 tiếng, 60 phút hoặc 60 giây.


*

7 – cách tính thời hạn vào Excel


Crúc ý. Nếu thời hạn hoàn thành to hơn thời gian bước đầu, cụ thể trường hợp tác dụng là số âm, cách làm đã hiển thị lỗi #NUM!

Công thức 5. Tính thời gian trôi qua Tính từ lúc thời hạn bước đầu mang đến bây giờ

Để tính thời hạn sẽ trôi qua, bạn có thể áp dụng hàm Now với Time. Trường hợp khoảng chừng thời hạn trôi qua dưới 24 giờ đồng hồ, bạn chỉ việc sử dụng hàm NOW để mang công dụng tháng ngày với thời hạn hiện nay, tiếp đến trừ đi ngày và thời gian bắt đầu.

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


*

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


 

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


*

9 – cách tính thời gian vào Excel


Nếu thời gian bắt đầu chỉ gồm giờ phút nhưng không tồn tại ngày, bạn cần áp dụng hàm Time để tính tân oán khoảng chừng thời hạn trôi đi một bí quyết đúng mực. Ví dụ, công thức sau trả về khoảng thời gian từ ô A2 đến giờ đồng hồ.

Xem thêm: Phân Biệt Các Kiểu Dữ Liệu Trong Excel, Các Loại Dữ Liệu Trong Mẫu Dữ Liệu

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


*

10- cách tính thời hạn trong Excel


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 tế, nó chỉ cập nhật Khi bảng tính được mlàm việc lại hoặc tính tân oán lại. Để update phương pháp, nhấn Shift + F9 để tính lại trang tính hoặc nhận F9 nhằm 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 hạn “XX days, XX hours, XX minutes và XX seconds”

quý khách sử dụng các hàm HOUR, MINUTE cùng SECOND để đưa hiệu quả thời hạn tương tứng với hàm INT nhằm tính chênh lệch ngày.Sau kia, 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 nlỗi sau :

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

 

*

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

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

 

*

Dường như, bạn có thể tính tân oán chênh lệch thời hạn bằng cách mang thời gian bắt đầu trừ thời hạn ngừng (ví dụ = B2-A2) rồi tùy chỉnh cấu hình định hình sau đến ô tính:

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


*

11 – phương 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 tân oán chênhh lệch thờ gian, chúng ta có thể bắt gặp lỗi ##### vị độ chênh lệch này âm. Tuy nhiên, cũng có tương đối nhiều cách để hiển thị tác dụng â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 tkhô hanh cuốn xuống When calculating this workbook và khắc ghi chọn Use 1904 date system.


*

12 – phương pháp tính thời hạn vào Excel


Nhấp OK nhằm lưu lại cài đặt bắt đầu, giá trị âm sẽ được hiển thị như sau:

*

Phương thơm pháp 2. Tính toán thời gian âm bởi công thức

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

*

Chú ý. Hãy nhớ là phương pháp trước tiên đã tạo ra công dụng âm là quý giá số âm, hiệu quả hàm TEXT luôn luôn là chuỗi văn bản và cần thiết áp dụng trong phnghiền toán hoặc cách làm khác.

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

Về cơ bạn dạng, có 2 bí quyết cộng/trừ thời gian trong Excel:

Sử dụng hàm TIMESử dụng phnghiền toán dựa trên khoảng thời gian (24), số phút ít (1440), số giây (86400) trong một ngày.

Hàm TIME(hour, minute, second) giúp vấn đề tính toán thù dễ dàng rộng, mặc dù nó không có thể chấp nhận được bài toán cùng trừ rộng 23 giờ, 59 phút hoặc 59 giây. Nếu bạn có nhu cầu tính tân oán khoảng chừng thời hạn to hơn, hãy thực hiện những phnghiền toán thù sau đây.

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

Cộng/trừ giờ vào thời hạn thế thể

Hàm thời gian để thêm vào đó dưới 24 giờ

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

ví dụ như, vào ô A2 là thời hạn bước đầu, bạn muốn thêm 2 tiếng vào kia, công thức sẽ là:

=A2 + TIME(2, 0, 0)

 

*

Công thức để cùng ngẫu nhiên số giờ làm sao (dưới/bên trên 24 giờ)

=Start time + (N hours/24)

ví dụ như, để thêm vào đó 28 tiếng vào ô A2, nhập cách làm sau:

=A2 + (28/24)

*

Để trừ đi một trong những giờ đồng hồ, áp dụng bí quyết tương tự như nhỏng nuốm vệt + bằng dấu –

lấy một ví dụ, trừ ô A2 cho 3 giờ, nhập một trong các bí quyết sau:

=A2-(3/24)

=A2-TIME(3,0,0)

Nếu các bạn trừ thời gian to hơn 23, áp dụng phương pháp thứ nhất.

Cách cộng/trừ phút ít vào thời gian vậy 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 tđắm đuối số sản phẩm 2:

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

Đây là một trong vài công thức thực tế nhằm tính số phút trong Excel:

Công trăng tròn phút vào A2: =A2 + TIME(0,đôi mươi,0)

Trừ A2 cho 30 phút: =A2 – TIME(0,30,0)

Cộng trừ trên 60 phút

Trong phnghiền tính của doanh nghiệp, chia số phút mang đến 1440 – số phút ít trong một ngày và thêm thời hạn ban đầu vào :

=Start time + (N minutes / 1440)

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

Để cộng thêm số giây ít hơn 60 vào thời gian rõ ràng, á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 bí quyết tương tự nhưng lại nỗ lực dấu + 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 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 trong Excel

Phép cùng Excel thường xuyên áp dụng hàm SUM, cùng áp dụng định dạng tương xứng cho kết quả.

Giả sử các bạn tất cả một vài thời gian vào cột B cùng bạn muốn cùng bọn chúng cùng nhau. Bạn viết bí quyết SUM nlỗi sau =SUM(B2:B4), tác dụng hiển thị theo định dạng mang định như hh:mm:ss

 

*

Trong một vài ba trường hòa hợp, định dạng thời hạn mang định rất có thể ổn định, mà lại thỉnh thoảng bạn muốn hiển thị tổng thời gian bên dưới dạng phút với giây, hoặc chỉ hiển thị giây. khi đó, bạn ko đề nghị phnghiền tính như thế nào cả, các bạn chỉ việc định hình lại thời hạn mang đến ô tính cất công thức SUM.

Nhấp nên vào ô tính và lựa chọn Format Cells từ list tùy chọn, nhận Ctrl + 1 nhằm mngơi nghỉ hộp thoại Format Cells. Chọn Custom trường đoản cú Category menu và gõ một trong số format sau vào hộp Type:

Hiển thị thời gian 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 đúng thật sau:

*

Cách cộng qua 24 giờ đồng hồ trong Excel

Để tính toán thù hiệu quả rộng 24 giờ đồng hồ, bạn có thể sử dụng bí quyết SUM, cùng chọn một trong các định hình sau:

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

Để coi kết quả định hình trong trang tính thực, xem hình tiếp sau đây (bí quyết SUM được nhtràn vào ô A9 tới ô A13:

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

Chụ ý. Định dạng thời gian chỉ vận dụng mang đến số dương. Nếu hiệu quả tính toán là số âm, tức là chúng ta rước quý hiếm nhỏ dại rộng trừ cho giá trị lớn hơn, kết quả sẽ hiển thị #####.

*

Đó là toàn thể cách để những bạn cũng có thể giải pháp cùng trừ thời gian trong excel. Nếu còn bất cứ thắc mắc gì thì chớ ngại ngùng nsát cmt xuống dưới và để được lời giải nkhô hanh tuyệt nhất nhé!