0.1 Cách tính chênh lệch thời gian vào 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 vào Excel

Trong nội dung bài viết này, Học Excel Online sẽ hướng dẫn những bí quyết có lợi nhằm cộng/trừ thời hạn, chênh lệch thời gian, đếm giờ…

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

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

Nhỏng các bạn đã biết, thời hạn vào Excel hay được định hình số thập phân nhằm trông tương đương thời gian. Vì chúng là số, bắt buộc bạn cũng có thể cùng trừ thời gian nhỏng những quý giá số không giống.Quý khách hàng vẫn xem: Cách tính thời gian

Công thức đơn giản nhất nhằm tính chênh lệch thời hạn 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 tiễn hoàn toàn có thể có khá nhiều dạng khác biệt, ví dụ:

Công thứcGiải thích
=A2-B2Tính toán thù chênh lệch thời hạn thân hai giá trị vào ô A2 và 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 gian chũm 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 giữa nhì quý giá vào ô A2 cùng B2, quanh đó chênh lệch ngày tháng giả dụ ô tính cất cả quý giá tháng ngày với thời gian

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


*

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

Mã thời gianGiải thích
hSố giờ trôi qua, ví dụ 4
h:mmSố giờ và số phú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:trăng tròn.

Bạn đang xem: Cách tính thời gian trong excel

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

Bây giờ đồng hồ, để thấy công thức tính chênh lệch thời hạn và mã thời hạn chuyển động ra làm sao trong bảng tính thực sự. Với Start time trong cột A và End time trong cột B, chúng ta cũng có thể coppy công thức 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 từng định hình thời hạn trong những cột:


*

Chú ý. Nếu thời hạn chênh lệch được hiển đô thị #####, nghĩa là ô đựng cách làm không được rộng nhằm hiển thị không còn thời gian hoặc hiệu quả phnghiền tính là một trong những giá trị â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ác để tính chênh lệch thời hạn vào Excel là áp dụng hàm TEXT .

Tính toán thù 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 ít giữa hai thời gian: =TEXT(B2-A2, “h:mm”)Tính toán thù chênh lệch tiếng, phút với giây thân nhị thời gian: =TEXT(B2-A2, “h:mm:ss”)


*

Chú ý.

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

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ởi một đơn vị (giờ đồng hồ, phút, giây), bạn cần tiến hành các phép tính sau.

Phép tính giờ đồng hồ thân nhì thời gian

=(End time – Start time) * 24

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

=(B2-A2) * 24

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

=INT((B2-A2) * 24)

 

Tổng số phút ít thân 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 có thể mang đến công dụng âm hoặc dương, hiệu quả âm xẩy ra Lúc thời gian xong to hơn thời gian ban đầu nhỏng hàng 5:

=(B2-A2)*1440

 

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

Để tính số giây thân hai thời hạn, 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ẽ nlỗi sau:

=(B2-A2)* 86400


Chụ ý. Định dạng General yêu cầu được thiết lập cấu hình mang đến ô tính đựng công thức tính chênh lệch thời gian nhằm tác dụng hiển thị đúng.

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

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

Chênh lệch giờ đồng hồ, bỏ qua mất phút với giây:

=HOUR(B2-A2)

Chênh lệch phút, bỏ qua giờ đồng hồ với giây:

=MINUTE(B2-A2)

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

=SECOND(B2-A2)

Khi sử dụng hàm HOUR, MINUTE, SECOND, hãy hãy nhớ là kết quả ko được quá vượt 24 giờ đồng hồ, 60 phút hoặc 60 giây.


Chú ý. Nếu thời hạn kết thúc lớn hơn thời hạn bắt đầu, ví dụ nếu 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 kể từ thời hạn bước đầu đến bây giờ

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

Giả sử ô A2 đựng thời gian bước đầu, cách làm =NOW()-A2 mang lại hiệu quả sau cùng với cột B được định dạng thời hạn cân xứng, vào ví dụ này nó có dạng h:mm

 

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


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


Chú ý. Khoảng thời gian trôi qua sẽ không được update theo thời gian thực tế, nó chỉ cập nhật Khi bảng tính được mnghỉ ngơi lại hoặc tính toán thù lại. Để update công thức, dìm Shift + F9 nhằm tính lại trang tính hoặc thừa nhận F9 nhằm tính lại toàn cục 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 and XX seconds”

Quý Khách thực hiện những hàm HOUR, MINUTE cùng SECOND để đưa công dụng thời gian tương tứng cùng hàm INT nhằm tính chênh lệch ngày.Sau kia, bạn lồng ghnghiền những hàm vào cùng một cách làm cùng những chuỗi text nhỏng sau :

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

 

Để ẩn quý giá 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, “,””) & IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) và ” minutes and “,””) & IF(SECOND(B2-A2)>0, SECOND(B2-A2) & ” seconds”,””)

 

Trong khi, bạn cũng có thể tính toán chênh lệch thời hạn bằng cách đem thời hạn ban đầu trừ thời gian ngừng (ví dụ = B2-A2) rồi tùy chỉnh thiết lập định dạng sau mang đến ô tính:

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


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

