Cách Cộng Trừ Thời Gian Trong Excel

     

Trong nội dung bài viết này, Học Excel Online sẽ khuyên bảo những công thức hữu ích nhằm cộng/trừ thời gian, chênh lệch thời gian, đếm giờ; phương pháp cộng trừ thời gian 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 với 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 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 hạn trong Excel

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

Như các bạn đã biết, thời hạn trong Excel hay được định dạng số thập phân nhằm trông như là thời gian. Vị chúng là số, nên chúng ta cũng có thể cộng trừ thời hạn 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 gian 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ó rất 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 quý hiếm 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 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 hạn giữa hai quý hiếm trong ô A2 cùng B2, xung quanh chênh lệch ngày tháng trường hợp ô tính chứa cả quý hiếm ngày tháng cùng thời gian

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

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

Phần thập phân vào cột D đúng nhưng không có ý nghĩa. Để góp chúng cung cấp thông tin có ích hơn, chúng ta cũng có thể áp dụng định dạng thời gian với một trong số 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à 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ụ 4:10:20.

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

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

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

Chú ý. Nếu thời hạn chênh lệch được hiển thành phố #####, tức là ô chứa phương pháp không đủ rộng nhằm hiển thị hết thời hạn hoặc tác 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 hạn bằng hàm TEXT

Một kỹ 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à giây giữa hai thời gian: =TEXT(B2-A2, “h:mm:ss”)
*
3 – giải 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. Các giá trị text được căn lề trài trong số cột C, D, E vào hình trên. Trong vài trường hợp nhất định, điều này rất có thể là một tinh giảm vì các bạn sẽ không thể sử dụng kết quả này để liên tiếp 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 giữa hai thời gian bằng một đơn vị (giờ, phút, giây), các bạn cần thực hiện 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 bước đầu trong ô A2 với thời gian chấm dứt trong ô B2, chúng ta hãy sử dụng phép tính đơn giản và dễ dàng là B2-A2 để đo lường và tính toán chênh lệch thời gian, tiếp nối nhân cùng với 24.

=(B2-A2) * 24

Để có số Complete hours, thực hiện hàm INT để có tác dụng tròn cho số nguyên hần nhất:

=INT((B2-A2) * 24)

*
4 – giải 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 công dụng âm hoặc dương, tác dụng âm xảy ra khi thời gian xong xuôi lớn rộng thời gian ban đầu như sản phẩm 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 – phương pháp tính thời gian trong Excel

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

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

Để tính chênh lệch giữa hai thời hạn trong thuộc một đối chọi vị, quăng quật qua những đơn vị thời gian khác, hãy áp dụng một trong các hàm sau:

Chênh lệch giờ, bỏ lỡ phút cùng giây:

=HOUR(B2-A2)

Chênh lệch phút, bỏ lỡ giờ và 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 ghi 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 ngừng lớn hơn thời gian bắt đầu, rõ ràng nếu hiệu quả là số âm, công thức sẽ hiển thị lỗi #NUM!

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

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

Giả sử ô A2 chứa thời khắc 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 ví dụ này nó bao gồm dạng h:mm

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

 

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

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

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

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

*
10- giải pháp tính thời gian trong Excel

Chú ý. Khoảng thời hạn 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 lật lại hoặc giám sát và đo lường lại. Để update công thức, dìm Shift + F9 để tính lại trang tính hoặc nhận 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 and XX seconds”

Bạn sử dụng những hàm HOUR, MINUTE SECOND để lấy công 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 đó, chúng ta lồng ghép các hàm vào thuộc một bí quyết cùng những chuỗi text như sau :

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

 

*

Để ẩn giá trị 0, lồng bốn hàm IF vào đó:

=IF(INT(B2-A2)>0, INT(B2-A2) & ” days, “,””) & IF(HOUR(B2-A2)>0, HOUR(B2-A2) và ” hours, “,””) và IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) và ” minutes and “,””) & 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 phương pháp lấy thời gian bắt đầu trừ thời gian ngừng (ví dụ = B2-A2) rồi thiết lập cấu hình định dạng sau cho ô tính:

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

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

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

Khi tính toán chênhh lệch cúng gian, chúng ta có thể bắt gặp mặt 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. Biến đổi 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à khắc ghi chọn Use 1904 date system.

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

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

*

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

Nếu không thích sử dụng phương thức 1, bạn có thể sử dụng phương pháp 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 cách làm sẽ soát sổ 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 công dụng hay không. Ví như 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ừ. Bí quyết thứ nhì cho kết quả tương tự bởi định dạng thời gian âm “-h:: mm”.

*

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

Xem thêm: Hướng Dẫn Bảng Đo Size Quần Áo Trẻ Em, Bảng Size Quần Áo Trẻ Em Việt Nam Chuẩn Nhất

Cộng/trừ thời gian trong Excel

Về cơ bản, gồm 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) trong một ngày.

Hàm TIME(hour, minute, second) giúp bài toán tính toán dễ dàng hơn, mặc dù nó không cho phép 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 toán khoảng thời gian 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 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 tiếng đồng hồ vào đó, bí quyết sẽ là:

=A2 + TIME(2, 0, 0)

 

*

Công thức để cộng bất kỳ số giờ làm sao (dưới/trên 24 giờ)

=Start time + (N hours/24)

Ví dụ, để thêm vào đó 28 giờ vào ô A2, nhập phương pháp sau:

=A2 + (28/24)

*

Để trừ đi một số trong những giờ, áp dụng công thức tương tự như như nỗ lực dấu + bởi dấu –

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

=A2-(3/24)

=A2-TIME(3,0,0)

Nếu chúng ta 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ố vật dụng 2:

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

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

Công đôi mươi 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 chia số phút mang lại 1440 – số phút vào một ngày và thêm thời gian bắt đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút, cố gắng 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 ít 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 lớn hơn 59, áp dụng 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ư nhưng cố 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 đế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 hạn trong Excel

Phép cùng Excel thường áp dụng hàm SUM, và vận dụng định dạng cân 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 có nhu cầu cộng chúng với nhau. Bạn viết bí quyết SUM như sau =SUM(B2:B4), tác dụng 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 hạn mặc định có thể ổn, tuy thế đô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. Khi đó, bạn không cần phép tính làm sao cả, bạn chỉ cần định dạng lại thời gian cho ô tính chứa bí quyết SUM.

Nhấp cần 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. Chọn Custom từ Category list và gõ một trong số định dạng sau vào vỏ hộp Type:

Hiển thị thời gian dưới dạng phút với 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 giờ đồng hồ trong Excel

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

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

*

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

Để hiện lên một khoảng chừ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 mà mã đơn vị thời gian tương ứng bên trong dấu ngoặc vuông, ví dụ điển hình như , , hay . Tiếp sau đây là công việc hướng dẫn chi tiết:

Chọn một hay các ô mà bạn có nhu cầu định dạng.Nhấp chuột đề xuất vào các ô được lựa chọn rồi lại click chuột phải vào Format Cells, hay chúng ta cũng có thể nhấn Ctrl + 1, thao tác này để 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õ trong số những định dạng thời gian sau đây trong hộp Type:

Lớn hơn 24 giờ: :mm:ss hay :mm

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

Lớn rộng 60 giây:

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

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

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

Khi được áp dụng cho dữ 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), đa số định dạng thời gian này vẫn cho công dụng như sau:

ABC
1Miêu tảThời gian hiển thịĐịnh dạng
2Số giờ50
3Số giờ và 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

Để mọi khoảng thời hạn được hiển thị nghỉ ngơi trên dễ dàng nắm bắt hơn so với người dùng, chúng ta có thể bổ sung đối kháng 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ờ và phút50 giờ và 40 phút “số tiếng 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ới 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ù đầy đủ khoảng thời hạn trên trông y như chuỗi ký kết tự, cơ mà chúng vẫn là giá trị số, bởi vì định dạng văn bản số vào Excel chỉ thay đổi về vẻ ngoài chứ thực chất không chũm đổi. Vày thế, chúng ta có thể tự bởi vì cộng giỏi trừ rất nhiều khoảng thời hạn đã được định dạng một phương pháp bình thường, hãy tham chiếu bọn 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 ước ao cho một khoảng thời gian cho sẵn, hãy đem số giờ, phút, tốt giây mà bạn muốn cộng cho đối chọi vị thời hạn tương ứng vào một ngày (24 giờ, 1440 phút, 86400 giây), rồi cộng số thương cho thời gian bắt đầu.

Cộng rộng 24 giờ.

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

Cộng hơn 60 phút.

Thời gian bước đầ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 có nhu cầu cộng.

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

Để cộng 45 giờ mang đến 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 cho thời gian bước đầu trong ô A2:

=A2+(200/86400)

Hay là, chúng ta có thể nhập khoảng thời gian cần cộng vào trong 1 ô riêng lẻ rồi tham chiếu ô kia trong công thức có dạng như trong hình ảnh chụp màn hình dưới đây:

Để trừ khoảng thời gian trong Excel, hãy thực hiện công thức giống như nhưng hãy nắm dấu cộng bằng dấu trừ nhé!

Trừ hơn 24 giờ

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

Trừ hơn 60 phút.

Xem thêm: Bật Mí Cách Làm Nước Mắm Bánh Bèo Kiểu Miền Trung, Cách Pha Nước Mắm Chấm Bánh Huế Dễ Làm Tại Nhà

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

Trừ hơn 60 giây

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

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

Lưu ý:

Nếu khoảng thời gian được đo lường và thống kê 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 thời điểm sử dụng định dạng cùng ô hiển thị #####, thì thường thì ô không được rộng để hiển thị giá bán trị thời hạn trong ngày. Để xử lý điều này, hãy không ngừng mở rộng chiều dài ô bằng phương pháp nhấp đúp loài chuột hay kéo size bên nên của cột.

Đó là toàn bộ phương pháp để các bạn 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ì đừng ngại ngần cmt xuống dưới sẽ được giải đáp nhanh nhất nhé!