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

Công thức đơ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ế tất cả thể có không ít 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 cực hiếm trong ô A2 và B2
=TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”)Tính toán chênh lệch thời hạn 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 gian giữa hai cực hiếm trong ô A2 cùng B2, ngoài chênh lệch ngày tháng ví như ô tính cất cả quý hiếm ngày tháng với thời gian

Trong hệ thống Excel, thời gian bộc lộ ở phần thập phân, các bạn sẽ có tác dụng như sau:

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

Phần thập phân trong cột D đúng nhưng không có ý nghĩa. Để giúp chúng báo tin có ích hơn, bạn cũng có thể áp dụng định dạng thời gian 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ụ 4
h:mmSố giờ cùng số phút trôi qua, lấy ví dụ như 4:10
h:mm:ssSố giờ, số phút cùng số giây trôi qua, ví dụ 4:10:20.

Để vận dụng định dạng thời gian, thừa nhận Ctrl + 1 để mở vỏ hộp thoại Format Cells, lựa chọn Custom từ Category các mục 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 hạn và mã thời gian hoạt động như gắng nào vào bảng tính thực sự. Với Start time vào cột A cùng End time vào cột B, bạn cũng có thể sao chép công thức vào cột C và 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 – 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 thành phố #####, tức thị ô chứa cách làm không đầy đủ rộng nhằm hiển thị hết thời hạn hoặc tác dụng phép tính là 1 giá trị âm.

Công thức 2. Tính chênh lệch thời gian bằng hàm TEXT

Một nghệ thuật khác nhằm tính chênh lệch thời gian trong Excel là áp 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à 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 – cách tính thời hạn trong Excel

Chú ý.

