Hàm điều kiện thời gian trong excel

Trong nội dung bài viết này, Học Excel Online vẫn chỉ dẫn đều công thức có ích để cộng/trừ thời gian, chênh lệch thời hạn, đếm giờ; bí quyết cùng trừ thời gian vào excel.

You watching: Hàm điều kiện thời gian trong excel


Cách tính chênh lệch thời gian vào ExcelCách tính cùng hiển thị thời hạn âm trong 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 hạn to hơn 24 tiếng, 60 phút ít, 60 giây?Cách cùng trừ thời gian to hơn 24 giờ đồng hồ, 60 phút í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ỏng các bạn vẫn biết, thời hạn vào Excel thường xuyên được định dạng số thập phân để trông như là thời hạn. Vì bọn chúng là số, đề xuất chúng ta cũng có thể cộng trừ thời gian nhỏng các cực hiếm số khác.

Công thức đơn giản dễ dàng duy nhất nhằm 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ế hoàn toàn 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 thù chênh lệch thời hạn giữa nhị giá trị 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 thù chênh lệch thời gian thân hai giá trị trong ô A2 cùng B2, quanh đó chênh lệch ngày tháng nếu như ô tính cất cả quý giá tháng ngày và thời gian

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

*
1 – cách tính thời gian vào Excel

Phần thập phân trong cột D đúng nhưng không tồn tại ý nghĩa. Để giúp chúng cung cấp thông tin bổ ích hơn, chúng ta cũng có thể vận dụng format thời hạn với cùng 1 trong số mã sau đây:

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

Để vận dụng format thời gian, dìm Ctrl + 1 nhằm mở vỏ hộp thoại Format Cells, chọn Custom tự Category danh mục cùng gõ mã thời hạn vào hộp Type.

Bây tiếng, giúp thấy bí quyết tính chênh lệch thời gian cùng mã thời gian hoạt động thế nào trong bảng tính thực thụ. Với Start time trong cột A cùng End time trong cột B, chúng ta có thể coppy phương pháp vào cột C cùng E:


*

*

=$B2-$A2

Thời gian chênh lệch được hiển thị không giống nhau tùy thuộc vào định hình thời hạn trong những cột:

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

Crúc ý. Nếu thời gian chênh lệch được hiển đô thị #####, tức thị ô chứa cách làm không được rộng để hiển thị không còn thời gian hoặc hiệu quả phnghiền tính là 1 trong cực hiếm âm.

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

Một nghệ thuật không giống nhằm tính chênh lệch thời hạn trong Excel là sử dụng hàm TEXT .

Tính toán chênh lệch giờ đồng hồ giữa hai thời gian: =TEXT(B2-A2, “h”)Tính tân oán chênh lệch giờ đồng hồ cùng phút thân nhì thời gian: =TEXT(B2-A2, “h:mm”)Tính toán thù chênh lệch tiếng, phút ít và giây giữa hai thời gian: =TEXT(B2-A2, “h:mm:ss”)
*
3 – cách tính thời gian vào Excel

Chú ý.

Kết quả của hàm TEXT luôn là văn bạn dạng. Các giá trị text được căn uống lề trài trong những cột C, D, E trong hình trên. Trong vài ba trường hợp nhất quyết, điều này rất có thể là một hạn chế do các bạn sẽ bắt buộc sử dụng hiệu quả này nhằm tiếp tục tính tân oán.

Công thức 3. Tính tiếng, phút, giây thân nhì thời gian

Để hiển thị chênh lệch giữa nhì thời gian bằng một đơn vị chức năng (giờ, phút, giây), bạn cần tiến hành những phép tính sau.

Phép tính giờ thân nhị thời gian

=(End time – Start time) * 24

Giả sử thời gian ban đầu trong ô A2 với thời gian xong vào ô B2, bạn hãy sử dụng phép tính dễ dàng và đơn giản là B2-A2 nhằm tính toán chênh lệch thời hạn, tiếp đến nhân với 24.

=(B2-A2) * 24

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

=INT((B2-A2) * 24)

*
4 – cách tính thời gian vào Excel

 

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

Để tính số phút giữa hai thời gian, nhân chênh lệch thời gian cùng với 1440

=(End time – Start time) * 1440

Công thức hoàn toàn có thể đến tác dụng âm hoặc dương, kết quả âm xẩy ra lúc thời hạn hoàn thành lớn hơn thời gian bước đầu như mặt hàng 5:

=(B2-A2)*1440

*
5 – phương pháp tính thời hạn vào Excel

 

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

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

