Các hàm thời gian trong excel

     
0.1 Cách tính chênh lệch thời gian trong Excel0.2 Cách tính cùng hiển thị thời hạn âm trong Excel0.3 Cộng/trừ thời gian trong Excel

Trong nội dung bài viết này, Học Excel Online vẫn khuyên bảo hầu hết bí quyết bổ ích để cộng/trừ thời gian, chênh lệch thời hạn, đếm giờ; giải pháp cộng trừ thời gian trong excel.

Bạn đang xem: Các hàm thời gian trong excel

Cách tính chênh lệch thời hạn vào Excel

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

Nlỗi các bạn đã biết, thời gian vào Excel hay được định dạng số thập phân để trông giống như thời hạn. Vì bọn chúng là số, bắt buộc chúng ta cũng có thể cùng trừ thời gian như các quý hiếm số không giống.

Công thức đơn giản dễ dàng duy 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 hạn thực tế hoàn toàn có thể có nhiều dạng không giống nhau, ví dụ:

Công thứcGiải thích
=A2-B2Tính tân oán chênh lệch thời gian giữa nhị cực hiếm vào ô A2 và B2
=TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”)Tính toán chênh lệch thời gian thân hai thời gian thế 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 nhì giá trị vào ô A2 và B2, bên cạnh chênh lệch ngày tháng giả dụ ô tính chứa cả cực hiếm ngày tháng cùng thời gian

Trong khối hệ thống Excel, thời gian biểu lộ ở trong phần thập phân, các bạn sẽ bao gồm tác dụng như 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 báo tin có lợi rộng, chúng ta có thể áp 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ờ đồng hồ với số phút ít trôi qua, ví dụ 4:10
h:mm:ssSố giờ, số phút ít và số giây trôi qua, ví dụ 4:10:20.

Để vận dụng định hình thời gian, dấn Ctrl + 1 nhằm mngơi nghỉ hộp thoại Format Cells, chọn Custom trường đoản cú Category các mục với gõ mã thời hạn vào vỏ hộp Type.

Bây tiếng, giúp xem công thức tính chênh lệch thời gian và mã thời hạn chuyển động ra làm sao trong bảng tính thực thụ. Với Start time vào cột A cùng End time trong cột B, chúng ta cũng có thể coppy 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 biệt tùy theo format thời hạn trong mỗi cột:


*

2 – cách 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 là ô cất bí quyết không đủ rộng lớn để hiển thị hết thời gian hoặc hiệu quả phnghiền tính là một quý giá âm.

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

Một chuyên môn không giống để tính chênh lệch thời gian trong Excel là áp dụng hàm TEXT .

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

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


Chú ý.

Kết trái của hàm TEXT luôn luôn là văn uống bản. Các quý giá text được cnạp năng lượng lề trài trong những cột C, D, E vào hình trên. Trong vài tình huống nhất quyết, điều này hoàn toàn có thể là một trong tinh giảm vày bạn sẽ không thể thực hiện công dụng này nhằm tiếp tục tính toán thù.

Công thức 3. Tính giờ đồng hồ, phút, giây giữa hai thời gian

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

Phnghiền tính giờ thân hai thời gian

=(End time – Start time) * 24

Giả sử thời gian bắt đầu vào ô A2 với thời gian xong trong ô B2, bạn hãy áp dụng phép tính đơn giản dễ dàng là B2-A2 nhằm tính tân oán chênh lệch thời hạn, sau đó nhân cùng 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 ổn hần nhất:

=INT((B2-A2) * 24)


*

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


 

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

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

=(End time – Start time) * 1440

Công thức hoàn toàn có thể cho kết quả âm hoặc dương, kết quả âm xảy ra khi thời gian ngừng lớn hơn thời gian bắt đầu nlỗi hàng 5:

=(B2-A2)*1440


*

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


 

Tổng số giây giữa nhì thời gian:

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

=(End time – Start time) * 86400

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

=(B2-A2)* 86400


*

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


Chụ ý. Định dạng General phải được cấu hình thiết lập đến ô tính đựng cách làm 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 hạn vào và một đơn vị thời hạn cùng bỏ lỡ những đơn vị không giống

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

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

=HOUR(B2-A2)

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

=MINUTE(B2-A2)

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

=SECOND(B2-A2)

khi sử dụng hàm HOUR, MINUTE, SECOND, hãy nhớ rằng hiệu quả không được quá thừa 24 giờ đồng hồ, 60 phút hoặc 60 giây.


*

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


Crúc ý. Nếu thời gian kết thúc lớn hơn thời hạn ban đầu, cụ thể ví như hiệu quả là số âm, phương pháp đã 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 ban đầu đến bây giờ

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

Giả sử ô A2 đựng thời điểm bắt đầu, công thức =NOW()-A2 mang đến công dụng sau cùng với cột B được định hình thời hạn phù hợp, vào ví dụ này nó có dạng h:mm


*

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


 

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


*

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


Nếu thời hạn bắt đầu chỉ bao gồm giờ đồng hồ phút mà lại không tồn tại ngày, bạn cần thực hiện hàm Time để tính toán khoảng tầm thời hạn trôi đi một phương pháp đúng chuẩn. lấy một ví dụ, cách làm sau trả về khoảng chừng thời hạn từ bỏ ô A2 mang đến tiếng.

Xem thêm: Không Copy Paste Được Trong Cad, Lệnh Paste Trong Autocad

=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 update theo thời hạn thực tế, nó chỉ cập nhật lúc bảng tính được mngơi nghỉ lại hoặc tính toán thù lại. Để cập nhật bí quyết, dìm Shift + F9 để tính lại trang tính hoặc nhận F9 nhằm tính lại toàn bộ 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 & XX seconds”

