Hàm Tính Thời Gian Trong Excel

Trong 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 hạn trong excel.

Bạn đang xem: Hàm tính thời gian trong excel


Cách tính chênh lệch thời gian trong ExcelCách tính với hiển thị thời hạn âm trong ExcelCộng/trừ thời gian trong ExcelCách cộng thời hạn 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 lớn 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ư chúng ta đã biết, thời gian trong Excel thường xuyên được format số thập phân để trông giống 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ư 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 cấu trúc dữ liệu, chênh lệch thời gian thực tế có thể có tương đối nhiều dạng không giống 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 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 gian giữa hai quý hiếm trong ô A2 và B2, xung quanh chênh lệch ngày tháng giả dụ ô tính đựng cả cực hiếm ngày tháng và thời gian

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

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

Mã thời gianGiải thích
hSố tiếng trôi qua, ví dụ như 4
h:mmSố giờ cùng 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, lấy một ví dụ 4:10:20.

Để áp dụng định dạng thời gian, dìm Ctrl + 1 để mở vỏ hộp thoại Format Cells, lựa chọn Custom từ Category list và gõ mã thời gian vào 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 hoạt động như cầm cố nào vào bảng tính thực sự. Với Start time trong cột A và End time trong cột B, bạn cũng có thể sao chép bí quyết vào cột C cùng E:


*

*

=$B2-$A2

Thời gian chênh lệch được hiển thị khác nhau tùy vào định dạng thời hạn trong từng cột:

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

Chú ý. Nếu thời gian chênh lệch được hiển tỉnh thành #####, nghĩa là ô chứa bí quyết không đầy đủ rộng nhằm hiển thị hết thời gian hoặc kết quả 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 kỹ thuật khác nhằm 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ờ giữa 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 và giây giữa 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 luôn là văn bản. Những giá trị text được căn lề trài trong các 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 giảm bớt vì bạn sẽ không thể sử dụng kết quả này để tiếp tục 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 gian bằng một đơn vị (giờ, phút, giây), chúng ta 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 ban đầu trong ô A2 và thời gian kết thúc trong ô B2, chúng ta hãy thực hiện phép tính đơn giản là B2-A2 để giám sát và đo lường chênh lệch thời gian, kế tiếp nhân với 24.

=(B2-A2) * 24

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

=INT((B2-A2) * 24)

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

 

Tổng số phút giữa 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 tác dụng âm hoặc dương, hiệu quả âm xẩy ra khi thời gian chấm dứt lớn hơn thời gian bước đầu như hàng 5:

=(B2-A2)*1440

*
5 – giải 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 gian với 86400

=(End time – Start time) * 86400

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

=(B2-A2)* 86400

*
6 – bí quyết 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 công thức tính chênh lệch thời gian để công dụng hiển thị đúng.

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

Để tính chênh lệch thân hai thời hạn trong cùng một solo vị, vứt qua các đơn vị thời gian khác, hãy thực hiện một trong số hàm sau:

Chênh lệch giờ, làm lơ phút với giây:

=HOUR(B2-A2)

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

=MINUTE(B2-A2)

Chênh lệch giây, bỏ lỡ giờ và phút:

=SECOND(B2-A2)

Khi áp dụng 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 – cách 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, bí quyết 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 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 tháng và thời hạn hiện tại, sau đó trừ đi ngày và thời gian bắt đầu.

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

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

 

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

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

Nếu thời gian bắt đầu chỉ gồm giờ phút mà không có ngày, bạn cần sử dụng hàm Time để giám sát và đo lường 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 gian từ ô A2 mang đến giờ.

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

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

Chú ý. Khoảng thời gian trôi qua sẽ không đượ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 mở lại hoặc giám sát lại. Để update công thức, nhấn Shift + F9 để tính lại trang tính hoặc dìm 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 and XX seconds”

Bạn sử dụng các 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 các hàm vào thuộc một phương pháp cùng những chuỗi text như sau :

=INT(B2-A2) và ” days, ” và HOUR(B2-A2) và ” hours, ” & MINUTE(B2-A2) & ” minutes and ” và SECOND(B2-A2) & ” 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”,””)

 

*

Ngoài ra, chúng ta 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 xong xuôi (ví dụ = B2-A2) rồi thiết lập định dạng sau mang lại ô 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 hạn âm vào Excel

Khi tính toán chênhh lệch cúng gian, bạn có thể bắt gặp lỗi ##### vì độ chênh lệch này âm. Tuy nhiên, cũng có nhiều cách để hiển thị công dụng âm trong 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 – bí quyết tính thời hạn trong Excel

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

*

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

*

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

Xem thêm: Forza Horizon 1 Cấu Hình - Tham Khảo Cấu Hình Forza Horizon 2, 3, 4

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

Về cơ bản, gồm 2 phương pháp cộng/trừ thời gian 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) trong một ngày.

Hàm TIME(hour, minute, second) giúp việc tính toán thuận lợi hơn, mặc dù 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 có nhu cầu tính toán khoảng thời hạn lớn hơn, hãy sử dụng những 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 để thêm vào đó dưới 24 giờ

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