=(End time – Start time) * 86400

Trong ví dụ, cách làm đang nlỗi sau:

=(B2-A2)* 86400

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

Crúc ý. Định dạng General buộc phải được tùy chỉnh mang đến ô tính chứa cách làm tính chênh lệch thời gian nhằm kết quả hiển thị đúng.

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

Để tính chênh lệch thân nhì thời gian trong cùng một đơn vị, bỏ lỡ các đơn vị chức năng thời gian khác, hãy áp dụng một trong số hàm sau:

Chênh lệch tiếng, bỏ lỡ phút ít và giây:

=HOUR(B2-A2)

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

=MINUTE(B2-A2)

Chênh lệch giây, bỏ qua tiếng và phút:

=SECOND(B2-A2)

Lúc sử dụng hàm HOUR, MINUTE, SECOND, hãy hãy nhớ là hiệu quả ko được vượt vượt 24 giờ, 60 phút ít hoặc 60 giây.

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

Chú ý. Nếu thời hạn hoàn thành lớn hơn thời gian ban đầu, cụ thể nếu hiệu quả là số âm, cách làm đang hiển thị lỗi #NUM!

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

Để tính thời hạn sẽ trôi qua, chúng ta có thể sử dụng hàm Now và Time. Trường phù hợp khoảng thời gian trôi qua dưới 24 giờ đồng hồ, bạn chỉ việc áp dụng hàm NOW để mang kết quả ngày tháng cùng thời gian hiện thời, tiếp đến trừ đi ngày cùng thời gian ban đầu.

Giả sử ô A2 cất thời khắc bắt đầu, bí quyết =NOW()-A2 mang lại công dụng sau cùng với cột B được định hình thời gian tương xứng, trong ví dụ này nó có dạng h:mm

*
8 – cách tính thời gian vào Excel

 

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

*
9 – phương pháp tính thời hạn vào Excel

Nếu thời gian bắt đầu chỉ bao gồm tiếng phút mà lại không tồn tại ngày, bạn phải sử dụng hàm Time để tính toán khoảng chừng thời hạn trôi đi một biện pháp chính xác. lấy ví dụ, bí quyết sau trả về khoảng tầm thời hạn trường đoản cú ô A2 đến giờ đồng hồ.

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

*
10- cách tính thời gian vào Excel

Crúc ý. Khoảng thời hạn trôi qua sẽ không được cập nhật theo thời gian thực tiễn, nó chỉ cập nhật Lúc bảng tính được msinh sống lại hoặc tính toán lại. Để cập nhật công thức, dấn Shift + F9 để tính lại trang tính hoặc dấn F9 nhằm 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 & XX seconds”

quý khách thực hiện các hàm HOUR, MINUTE với SECOND để mang kết quả thời hạn tương tứng cùng hàm INT nhằm tính chênh lệch ngày.Sau đó, các bạn lồng ghxay những hàm vào và một phương pháp thuộc các chuỗi text nlỗi sau :

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

 

*

Ngoài ra, chúng ta có thể tính tân oán chênh lệch thời gian bằng cách đem thời gian bước đầu trừ thời gian xong (ví dụ = B2-A2) rồi thiết lập định hình sau mang lại ô tính:

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

*
11 – cách tính thời gian vào Excel

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

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

Phương pháp 1. Txuất xắc đổi hệ thông ngày tháng của Excel thành 1904 date system

Vào File > Options > Advanced, kéo thanh khô cuốn xuống When calculating this workbook và khắc ghi chọn Use 1904 date system.

*
12 – cách tính thời gian vào Excel

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

*

Pmùi hương pháp 2. Tính toán thời gian âm bằng công thức

Nếu không thích áp dụng phương pháp 1, chúng ta có thể áp dụng bí quyết 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ả nhị công thức đã soát sổ chênh lệch thời gian (A2-B2) bao gồm to hơn 0 hay là không với nó gồm hiện tại công dụng hay là không. Nếu chênh lệch bé hơn 0, cách làm thứ nhất sẽ tính trị tuyệt vời và hoàn hảo nhất rồi thêm vệt trừ. Công thức sản phẩm công nghệ nhị đến tác dụng tương tự như bằng định hình thời hạn âm “-h:: mm”.

*

Crúc ý. Hãy nhớ rằng cách thức đầu tiên cho ra kết quả âm là quý giá số âm, công dụng hàm TEXT luôn là chuỗi vnạp năng lượng bạn dạng cùng tất yêu thực hiện vào phnghiền toán thù hoặc công thức không giống.

