Cách cộng trừ thời gian trong excel

     

Trong nội dung bài viết này, Học Excel Online đang khuyên bảo phần đa bí quyết hữu ích để cộng/trừ thời gian, chênh lệch thời gian, đếm giờ; biện pháp cùng trừ thời hạn trong excel.

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


Cách tính chênh lệch thời hạn trong ExcelCách tính cùng hiển thị thời hạn âm trong ExcelCộng/trừ thời hạn vào 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 í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 hạn trong Excel

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

Như chúng ta sẽ biết, thời gian trong Excel thường xuyên được format số thập phân để trông như là thời hạn. Vì bọn chúng là số, phải bạn có thể cùng trừ thời gian nhỏng những quý hiếm số không giống.

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

=End time – Start time

Tùy vào cấu tạo dữ liệu, chênh lệch thời hạn thực tế có thể có tương đối nhiều dạng khác biệt, ví dụ:

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

Trong hệ thống Excel, thời gian biểu thị tại phần thập phân, các bạn sẽ gồm kết quả nlỗi 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 nhiên không tồn tại ý nghĩa. Để góp chúng đưa thông tin có ích rộng, bạn có thể áp dụng định dạng thời hạn với cùng một trong những mã sau đây:

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

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

Bây tiếng, để xem cách làm tính chênh lệch thời gian với mã thời gian chuyển động như thế nào trong bảng tính thực sự. Với Start time trong cột A và End time trong cột B, chúng ta có thể coppy bí quyết 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 từng định hình thời hạn trong những cột:

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

Chụ ý. Nếu thời gian chênh lệch được hiển tỉnh thành #####, tức thị ô chứa cách làm không được rộng nhằm hiển thị không còn thời gian hoặc hiệu quả phép tính là một 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 để tính chênh lệch thời gian vào Excel là thực hiện hàm TEXT .

Tính toán chênh lệch giờ giữa nhì thời gian: =TEXT(B2-A2, “h”)Tính toán chênh lệch tiếng và phút thân hai thời gian: =TEXT(B2-A2, “h:mm”)Tính toán thù chênh lệch giờ đồng hồ, phút ít cùng giây giữa nhì thời gian: =TEXT(B2-A2, “h:mm:ss”)
*
3 – cách 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 uống bạn dạng. Các giá trị 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 tình huống nhất mực, điều này rất có thể là 1 trong giảm bớt do bạn sẽ cấp thiết sử dụng kết quả này nhằm tiếp tục tính toán.

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

Để hiển thị chênh lệch giữa hai thời gian bằng một đơn vị chức năng (giờ, phút, giây), bạn phải triển khai các phxay tính sau.

Phxay tính tiếng thân nhì thời gian

=(End time – Start time) * 24

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

=(B2-A2) * 24

Để gồm số Complete hours, sử dụng hàm INT để làm tròn đến số nguim 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 giữa nhị thời gian:

Để tính số phút giữa hai 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ể cho công dụng âm hoặc dương, tác dụng âm xẩy ra Khi thời hạn xong lớn hơn thời hạn ban đầu nhỏng hàng 5:

=(B2-A2)*1440

*
5 – cách tính thời hạn trong 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 gian với 86400

=(End time – Start time) * 86400

Trong ví dụ, bí quyết đã nhỏng sau:

=(B2-A2)* 86400

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

Chú ý. Định dạng General phải được tùy chỉnh thiết lập cho ô tính cất bí quyết tính chênh lệch thời hạn để kết quả hiển thị đúng.

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

Để tính chênh lệch giữa nhì thời gian vào và một đơn vị, bỏ qua mất các đơ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 với giây:

=HOUR(B2-A2)

Chênh lệch phút ít, bỏ qua mất giờ và giây:

=MINUTE(B2-A2)

Chênh lệch giây, bỏ qua tiếng 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ả ko được quá vượt 24 giờ, 60 phút ít hoặc 60 giây.

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

Chú ý. Nếu thời gian xong lớn hơn thời hạn bước đầu, rõ ràng ví như tác dụng là số âm, cách làm đang 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 lại bây giờ

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

Giả sử ô A2 cất thời gian bắt đầu, cách làm =NOW()-A2 cho hiệu quả sau cùng với cột B được format thời hạn cân xứng, trong ví dụ này nó có dạng h:mm

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

 

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

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

Nếu thời hạn bắt đầu chỉ bao gồm giờ phút cơ mà không tồn tại ngày, bạn cần sử dụng hàm Time nhằm tính toán thù khoảng chừng thời gian trôi đi một cách đúng mực. lấy ví dụ, công thức sau trả về khoảng thời gian từ bỏ ô A2 cho tiếng.

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

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

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