Kết trái của hàm TEXT luôn là văn bản. Các giá trị text được căn 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 định, điều này hoàn toàn có thể là một tiêu giảm vì các bạn sẽ không thể sử dụng công dụng 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 thân hai thời hạn bằng một đơn vị chức năng (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ắt đầu trong ô A2 với thời gian xong xuôi trong ô B2, các bạn hãy sử dụng phép tính dễ dàng và đơn giản là B2-A2 để tính toán chênh lệch thời gian, kế tiếp nhân cùng với 24.

=(B2-A2) * 24

Để có số Complete hours, sử dụng hàm INT để làm cho tròn mang lại số nguyên hần nhất:

=INT((B2-A2) * 24)

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

 

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

Để tính số phút giữa 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 công dụng âm hoặc dương, hiệu quả âm xẩy ra khi thời gian xong lớn rộng thời gian ban đầu như sản phẩm 5:

=(B2-A2)*1440

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

 

Tổng số giây thân hai thời gian:

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

=(End time – Start time) * 86400

Trong ví dụ, phương pháp sẽ như sau:

=(B2-A2)* 86400

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

Chú ý. Định dạng General nên được thiết lập cấu hình cho ô tính chứa phương pháp 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 hạn trong thuộc một 1-1 vị thời gian và quăng quật qua những đơn vị khác

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

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

=HOUR(B2-A2)

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

=MINUTE(B2-A2)

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

=SECOND(B2-A2)

Khi áp dụng hàm HOUR, MINUTE, SECOND, hãy ghi nhớ rằng hiệu quả không được vượt quá 24 giờ, 60 phút hoặc 60 giây.

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

Chú ý. Nếu thời gian xong xuôi lớn hơn thời hạn bắt đầu, rõ ràng 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 bắt đầu đến bây giờ

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

Giả sử ô A2 chứa thời điểm bắt đầu, phương pháp =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 ví dụ như này nó tất cả dạng h:mm

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

 

Trong trường hòa hợp khoảng thời gian trôi qua vượt thừa 24 giờ, áp dụng một trong những định dạng thời hạn sau, ví dụ như 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ước đầu chỉ tất cả giờ phút mà không tồn tại ngày, bạn phải sử dụng hàm Time để đo lường và tính toán khoảng thời hạn trôi đi một cách bao gồm xác. Ví dụ, phương pháp sau trả về khoảng thời gian từ ô A2 cho 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 update 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 và đo lường 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 thừa nhận F9 để tính lại tổng thể 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 & XX seconds”

Bạn sử dụng những hàm HOUR, MINUTE SECOND nhằm lấy công dụng thời gian tương tứng với hàm INT nhằm tính chênh lệch ngày.Sau đó, chúng ta lồng ghép những hàm vào cùng một bí quyết cùng các chuỗi text như sau :

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

 

*

Ngoài ra, bạn 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 hoàn thành (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 – giải pháp tính thời gian trong Excel

Cách tính cùng hiển thị thời hạn âm trong Excel

Khi giám sát và đo lường chênhh lệch bái gian, bạn có thể bắt chạm mặt 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ị hiệu quả âm vào Excel.

Phương pháp 1. 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 cuốn xuống When calculating this workbook và khắc ghi chọn Use 1904 date system.

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

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

*

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

Nếu không muốn sử dụng phương thức 1, bạn cũng có thể sử dụng công thức 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 phương pháp 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ó có hiện tác dụng hay không. Nếu chênh lệch nhỏ hơn 0, công thức đầu tiên sẽ tính trị tuyệt vời nhất rồi thêm lốt trừ. Phương pháp thứ hai cho công dụng tương tự bằng định dạng thời gian âm “-h:: mm”.

*

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

Xem thêm: Sao Mai Phương Thanh: "Hương Tràm Nổi Tiếng Hơn Tôi Là… Đương Nhiên"

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

Về cơ bản, tất cả 2 giải pháp cộng/trừ thời hạn 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 thuận lợi hơn, mặc dù nó không được 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 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ừ tiếng vào thời hạn cụ thể

Hàm thời gian để thêm vào đó 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 để cộng ngẫu nhiên số giờ nào (dưới/trên 24 giờ)

=Start time + (N hours/24)

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

=A2 + (28/24)

*

Để trừ đi một vài giờ, áp dụng công thức tương tự như thay dấu + bởi dấu –

Ví dụ, trừ ô A2 mang lại 3 giờ, nhập một trong những 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, 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 có nhu cầu vào tham số máy 2:

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

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

Công 20 phút vào A2: =A2 + TIME(0,20,0)

Trừ A2 mang đế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 mang đến 1440 – số phút trong một ngày và 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 hạn 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 to hơn 59, sử 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 chũm dấu + thành dấu -.

Trên trang tính Excel, bí quyết 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 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 phù hợp cho kết quả.

Giả sử bạn có một vài thời hạn trong cột B và bạn muốn cộng chúng với nhau. Chúng ta viết phương pháp SUM như sau =SUM(B2:B4), kết quả hiển thị theo format mặc định như hh:mm:ss

 

*

Trong một vài ngôi trường hợp, định dạng thời hạn mặc định có thể ổn, tuy vậy đôi khi bạn muốn hiển thị tổng thời hạn dưới dạng phút cùng giây, hoặc chỉ hiển thị giây. Lúc đó, các bạn không nên phép tính nào cả, bạn chỉ việc định dạng lại thời gian cho ô tính chứa bí quyết SUM.

Nhấp đề xuất vào ô tính và chọn Format Cells từ danh sách tùy chọn, thừa nhận Ctrl + 1 để mở hộp thoại Format Cells. Lựa chọn Custom từ Category menu và gõ một trong những định dạng 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ờ trong Excel

Để tính toán hiệu quả hơn 24 giờ, chúng ta có thể sử dụng công thức SUM, và chọn 1 trong các định dạng sau:

Định dạngHiển thịGiải thích
:mm30:10Giờ và phút
:mm:ss30:10:20Giờ, phút cùng giây
“hours”, mm “minutes”, ss “seconds”30 hours, 10 minutes, đôi mươi seconds
d h:mm:ss1 06:10:20Ngày, giờ, phút và 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

Để xem hiệu quả định dạng vào trang tính thực, coi hình tiếp 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 kết quả tính toán là số âm, nghĩa là các bạn lấy giá bán trị nhỏ tuổi 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 lớn hơn 24 giờ, 60 phút, 60 giây?

Để hiện thị một khoảng tầm 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à lại mã đối kháng vị thời hạn tương ứng phía bên trong dấu ngoặc vuông, ví dụ điển hình như , , tốt . Sau đây là công việc hướng dẫn bỏ ra tiết:

Chọn một hay những ô mà bạn có nhu cầu định dạng.Nhấp chuột yêu cầu vào những ô được lựa chọn rồi lại bấm 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 sẽ 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õ một trong những định dạng thời gian tiếp sau đây trong hộp Type:

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

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

Lớn rộng 60 giây:

Ảnh chụp screen dưới đây cho thấy thêm đị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 dạng khác. Bạn có thể sử dụng nó để hiển thị những khoảng chừ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ờ và phút:mm
Số giờ, phút, giây:mm:ss
Tổng số phút
Số phút và giây:ss
Tổng số giây

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

Để phần nhiều khoảng thời hạn đượ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 1-1 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ờ 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 cùng 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 lớn khoảng thời gian trên trông y hệt như chuỗi ký kết tự, mà lại chúng vẫn luôn là giá trị số, chính vì định dạng chữ số vào Excel chỉ biến hóa về hiệ tượng chứ thực chất không thay đổi. Vì chưng thế, bạn có thể tự vị cộng xuất xắc trừ phần nhiều khoảng thời hạn đã được định dạng một biện 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 mong mỏi cho một khoảng thời hạn cho sẵn, hãy lấy số giờ, phút, tuyệt giây mà bạn có nhu cầu cộng cho 1-1 vị thời hạn tương ứng trong một ngày (24 giờ, 1440 phút, 86400 giây), rồi cộng số mến cho thời gian bắt đầu.

Cộng hơn 24 giờ.

Thời gian bắt đầu + (N/24)

Cộng rộng 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, xuất xắc giây mà bạn muốn cộng.

Đây là một trong vài ví dụ công thức trên thực tế:

Để cùng 45 giờ đến thời gian ban đầu trong ô A2:

=A2+(45/24)

Để cộng 100 phút mang lại thời gian bước đầu trong ô A2:

=A2+(100/1440)

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

=A2+(200/86400)

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

Để trừ khoảng thời hạn trong Excel, hãy sử dụng công thức tương tự như nhưng hãy núm dấu cộng bởi dấu trừ nhé!

Trừ hơn 24 giờ

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

Trừ rộng 60 phút.

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

Trừ hơn 60 giây

Thời gian bắt đầ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 hạn được tính toán có dạng số thập phân, hãy sử dụng định dạng thời gian/ngày nhằm định dạng ô.Nếu sau khi sử dụng định dạng với ô hiển thị #####, thì thông thường ô cảm thấy không được rộng để hiển thị giá chỉ trị thời gian trong ngày. Để cách xử trí điều này, hãy không ngừng mở rộng chiều nhiều năm ô bằng phương pháp nhấp đúp loài chuột hay kéo size bên phải 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 hạn trong excel. Nếu như còn bất cứ thắc mắc gì thì đừng ngại ngần cmt xuống dưới để được giải đáp nhanh nhất có thể nhé!