See more: Cách Đăng Kí Nick Ninja School Online, Ninja School Online

Cộng/trừ thời gian vào Excel

Về cơ phiên bản, tất cả 2 cách cộng/trừ thời hạn trong Excel:

Sử dụng hàm TIMESử dụng phép toán thù dựa vào số giờ (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 tân oán thuận lợi rộng, mặc dù nó ko có thể chấp nhận được bài toán cộng trừ hơn 23h, 59 phút hoặc 59 giây. Nếu bạn có nhu cầu tính tân oán khoảng tầm thời gian lớn hơn, hãy thực hiện những phxay tân oán sau đây.

Tđắm say khảo ngay: Giáo trình Excel 2020

Cộng/trừ giờ đồng hồ vào thời gian rứa thể

Hàm thời hạn nhằm cộng thêm dưới 24 giờ

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

lấy một ví dụ, trong ô A2 là thời hạn bắt đầu, bạn có nhu cầu thêm 2 giờ vào kia, công thức đang là:

=A2 + TIME(2, 0, 0)

 

*

Công thức để cộng ngẫu nhiên thời gian như thế nào (dưới/bên trên 24 giờ)

=Start time + (N hours/24)

lấy ví dụ như, nhằm cộng thêm 28 giờ vào ô A2, nhập phương pháp sau:

=A2 + (28/24)

*

Để trừ đi một số trong những tiếng, sử dụng phương pháp tựa như nhỏng thế vết + bằng lốt –

ví dụ như, trừ ô A2 mang đến 3h, nhập một trong số bí quyết sau:

=A2-(3/24)

=A2-TIME(3,0,0)

Nếu chúng ta trừ khoảng thời gian lớn hơn 23, áp dụng phương pháp đầu tiên.

Cách cộng/trừ phút vào thời gian gắng thể

Cộng/trừ bên dưới 60 phút

Sử dụng hàm TIME để cộng trừ số phút ít bạn muốn vào tđê mê số trang bị 2:

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

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

Công trăng tròn phút ít vào A2: =A2 + TIME(0,trăng tròn,0)

Trừ A2 đến 30 phút: =A2 – TIME(0,30,0)

Cộng trừ bên trên 60 phút

Trong phxay tính của người sử dụng, phân chia số phút cho 1440 – số phút ít trong một ngày cùng thêm thời hạn bước đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút, nỗ lực vết + 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 thế thể

Để cộng thêm số giây thấp hơn 60 vào thời gian rõ ràng, sử 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 bí quyết tựa như tuy vậy cố kỉnh lốt + thành vệt -.

Trên trang tính Excel, cách làm trông nlỗi 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 cho 900 giây: =A2 – (900/86400)Cách cùng thời hạn vào Excel

Phép cùng Excel hay áp dụng hàm SUM, và áp dụng định dạng tương xứng cho hiệu quả.

Giả sử bạn tất cả một vài ba thời gian vào cột B với bạn có nhu cầu cộng chúng cùng nhau. Quý Khách viết cách làm SUM nlỗi 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 ba trường vừa lòng, định hình thời gian mang định hoàn toàn có thể ổn, tuy nhiên nhiều 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 bắt buộc phnghiền tính làm sao cả, chúng ta chỉ việc định hình lại thời hạn mang đến ô tính cất phương pháp SUM.

Nhấp đề xuất vào ô tính và chọn Format Cells từ bỏ list tùy chọn, thừa nhận Ctrl + 1 nhằm mngơi nghỉ vỏ hộp thoại Format Cells. Chọn Custom từ Category danh sách cùng gõ một trong số định hình sau vào vỏ hộp Type:

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

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

Kết quả thật sau:

*

Cách cùng qua 24 tiếng vào Excel

Để tính toán kết quả rộng 24 tiếng, bạn có thể thực hiện bí quyết SUM, với lựa chọn một 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 với 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ờ đồng hồ, phút í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 trăng tròn seconds

Để coi kết quả định dạng vào trang tính thực, coi hình tiếp sau đây (bí quyết SUM được nhtràn vào ô A9 cho tới ô A13:

=SUM($B$2:$B$4)

Crúc ý. Định dạng thời hạn chỉ vận dụng mang lại số dương. Nếu tác dụng tính toán là số âm, nghĩa là các bạn mang giá trị nhỏ tuổi rộng trừ mang đến quý hiếm lớn hơn, hiệu quả đã hiển thị #####.

*

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

Để hiện thị lên một khoảng thời hạn to hơn 24 giờ đồng hồ, 60 phút ít, 60 giây, hãy sử dụng custom time format nơi cơ mà mã đơn vị chức năng thời hạn khớp ứng nằm trong lốt ngoặc vuông, chẳng hạn như , , hay . Dưới đó là quá trình khuyên bảo đưa ra tiết:

Chọn một xuất xắc các ô mà lại bạn muốn định hình.Nhấp con chuột nên vào các ô được chọn rồi lại nhấp chuột đề xuất vào Format Cells, tuyệt chúng ta cũng có thể nhấn Ctrl + 1, làm việc này để giúp msinh sống 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 hình thời gian sau đây trong hộp Type:

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

Dưới đấy là một vài ba định hình không giống. Chúng ta rất có thể thực hiện nó để hiển thị đều khoảng chừng thời gian to hơn chiều dài đơn vị chức năng thời hạn chuẩn chỉnh.

Miêu tảMã định dạng
Tổng số giờ
Số tiếng cùng phút:mm
Số giờ đồng hồ, phút, giây:mm:ss
Tổng số phút
Số phút ít và giây:ss
Tổng số giây

khi được áp dụng mang lại dữ liệu chủng loại của chúng ta (Thời gian tổng số 50:40 vào hình họa chụp screen trên), số đông định hình thời hạn này đang mang đến 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ố tiếng, phút, giây50:40:30:mm:ss
5Số phút3040
6Sốphút ít với giây3040:30:ss
7Số giây182430

Để đầy đủ khoảng chừng thời gian được hiển thị sống bên trên dễ hiểu hơn so với người dùng, bạn có thể bổ sung cập nhật đơn vị thời gian bởi 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ờ đồng hồ cùng phút50 giờ với 40 phút “số giờ 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 ít cùng 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 mặc dù các khoảng chừng thời hạn bên trên trông y như chuỗi ký tự, dẫu vậy chúng vẫn luôn là giá trị số, chính vì định dạng văn bản số vào Excel chỉ chuyển đổi về hiệ tượng chđọng thực chất không biến đổi. Vì vắt, bạn cũng có thể thoải mái cùng tốt trừ hồ hết khoảng thời gian đã làm được định hình một bí quyết bình thường, hãy tmê say chiếu chúng vào phương pháp và thực hiện trong số phép tính không giống.

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

Để cùng khoảng tầm thời hạn mong ước cho một khoảng thời hạn đến sẵn, hãy đem số giờ, phút ít, giỏi giây nhưng bạn muốn cộng mang lại đơn vị thời gian khớp ứng trong một ngày (24 giờ đồng hồ, 1440 phút ít, 86400 giây), rồi cộng số thương thơm mang đến thời hạn ban đầu.

Cộng hơn 24 giờ.

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

Cộng hơn 60 phút í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 số đó, N là số giờ, phút, hay giây nhưng bạn muốn cùng.

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

Để cộng 45 giờ đồng hồ mang lại thời hạn bắt đầu trong ô A2:

=A2+(45/24)

Để cùng 100 phút ít mang lại thời hạn bắt đầu vào ô A2:

=A2+(100/1440)

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

=A2+(200/86400)

hay những, chúng ta cũng có thể nhập khoảng chừng thời gian đề nghị cùng vào một trong những ô lẻ tẻ rồi tsay mê chiếu ô kia vào công thức có dạng như vào ả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ách làm tương tự dẫu vậy hãy ráng dấu cùng bằng lốt trừ nhé!

Trừ hơn 24 giờ

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

Trừ hơn 60 phút.

See more: Không Gõ Được Tiếng Việt Trong Skype Windows 10, Gõ Tiếng Việt Trong Skype

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

Trừ rộng 60 giây

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

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

Lưu ý:

Nếu khoảng thời gian được tính toán gồm dạng số thập phân, hãy thực hiện format thời gian/ngày để format ô.Nếu sau thời điểm áp dụng định dạng và ô hiển thị #####, thì thông thường ô cảm thấy không được rộng lớn nhằm hiển thị quý hiếm thời hạn trong thời gian ngày. Để giải pháp 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 con chuột tốt kéo form mặt nên của cột.

Đó là toàn thể phương pháp để các chúng ta có thể bí quyết cộng trừ thời hạn trong excel. Nếu còn bất cứ thắc mắc gì thì chớ mắc cỡ nngay gần cmt xuống dưới sẽ được lời giải nkhô hanh duy nhất nhé!