Quý Khách sử dụng các hàm HOUR, MINUTE SECOND để đưa hiệu quả thời gian tương tứng cùng hàm INT để tính chênh lệch ngày.Sau đó, chúng ta lồng ghnghiền các hàm vào và một công thức cùng các chuỗi text nhỏng sau :

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

 

*

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

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

 

*

Bên cạnh đó, bạn cũng có thể tính toán chênh lệch thời gian bằng phương pháp mang thời gian bắt đầu trừ thời gian dứt (ví dụ = B2-A2) rồi thiết lập cấu hình format sau đến ô 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 gian âm vào Excel

lúc tính toán thù chênhh lệch thờ 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ó rất nhiều cách để hiển thị kết quả âm vào Excel.

Pmùi hương pháp 1. Ttốt 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 khô cuốn nắn xuống When calculating this workbook cùng lưu lại chọn Use 1904 date system.


*

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


Nhấp OK để lưu cài đặt new, giá trị âm sẽ được hiển thị nhỏng sau:

*

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

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

*

Chú ý. Hãy nhớ là phương pháp đầu tiên cho ra kết quả âm là cực hiếm số âm, công dụng hàm TEXT luôn luôn là chuỗi văn phiên bản với không thể sử dụng trong phnghiền toán thù hoặc bí quyết khác.

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

Về cơ bạn dạng, có 2 bí quyết cộng/trừ thời gian trong Excel:

Sử dụng hàm TIMESử dụng phép toán thù dựa vào thời gian (24), số phút í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 thuận lợi rộng, tuy nhiên nó ko cho phép câu hỏi 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 tân oán khoảng thời gian to hơn, hãy áp dụng các phxay tân oán sau đây.

Ttê mê khảo ngay: Giáo trình Excel 2020

Cộng/trừ tiếng vào thời gian nuốm thể

Hàm thời hạn nhằm thêm vào đó bên dưới 24 giờ

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

lấy ví dụ như, vào ô A2 là thời gian bắt đầu, bạn có nhu cầu thêm 2 tiếng đồng hồ vào kia, cách làm đang là:

=A2 + TIME(2, 0, 0)

 

*

Công thức nhằm cộng ngẫu nhiên số giờ làm sao (dưới/bên trên 24 giờ)

=Start time + (N hours/24)

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

=A2 + (28/24)

*

Để trừ đi một số trong những tiếng, áp dụng phương pháp giống như như gắng vệt + bởi lốt –

lấy ví dụ như, trừ ô A2 đến 3h, nhập một trong những công thức sau:

=A2-(3/24)

=A2-TIME(3,0,0)

Nếu bạn trừ thời gian to hơn 23, áp dụng phương pháp đầu tiên.

Cách cộng/trừ phút ít vào thời hạn cầm cố thể

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

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

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

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

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

Trừ A2 cho 30 phút: =A2 – TIME(0,30,0)

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

Trong phnghiền tính của công ty, phân tách số phút đến 1440 – số phút trong một ngày với thêm thời hạn bước đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút, cố kỉnh 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 rứa thể

Để thêm vào đó số giây thấp hơn 60 vào thời hạn rõ ràng, sử dụng hàm TIME:

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

Để thêm vào đó số giây to 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 bí quyết tương tự như nhưng lại cố gắng lốt + thành dấu -.

Trên trang tính Excel, phương pháp trông nhỏng 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 đến 900 giây: =A2 – (900/86400)Cách cộng thời gian vào Excel

Phép cộng Excel thường xuyên áp dụng hàm SUM, và vận dụng format tương xứng mang lại tác dụng.

Giả sử bạn bao gồm một vài thời hạn vào cột B và bạn muốn cùng bọn chúng cùng nhau. Quý khách hàng viết bí quyết SUM nhỏng sau =SUM(B2:B4), tác dụng hiển thị theo định dạng mang định nhỏng hh:mm:ss

 

*

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

Nhấp đề nghị vào ô tính với chọn Format Cells tự danh sách tùy lựa chọn, nhận Ctrl + 1 nhằm mlàm việc hộp thoại Format Cells. Chọn Custom trường đoản cú Category menu cùng gõ một trong những định dạng sau vào hộp Type:

Hiển thị thời hạn dưới dạng phút ít cùng 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ờ trong Excel

Để tính toán thù kết quả hơn 24 tiếng, chúng ta có thể thực hiện phương pháp SUM, cùng chọn một trong những định hình sau:

Định dạngHiển thịGiải thích
:mm30:10Giờ cùng phút
:mm:ss30:10:20Giờ, phút ít với 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 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 & đôi mươi seconds

Để xem công dụng format vào trang tính thực, coi hình sau đây (công thức SUM được nhập lệ ô A9 cho tới ô A13:

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

Crúc ý.

Xem thêm: Lengendary Sword - Lệnh Command Block Kiếm Mạnh Nhất

Định dạng thời gian chỉ áp dụng cho số dương. Nếu kết quả tính toán là số âm, tức là bạn lấy quý hiếm nhỏ rộng trừ mang lại quý hiếm to hơn, kết quả sẽ hiển thị #####.

*

Đó là cục bộ cách để những bạn có thể phương pháp cùng trừ thời hạn trong excel. Nếu còn bất cứ thắc mắc gì thì đừng trinh nữ nngay sát cmt xuống bên dưới và để được đáp án nkhô hanh duy nhất nhé!


Chuyên mục: Công nghệ