Khi tính tân oán chênhh lệch thờ gian, bạn cũng có thể phát hiện lỗi ##### bởi độ chênh lệch này âm. Tuy nhiên, cũng có khá nhiều phương pháp để hiển thị công dụng âm vào Excel.

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

Vào File > Options > Advanced, kéo tkhô giòn cuốn xuống When calculating this workbook cùng ghi lại lựa chọn Use 1904 date system.


Nhấp OK để giữ thiết đặt bắt đầu, 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 hạn âm bằng công thức

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


Chú ý. Hãy hãy nhờ rằng phương pháp thứ nhất tạo ra kết quả âm là quý hiếm số âm, công dụng hàm TEXT luôn là chuỗi vnạp năng lượng bạn dạng với không thể áp dụng vào phxay toán hoặc cách làm không giống.

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

Về cơ bản, bao gồm 2 giải pháp cộng/trừ thời hạn vào Excel:

Sử dụng hàm TIMESử dụng phép tân oán dựa vào khoảng thời gian (24), số phút ít (1440), số giây (86400) trong một ngày.

Hàm TIME(hour, minute, second) góp bài toán tính toán thù dễ ợt hơn, tuy nhiên nó ko cho phép bài toán 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 chừng thời gian lớn hơn, hãy áp dụng các phnghiền toán thù tiếp sau đây.

Tmê say khảo ngay: Giáo trình Excel 2020

Cộng/trừ giờ vào thời gian nỗ lực thể

Hàm thời gian để cộng thêm bên dưới 24 giờ

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

lấy ví dụ, vào ô A2 là thời gian ban đầu, bạn muốn thêm 2 tiếng đồng hồ vào đó, bí quyết đã là:

=A2 + TIME(2, 0, 0)

 

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

=Start time + (N hours/24)

Ví dụ, để cộng thêm 28 giờ đồng hồ vào ô A2, nhập công thức sau:

=A2 + (28/24)


Để trừ đi một số tiếng, áp dụng công thức tựa như nlỗi nắm lốt + bởi lốt –

ví dụ như, trừ ô A2 mang lại 3 giờ, nhập một trong các cách làm sau:

=A2-(3/24)

=A2-TIME(3,0,0)

Nếu bạn trừ số giờ lớn hơn 23, thực hiện công thức trước tiên.

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

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

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

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

Đây là 1 trong vài ba cách làm thực tế nhằm tính số phút ít vào Excel:

Công 20 phút ít vào A2: =A2 + TIME(0,đôi mươi,0)

Trừ A2 mang lại 30 phút: =A2 – TIME(0,30,0)

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

Trong phnghiền tính của doanh nghiệp, chia số phút cho 1440 – số phút ít trong một ngày cùng thêm thời gian bắt đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút, chũm dấu + thành lố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 nạm thể

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

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

Để cộng thêm 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 phương pháp tựa như tuy nhiên vắt vệt + 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 đến 40 giây: =A2 – TIME(0,0,40)Trừ ô A2 mang lại 900 giây: =A2 – (900/86400)Cách cộng thời gian trong Excel

Phxay cùng Excel hay thực hiện hàm SUM, và vận dụng format phù hợp mang lại công dụng.

Giả sử các bạn gồm một vài thời gian trong cột B và bạn có nhu cầu cộng chúng cùng nhau. Quý khách hàng viết bí quyết SUM như sau =SUM(B2:B4), kết quả hiển thị theo format mang định nhỏng hh:mm:ss

 

Trong một vài ba ngôi trường hòa hợp, định hình thời hạn khoác định hoàn toàn có thể ổn, tuy thế nhiều lúc 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. khi đó, chúng ta ko đề nghị phxay tính nào cả, bạn chỉ cần định hình lại thời hạn cho ô tính đựng bí quyết SUM.

Nhấp cần vào ô tính với chọn Format Cells trường đoản cú list tùy chọn, dấn Ctrl + 1 nhằm msinh sống hộp thoại Format Cells. Chọn Custom từ bỏ Category danh sách cùng gõ một trong số định dạng sau vào vỏ hộp Type:

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

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

Kết đúng như sau:


Cách cộng qua 24 tiếng trong Excel

Để tính toán kết quả hơn 24 giờ đồng hồ, chúng ta có thể áp dụng công thức SUM, và chọn một trong số định hình 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, 20 seconds
d h:mm:ss1 06:10:20Ngày, giờ đồng hồ, phút í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

Để coi tác dụng định dạng vào trang tính thực, coi hình sau đây (cách làm SUM được nhtràn lên ô A9 tới ô A13:

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

Crúc ý.

Xem thêm: Thực Hư Chuyện Ăn Sữa Chua Nếp Cẩm Giảm Cân, Ăn Gạo Nếp Cẩm Có Béo Không, Có Tốt Không

Định dạng thời hạn chỉ áp dụng đến số dương. Nếu kết quả tính toán thù là số âm, tức thị chúng ta mang quý giá bé dại rộng trừ mang lại quý hiếm lớn hơn, tác dụng đã hiển thị #####.


Bài viết xem nhiều