Bạn sử dụng các hàm HOUR, MINUTE SECOND để lấy công dụng thời hạn tương tứng và hàm INT nhằm tính chênh lệch ngày.Sau kia, chúng ta lồng ghxay các hàm vào và một phương pháp thuộc những chuỗi text nlỗi sau :

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

 

*

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

 

*

Trong khi, chúng ta có thể tính toán thù chênh lệch thời gian bằng cách mang thời gian bắt đầu trừ thời gian xong xuôi (ví dụ = B2-A2) rồi cấu hình 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 vào Excel

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

lúc tính tân oán chênhh lệch thờ gian, chúng ta cũng có thể phát hiện lỗi ##### vì chưng độ chênh lệch này âm. Tuy nhiên, cũng có tương đối nhiều cách để hiển thị kết quả âm vào 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 thanh khô cuốn xuống When calculating this workbook với khắc ghi chọn Use 1904 date system.

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

Nhấp OK để lưu lại setup bắt đầu, quý hiếm âm sẽ được hiển thị như sau:

*

Pmùi hương pháp 2. Tính toán thời hạn â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, “-” & TEXT(ABS(A2-B2),”h:mm”))

=IF(A2-B2>0, A2-B2, TEXT(ABS(A2-B2),”-h:mm”))

Cả nhì bí quyết sẽ bình chọn chênh lệch thời gian (A2-B2) gồm to hơn 0 hay là không và nó gồm hiện kết quả hay không. Nếu chênh lệch bé thêm hơn 0, cách làm trước tiên và tính trị hoàn hảo rồi thêm vệt trừ. Công thức vật dụng nhị mang lại kết quả tựa như bằng định hình thời hạn âm “-h:: mm”.

*

Chụ ý. Hãy hãy nhớ là cách thức thứ nhất cho ra hiệu quả âm là cực hiếm số âm, tác dụng hàm TEXT luôn luôn là chuỗi văn phiên bản cùng bắt buộc sử dụng trong phép toán hoặc cách làm khác.

Xem thêm: Tải Phần Mềm Đồng Bộ Dữ Liệu Hàng Đầu 2021, Phần Mềm Đồng Bộ Dữ Liệu Giữa 2 Thư Mục

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

Về cơ phiên bản, tất cả 2 giải pháp cộng/trừ thời gian vào Excel:

Sử dụng hàm TIMESử dụng phxay tân oán dựa vào thời gian (24), số phút ít (1440), số giây (86400) trong một ngày.

Hàm TIME(hour, minute, second) góp bài toán tính toán dễ dãi hơn, tuy vậy nó không có thể chấp nhận được 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 tân oán khoảng tầm thời gian lớn hơn, hãy áp dụng các phxay toán tiếp sau đây.

Tmê man khảo ngay: Giáo trình Excel 2020

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

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

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

Ví dụ, vào ô A2 là thời gian bước đầu, bạn muốn thêm 2 giờ vào đó, phương pháp sẽ là:

=A2 + TIME(2, 0, 0)

 

*

Công thức để cùng bất kỳ khoảng thời gian nào (dưới/bên trên 24 giờ)

=Start time + (N hours/24)

Ví dụ, để 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 cách làm tựa như như thế vết + bằng vệt –

ví dụ như, trừ ô A2 đến 3h, nhập một trong những cách làm sau:

=A2-(3/24)

=A2-TIME(3,0,0)

Nếu bạn trừ số giờ to hơn 23, thực hiện cách làm đầu tiên.

Cách cộng/trừ phút ít vào thời hạn núm 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 tsay mê số trang bị 2:

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

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

Công đôi mươi phút ít vào A2: =A2 + TIME(0,20,0)

Trừ A2 mang đến 30 phút: =A2 – TIME(0,30,0)

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

Trong phnghiền tính của người tiêu dùng, phân chia số phút cho 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, ráng lố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 gian gắng thể

Để cộng thêm số giây thấp hơn 60 vào thời gian cụ thể, áp dụng hàm TIME:

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

Để cộng thêm số giây to hơn 59, thực hiện công thức:

=Start time + (N seconds / 86400)

Để trừ số giây, tận dụng phương pháp tương tự như mà lại cố gắng dấu + thành dấu -.

Trên trang tính Excel, công thức 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 đến 40 giây: =A2 – TIME(0,0,40)Trừ ô A2 mang lại 900 giây: =A2 – (900/86400)Cách cộng thời gian trong Excel

Phxay cộng Excel thường xuyên thực hiện hàm SUM, và vận dụng định hình cân xứng mang đến công dụng.

Giả sử các bạn có một vài thời gian trong cột B với bạn có nhu cầu cùng chúng với nhau. quý khách viết công thức SUM nlỗi sau =SUM(B2:B4), kết quả hiển thị theo định dạng khoác định nlỗi hh:mm:ss

 

