Trong bài viết này, Học Excel Online sẽ trả lờ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ờ; cách cộng trừ thời hạn trong excel.

Bạn đang xem: Hàm so sánh 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 hạn âm vào ExcelCộng/trừ thời hạn trong ExcelCách cộng thời hạn trong ExcelCách hiển thị khoảng chừ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 hạn trong Excel thường được định hình số thập phân nhằm trông tương tự thời gian. Vì chưng chúng là số, nên bạn có thể cộng trừ thời hạn như những giá trị số khác.

Công thức đơn giản dễ dàng 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ế bao gồm thể có 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 cực hiếm trong ô A2 cùng 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 giá trị trong ô A2 với B2, kế bên chênh lệch ngày tháng giả dụ ô tính đựng cả quý giá ngày tháng cùng thời gian

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

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

Phần thập phân trong cột D đúng nhưng không tồn tại ý nghĩa. Để góp chúng báo tin có ích hơn, bạn có thể áp dụng định dạng thời hạn với một trong số mã sau đây:

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

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

Bây giờ, nhằm xem cách làm tính chênh lệch thời gian và mã thời gian vận động như cố gắng nào trong bảng tính thực sự. Cùng với Start time trong cột A với End time vào cột B, bạn cũng có thể sao chép phương pháp vào cột C và 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 gian trong từng cột:

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

Chú ý. Nếu thời hạn chênh lệch được hiển thị trấn #####, nghĩa là ô chứa bí quyết không đủ rộng để hiển thị hết thời gian hoặc tác dụng phép tính là một trong 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 để 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 giữa hai thời gian: =TEXT(B2-A2, “h:mm”)Tính toán chênh lệch giờ, phút cùng giây thân hai thời gian: =TEXT(B2-A2, “h:mm:ss”)
*
3 – bí quyết tính thời gian trong Excel

Chú ý.

Kết trái 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 trường hợp 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 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 giữa hai thời hạn bằng một đơn vị chức năng (giờ, phút, giây), bạn cần triển khai 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 bước đầu trong ô A2 cùng thời gian xong trong ô B2, các bạn hãy thực hiện phép tính dễ dàng và đơn giản là B2-A2 để đo lường chênh lệch thời gian, sau đó nhân cùng với 24.

=(B2-A2) * 24

Để tất cả số Complete hours, thực hiện hàm INT để làm cho tròn mang đến số nguyên hần nhất:

=INT((B2-A2) * 24)

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

 

Tổng số phút thân hai thời gian:

Để tính số phút thân 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, hiệu quả âm xẩy ra khi thời gian xong lớn rộng thời gian bắt đầ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 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 hạn với 86400

=(End time – Start time) * 86400

Trong ví dụ, công thức sẽ như sau:

=(B2-A2)* 86400

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

Chú ý. Định dạng General nên được tùy chỉnh 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 gian trong cùng một đơn vị thời gian và bỏ qua các đơn vị không giống

Để tính chênh lệch giữa hai thời hạn trong cùng một đối chọi vị, quăng quật qua các đơ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ỏ qua mất 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ờ với phút:

=SECOND(B2-A2)

Khi thực hiện hàm HOUR, MINUTE, SECOND, hãy lưu giữ rằng hiệu quả không được vượt thừa 24 giờ, 60 phút hoặc 60 giây.

*
7 – giải pháp tính thời gian 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ó thể sử dụng hàm Now với 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 áp dụng hàm NOW để lấy kết quả ngày mon và thời gian hiện tại, sau đó trừ đi ngày và thời gian bắt đầu.

Giả sử ô A2 chứa thời khắc bắt đầu, công thức =NOW()-A2 cho công dụng 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ó gồm dạng h:mm

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

 

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

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

Nếu thời gian bước đầu chỉ bao gồm 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 thiết yếu xác. Ví dụ, công thức sau trả về khoảng thời hạn từ ô A2 đế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 hạn trôi qua sẽ không được cập nhật theo thời gian thực tế, nó chỉ update khi bảng tính được lật lại hoặc đ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 dìm 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 để lấy kết quả thời gian tương tứng và hàm INT để tính chênh lệch ngày.Sau đó, bạn lồng ghép những hàm vào thuộc một bí quyết cùng những chuỗi text như sau :

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

 

*

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

 

*

