Cách trừ giờ phút giây trong excel

      24

Trong bài viết này, Học Excel Online sẽ lí giải những cách làm hữu ích nhằm 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 trừ giờ phút giây 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 gian âm vào ExcelCộng/trừ thời hạn trong ExcelCách cộng thời gian trong ExcelCách hiển thị khoảng chừng thời gian lớn hơn 24 giờ, 60 phút, 60 giây?Cách cùng trừ thời gian to 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ư các bạn đã biết, thời gian trong Excel thường được định hình số thập phân để trông giống như thời gian. Do chúng là số, nên bạn cũng có thể cộng trừ thời gian như các giá trị số khác.

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

=End time – Start time

Tùy vào kết cấu dữ liệu, chênh lệch thời gian thực tế gồm thể có khá nhiều 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à 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 hạn giữa hai quý giá trong ô A2 cùng 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 hệ thống Excel, thời gian biểu lộ ở phần thập phân, các bạn sẽ có hiệu quả như sau:

*
1 – cách 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 đưa tin có ích hơn, chúng ta có thể áp dụng định dạng thời gian với một trong những mã sau đây:

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

Để vận 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 danh sách và gõ mã thời hạn vào hộp Type.

Bây giờ, nhằm xem bí quyết tính chênh lệch thời gian và mã thời gian chuyển động như vậy nào trong bảng tính thực sự. Cùng với Start time trong cột A cùng End time vào cột B, bạn có thể sao chép cách làm vào cột C cùng 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 hạn trong mỗi 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 phố #####, tức thị ô chứa bí quyết không đầy đủ rộng nhằm hiển thị hết thời hạn hoặc công dụng phép tính là một trong những 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 gian trong Excel là sử 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ới phút giữa 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 quả của hàm TEXT 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 hoàn toàn có thể là một hạn chế 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), chúng ta cần tiến hành 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 dứt trong ô B2, bạn hãy sử dụng 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 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 – 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 giữa 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 hoàn toàn có thể cho hiệu quả âm hoặc dương, kết quả âm xảy ra khi thời gian chấm dứt lớn rộng thời gian bắt đầu như hàng 5:

=(B2-A2)*1440

*
5 – bí quyết tính thời hạn 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 gian với 86400

=(End time – Start time) * 86400

Trong ví dụ, cách làm sẽ như sau:

=(B2-A2)* 86400

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

Chú ý. Định dạng General nên được thiết lập cho ô tính chứa công thức 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 đối kháng vị thời gian và bỏ qua các đơn vị không giống

Để tính chênh lệch thân hai thời gian trong cùng một 1-1 vị, vứt qua các đơn vị thời hạn khác, hãy sử dụng một trong các 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, làm 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 nhớ rằng kết quả không được vượt thừa 24 giờ, 60 phút hoặc 60 giây.

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

Chú ý. Nếu thời gian kết thúc lớn hơn thời hạn bắt đầu, ví dụ nếu kết quả là số âm, phương pháp 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 ban đầu đến bây giờ

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

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

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

 

Trong trường vừa lòng khoảng thời gian trôi qua vượt vượt 24 giờ, thực hiện một trong những định dạng thời gian sau, ví dụ 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 để thống kê giám sát khoảng thời hạn trôi đi một cách chính xác. Ví dụ, phương pháp sau trả về khoảng thời hạn từ ô A2 đến 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 update theo thời hạn thực tế, nó chỉ cập nhật khi bảng tính được mở ra hoặc thống kê giám sát lại. Để cập nhật công thức, 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 và XX seconds”

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

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

 

*

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

 

*

Ngoài ra, bạn cũng có thể tính toán chênh lệch thời gian bằng cách lấy thời gian ban đầu trừ thời gian chấm dứt (ví dụ = B2-A2) rồi cấu hình thiết lập định dạng sau đến ô tính:

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

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

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

Khi đo lường và tính toán chênhh lệch thờ gian, chúng ta cũng có thể bắt chạm chán lỗi ##### vì độ chênh lệch này âm. Tuy nhiên, cũng có nhiều phương pháp để hiển thị kết quả âm trong Excel.

Phương pháp 1. Biến hóa 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 – giải pháp tính thời gian trong Excel

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

*

Phương pháp 2. Giám sát và đo lường thời gian âm bởi công thức

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

*

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

Xem thêm: Bật Mí Con Số Hợp Mệnh Thổ Hợp Với Số Nào Nhất? Những Người Mệnh Thổ Hợp & Khắc Số Gì

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

Về cơ bản, có 2 giải pháp 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 tiện lợi hơn, tuy vậy nó không chất nhận được 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 gian lớn hơn, hãy sử dụng các phép toán bê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 gian để cộng thêm dưới 24 giờ

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

Ví dụ, trong ô A2 là thời gian bắt đầu, bạn có nhu cầu 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ụ, để thêm vào đó 28 giờ vào ô A2, nhập công thức sau:

=A2 + (28/24)

*

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

Ví dụ, trừ ô A2 mang lại 3 giờ, nhập một trong số công thức sau:

=A2-(3/24)

=A2-TIME(3,0,0)

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

Cách cộng/trừ phút vào thời gian 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ố thiết bị 2:

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

Đây là 1 trong vài công thức thực tế để 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 đến 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 cho 1440 – số phút trong một ngày cùng thêm thời gian bước đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút, nắm dấu + 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 cụ thể

Để thêm vào đó số giây thấp hơn 60 vào thời gian cụ thể, thực hiện hàm TIME:

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

Để thêm vào đó 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 công thức tương tự như nhưng cố kỉnh dấu + thành vết -.

Trên trang tính Excel, công thức 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 đế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 phù hợp 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 có nhu cầu cộng chúng với nhau. Các bạn viết cách làm SUM như sau =SUM(B2:B4), hiệu quả hiển thị theo định hình 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 có thể ổn, cơ mà đôi khi bạn có nhu cầu 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 đề nghị phép tính như thế nào cả, bạn chỉ cần định dạng lại thời hạn cho ô tính chứa công thức SUM.

Nhấp đề nghị vào ô tính và lựa chọn Format Cells từ list tùy chọn, nhấn Ctrl + 1 để mở hộp thoại Format Cells. Chọn Custom từ Category menu và gõ một trong những định dạng sau vào vỏ hộp Type:

Hiển thị thời gian dưới dạng phút và giây: :ss

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

Kết quả thật sau:

*

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

Để tính toán hiệu quả hơn 24 giờ, bạn có thể sử dụng phương pháp SUM, và 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 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ờ, 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 and 20 seconds

Để xem hiệu quả định dạng trong trang tính thực, xem hình dưới đâ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ỉ vận dụng cho số dương. Nếu tác dụng tính toán là số âm, nghĩa là chúng ta lấy giá bán trị nhỏ hơn trừ mang lại giá trị phệ hơn, công dụng sẽ hiển thị #####.

*

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

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

Chọn một hay các ô mà bạn có nhu cầu định dạng.Nhấp chuột buộc phải 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ó thể nhấn Ctrl + 1, thao tác này để giúp đỡ mở 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 những định dạng thời gian tiếp sau đây trong vỏ 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 hơn 60 giây:

Ảnh chụp màn hình dưới đây cho thấy 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 tầm thời gian lớn hơn chiều dài 1-1 vị thời gian 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 cùng giây:ss
Tổng số giây

Khi được sử dụng cho dữ liệu mẫu của chúng ta (Thời gian tổng cộng 50:40 trong ảnh chụp screen trên), đều định dạng thời gian này đã cho tác dụng 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 cùng giây3040:30:ss
7Số giây182430

Để phần đa khoảng thời gian được hiển thị sống trên dễ dàng nắm bắt hơn so với người dùng, bạn có thể bổ sung đối kháng vị thời hạn bằng những 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ờ và 40 phút “số giờ và” mm “số phút”
3Số giờ, phút, giây50h.40m.30s “h.” mm “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 dù hồ hết khoảng thời gian trên trông giống hệt như chuỗi ký tự, nhưng chúng vẫn là giá trị số, bởi vì định dạng chữ số vào Excel chỉ đổi khác về hình thức chứ bản chất không vắt đổi. Bởi vì thế, chúng ta có thể tự bởi cộng tuyệt trừ đều khoảng thời hạn đã được format một giải pháp bình thường, hãy tham 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 gian lớn hơn 24 giờ, 60 phút, 60 giây

Để cộng khoảng thời hạn mong hy vọng cho một khoảng thời gian cho sẵn, hãy rước 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 hơn 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 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à một vài ví dụ công thức 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 đến thời gian ban đầu trong ô A2:

=A2+(100/1440)

Để cùng 200 giây đến thời gian bắt đầ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 một ô hiếm hoi rồi tham chiếu ô kia trong công thức tất cả dạng như trong hình ảnh chụp màn hình dưới đây:

Để trừ khoảng thời hạn trong Excel, hãy thực hiện công thức tương tự như nhưng hãy nỗ lực dấu cộng bởi dấu trừ nhé!

Trừ rộng 24 giờ

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

Trừ rộng 60 phút.

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

Trừ rộng 60 giây

Thời gian bước đầ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 có dạng số thập phân, hãy áp dụng định dạng thời gian/ngày để định dạng ô.Nếu sau khoản thời gian sử dụng định dạng và ô hiển thị #####, thì thường thì ô cảm thấy không được rộng để hiển thị giá chỉ 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 cách nhấp đúp chuột hay kéo khung bên đề nghị của cột.

Đó là toàn bộ cách để các bạn cũng có thể cách cùng trừ thời hạn trong excel. Nếu như còn bất kể thắc mắc gì thì chớ ngại ngần cmt xuống dưới và để được giải đáp sớm nhất có thể nhé!