*

Trong một vài trường hợp, format thời gian khoác định có thể ổn định, dẫu vậy đôi khi bạn có nhu cầu hiển thị tổng thời hạn dưới dạng phút với giây, hoặc chỉ hiển thị giây. khi kia, chúng ta ko bắt buộc phép tính nào cả, chúng ta chỉ việc định hình lại thời gian cho ô tính đựng công thức SUM.

Nhấp đề nghị vào ô tính và chọn Format Cells từ list tùy lựa chọn, dấn Ctrl + 1 nhằm mnghỉ ngơi hộp thoại Format Cells. Chọn Custom từ bỏ Category danh mục cùng 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: :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ờ vào Excel

Để tính tân oán kết quả rộng 24 tiếng, chúng ta cũng có thể áp dụng bí quyết SUM, cùng chọn 1 trong những đị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, đôi mươi seconds
d h:mm:ss1 06:10:20Ngày, giờ đồng hồ, 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 và 20 seconds

Để coi công dụng định hình vào trang tính thực, coi hình dưới đây (phương pháp SUM được nhtràn 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 hiệu quả tính tân oán là số âm, nghĩa là chúng ta đem quý hiếm nhỏ dại hơn trừ cho cực hiếm to hơn, kết quả vẫn hiển thị #####.

*

Cách hiển thị khoảng tầm thời hạn to hơn 24 giờ đồng hồ, 60 phút ít, 60 giây?

Để hiện lên một khoảng tầm thời gian to hơn 24 giờ đồng hồ, 60 phút ít, 60 giây, hãy sử dụng custom time format chỗ cơ mà mã đơn vị thời gian khớp ứng nằm trong vệt ngoặc vuông, chẳng hạn như , , tuyệt . Dưới đấy là công việc giải đáp đưa ra tiết:

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

Lớn hơn 24 giờ: :mm:ss tuyệt :mm

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

Lớn hơn 60 giây:

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

Dưới đây là một vài format khác. Chúng ta có thể áp dụng nó nhằm hiển thị đa số khoảng tầm thời hạn to hơn chiều lâu năm đơn vị chức năng thời gian chuẩn chỉnh.

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 cùng giây:ss
Tổng số giây

lúc được áp dụng mang đến dữ liệu chủng loại của họ (Thời gian tổng số 50:40 vào hình họa chụp màn hình hiển thị trên), hồ hết định hình thời gian này vẫn mang đến kết quả như sau:

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

Để hồ hết khoảng thời gian được hiển thị sinh sống trên dễ nắm bắt hơn so với người dùng, chúng ta có thể bổ sung đơn vị chức năng thời hạn bằng các tự viết tắt khớp ứ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ố giờ và” milimet “số phút”
3Số tiếng, phút ít, giây50h.40m.30s “h.” milimet “m.” ss “s.”
4Số phút3040 phút “số phút”
5Số phút và 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 cho dù hầu như khoảng tầm thời hạn bên trên trông giống hệt như chuỗi ký kết tự, nhưng lại chúng vẫn chính là quý hiếm số, cũng chính vì định dạng chữ số trong Excel chỉ biến đổi về hiệ tượng chứ đọng thực chất không biến hóa. Vì nạm, bạn có thể tự do thoải mái cộng xuất xắc trừ đa số khoảng thời hạn đã làm được định hình một giải pháp thông thường, hãy tham mê chiếu bọn chúng vào công thức cùng thực hiện trong những phnghiền tính khác.

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

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

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

Thời gian ban đầ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 bước đầu + (N/86400)

Trong số đó, N là khoảng thời gian, phút, hay giây nhưng bạn có nhu cầu cộng.

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

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

=A2+(45/24)

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

=A2+(100/1440)

Để cộng 200 giây đến thời hạn bước đầu trong ô A2:

=A2+(200/86400)

hoặc là, bạn cũng có thể nhập khoảng tầm thời hạn buộc phải cộng vào trong 1 ô riêng lẻ rồi tđam mê chiếu ô đó vào công thức bao gồm dạng như trong hình ảnh chụp màn hình hiển thị bên dưới đây:

Để trừ khoảng chừng thời hạn vào Excel, hãy áp dụng phương pháp giống như tuy vậy hãy thay dấu cùng bởi vết trừ nhé!

Trừ hơn 24 giờ

Thời gian bước đầ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 ban đầu – (N/86400)

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

Lưu ý:

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

Đó là toàn cục phương pháp để các bạn cũng có thể cách cộng trừ thời hạn trong excel. Nếu còn bất cứ vướng mắc gì thì đừng trinh nữ nngay gần cmt xuống bên dưới và để được giải đáp nhanh hao độc nhất vô nhị nhé!