Ngoài ra, chúng ta 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 xong (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 cùng hiển thị thời gian âm trong Excel

Khi giám sát chênhh lệch bái gian, chúng ta cũng có thể bắt gặp gỡ lỗi ##### vì độ chênh lệch này âm. Tuy nhiên, cũng đều có nhiều cách để hiển thị kết quả âm trong Excel.

Phương pháp 1. đổi khác 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à ghi lại chọn Use 1904 date system.

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

Nhấp OK nhằm lưu cài đặt mới, cực hiếm âm sẽ được 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 cách thức 1, chúng ta 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 gian (A2-B2) có lớn hơn 0 hay không và nó gồm hiện công dụng hay không. Ví như chênh lệch bé hơn 0, công thức thứ nhất sẽ tính trị tuyệt đối rồi thêm dấu trừ. Cách làm thứ nhị cho công dụng tương tự bởi định dạng thời gian âm “-h:: mm”.

*

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

Xem thêm: Cẩm Vân Là Ai? Tiểu Sử, Thông Tin Của Ca Sĩ Cẩm Vân Ca Sĩ Phan Cẩm Vân Đột Ngột Qua Đời Ở Tuổi 41

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

Về cơ bản, gồm 2 biện 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 vào 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 bài toán tính toán dễ dãi hơn, mặc dù nó không được cho phép việc cộng trừ hơn 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 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ờ đồng hồ 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 gian bắt đầ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ỳ số giờ nào (dưới/trên 24 giờ)

=Start time + (N hours/24)

Ví dụ, để cộng thêm 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ư rứa dấu + bởi dấu –

Ví dụ, trừ ô A2 cho 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, áp 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ừ 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 tham số máy 2:

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

Đây là 1 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 đế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, chia số phút mang lại 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ỗ lực 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 gian cụ thể

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

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

Để thêm vào đó số giây lớn hơn 59, thực hiện 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ách làm 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 đến 40 giây: =A2 – TIME(0,0,40)Trừ ô A2 mang đến 900 giây: =A2 – (900/86400)Cách cộng thời gian trong Excel

Phép cùng Excel thường thực hiện hàm SUM, và vận 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 hạn trong cột B và bạn muốn cộng bọn chúng với nhau. Các bạn viết công thức SUM như sau =SUM(B2:B4), tác dụng hiển thị theo định dạng mặc định như hh:mm:ss

 

*

Trong một vài ngôi trường hợp, định dạng thời gian mặc định rất có thể ổn, cơ mà đô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. Lúc đó, chúng ta không phải 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 phải vào ô tính và lựa chọn Format Cells từ danh sách tùy chọn, dìm Ctrl + 1 để mở vỏ hộp thoại Format Cells. Chọn Custom từ Category các mục và gõ một trong các đị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 giờ đồng hồ trong Excel

Để tính toán hiệu quả hơn 24 giờ, chúng ta có thể sử dụng phương pháp SUM, và lựa chọn 1 trong các đị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, đô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 and 20 seconds

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

*

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

Để hiện thị lên một khoảng thời gian lớn hơn 24 giờ, 60 phút, 60 giây, hãy sử dụng custom time format nơi mà lại mã 1-1 vị thời gian tương ứng bên trong dấu ngoặc vuông, ví dụ điển hình như , , xuất xắc . 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 buộc phải vào những ô được lựa chọn rồi lại nhấn vào phải vào Format Cells, hay chúng ta 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õ trong số những định dạng thời gian sau đây trong hộp Type:

Lớn rộng 24 giờ: :mm:ss giỏi :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 thêm định dạng thời gian “lớn hơn 24 giờ” trên thực tế:

Dưới đó là một vài format khác. Chúng ta cũng có thể sử dụng nó nhằm 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 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à 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 ảnh chụp screen trên), rất nhiều đị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ờ và 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

Để gần như khoảng thời gian được hiển thị làm việc trên dễ nắm bắt hơn so với người dùng, bạn cũng có thể bổ sung đối kháng vị thời hạn 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 cùng 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ù đa số khoảng thời gian trên trông hệt như chuỗi ký kết tự, mà lại chúng vẫn chính là giá trị số, chính vì định dạng chữ số vào Excel chỉ đổi khác về hiệ tượng chứ thực chất không cụ đổi. Do thế, bạn có thể tự vày cộng xuất xắc trừ đều khoảng thời gian đã được format một biện pháp bình thường, hãy tham chiếu bọn chúng trong công thức và sử dụng trong những phép tính khác.

Cách cộng trừ thời gian to hơn 24 giờ, 60 phút, 60 giây

Để cùng khoảng thời hạn mong muốn 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 đơn 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ố yêu mến cho thời hạn bắt đầu.

Cộng rộng 24 giờ.

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

Cộng hơn 60 phút.

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

Cộng rộng 60 giây.

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

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

Đây là 1 vài ví dụ phương pháp trên thực tế:

Để cộng 45 giờ mang lại thời gian ban đầu trong ô A2:

=A2+(45/24)

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

=A2+(100/1440)

Để cộng 200 giây mang lại 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 ô lẻ tẻ rồi tham chiếu ô đó trong công thức bao gồm dạng như trong hình ảnh chụp màn hình hiển thị dưới đây:

Để trừ khoảng thời hạn trong Excel, hãy áp dụng công thức tựa như nhưng hãy cố dấu cộng bằng dấu trừ nhé!

Trừ hơn 24 giờ

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

Trừ rộng 60 phút.

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 sau đây hiển thị các kết quả:

Lưu ý:

Nếu khoảng thời gian được tính toán có dạng số thập phân, hãy áp dụng định dạng thời gian/ngày nhằm định dạng ô.Nếu sau khoản thời gian sử dụng định dạng cùng ô hiển thị #####, thì thông thường ô không đủ rộng để hiển thị giá bán trị thời hạn trong ngày. Để cách xử trí điều này, hãy không ngừng mở rộng chiều lâu năm ô bằng cách nhấp đúp chuột hay kéo size bên cần của cột.

Đó là toàn bộ 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 kể 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é!