Ví dụ, trong ô A2 là thời hạn bắt đầu, bạn có nhu cầu thêm 2 tiếng đồng hồ vào đó, phương pháp sẽ là:

=A2 + TIME(2, 0, 0)

 

*

Công thức nhằm 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 công thức sau:

=A2 + (28/24)

*

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

Ví dụ, trừ ô A2 mang đế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 chúng ta trừ số giờ to hơn 23, sử dụng 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ố thứ 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 vào Excel:

Công đôi mươi 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 trong một ngày và thêm thời gian bắt đầu vào :

=Start time + (N minutes / 1440)

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

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

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

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

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 đến 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 áp dụng hàm SUM, và áp 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 gian trong cột B và bạn muốn cộng bọn chúng với nhau. Bạn viết công thức SUM như sau =SUM(B2:B4), công 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 hoàn toàn có thể ổn, nhưng mà đô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. Khi đó, các bạn không buộc phải phép tính nào cả, bạn chỉ cần định dạng lại thời hạn cho ô tính chứa phương pháp SUM.

Nhấp bắt buộc vào ô tính và chọn Format Cells từ list tùy chọn, dấn Ctrl + 1 để mở vỏ hộp thoại Format Cells. Chọn Custom từ Category danh mục và gõ một trong các định dạng sau vào 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ả như sau:

*

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

Để tính toán tác dụng hơn 24 giờ, bạn cũng có thể sử dụng công thức SUM, và chọn một 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”, milimet “minutes”, ss “seconds”30 hours, 10 minutes, đôi mươi 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 gian chỉ áp 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ỏ dại hơn trừ mang lại giá trị lớn hơn, kết quả sẽ hiển thị #####.

*

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

Để hiện thị 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 nhưng mà mã đơn vị thời hạn tương ứng phía trong dấu ngoặc vuông, ví dụ điển hình như , , giỏi . Tiếp sau đây là quá trình 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 bắt buộc vào các ô được chọn rồi lại bấm vào phải vào Format Cells, hay chúng ta có thể nhấn Ctrl + 1, thao tác này để giúp đỡ mở vỏ hộp thoại Format Cells.Trong Number tab, mặt dưới Category, hãy chọn Custom rồi gõ giữa những định dạng thời gian dưới đâ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 hơn 60 giây:

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

Dưới đó là một vài định hình khác. Chúng ta có thể sử dụng nó để hiển thị những khoảng thời gian lớn hơn chiều dài đơn 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 với giây:ss
Tổng số giây

Khi được thực hiện cho dữ liệu mẫu của họ (Thời gian tổng số 50:40 trong hình ảnh chụp screen trên), đầy đủ định dạng thời gian này đang 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ờ 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

Để phần nhiều khoảng thời gian được hiển thị ở trên dễ hiểu hơn so với người dùng, bạn có thể bổ sung đơn vị thời gian 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ờ đồng hồ và” milimet “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 cùng 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 giống hệt như chuỗi ký kết tự, nhưng mà chúng vẫn là giá trị số, bởi vì định dạng chữ số trong Excel chỉ biến hóa về hình thức chứ thực chất không cố đổi. Bởi vì thế, chúng ta cũng có thể tự vì chưng cộng xuất xắc trừ gần như khoảng thời gian đã được định dạng một biện 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 mong muốn cho một khoảng thời hạn cho sẵn, hãy đem số giờ, phút, hay giây mà bạn muốn 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 đương cho thời gian bắt đầu.

Cộng hơn 24 giờ.

Thời gian ban đầu + (N/24)

Cộng rộng 60 phút.

Thời gian ban đầu + (N/1440)

Cộng rộng 60 giây.

Thời gian ban đầu + (N/86400)

Trong đó, N là số giờ, phút, hay giây mà bạn muốn cộng.

Đây là 1 trong những vài ví dụ cách làm 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 cho thời gian bước đầu trong ô A2:

=A2+(100/1440)

Để cùng 200 giây đến thời gian ban đầ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 một trong những ô cá biệt rồi tham chiếu ô đó trong công thức bao gồm dạng như trong ả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 tương tự nhưng hãy thế dấu cộng bởi dấu trừ nhé!

Trừ hơn 24 giờ

Thời gian ban đầu – (N/24)

Trừ hơn 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 giám sát có dạng số thập phân, hãy thực hiện đị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ường ô không đủ rộng nhằm hiển thị giá chỉ trị thời gian trong ngày. Để xử trí điều này, hãy mở rộng chiều dài ô bằng phương pháp nhấp đúp chuột hay kéo khung bên phải của cột.

Đó là toàn bộ cách để các chúng ta 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 để được giải đáp nhanh nhất nhé!

Leave a Reply

Your email address will not be published. Required fields are marked *

  • Đổi phông nền ảnh thẻ online

  • Cách đổi username zalo

  • Powerpoint 2016 không có morph

  • Gõ dấu bị mất chữ trong excel

  • x