CÁCH TÍNH 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 vào Excel

Trong bài viết này, Học Excel Online đang trả lời đều bí quyết có lợi để cộng/trừ thời gian, 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

Nlỗi các bạn đang biết, thời hạn vào Excel thường được format số thập phân để trông tương tự thời gian. Vì chúng là số, bắt buộc chúng ta có thể cộng trừ thời gian như những quý hiếm số khác.Quý Khách đã xem: Cách tính thời gian

Công thức dễ dàng và đơn giản nhất nhằm tính chênh lệch thời gian là:

=End time – Start time


*

*

Tùy vào cấu tạo tài liệu, chênh lệch thời hạn thực tiễn có thể có không ít dạng khác nhau, ví dụ:

Công thứcGiải thích
=A2-B2Tính tân oán 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 tân oán chênh lệch thời hạn thân nhị thời gian thay 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ị trong ô A2 cùng B2, kế bên chênh lệch ngày tháng giả dụ ô tính chứa cả quý hiếm ngày tháng và thời gian

Trong hệ thống Excel, thời hạn biểu hiện tại phần thập phân, bạn sẽ tất cả kết quả nlỗi sau:


*

Phần thập phân trong cột D đúng tuy vậy không tồn tại ý nghĩa. Để góp bọn chúng đưa thông tin có ích rộng, bạn có thể vận dụng định dạng thời hạn với cùng 1 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ờ với số phút ít trôi qua, ví dụ 4:10
h:mm:ssSố tiếng, số phút với số giây trôi qua, ví dụ 4:10:20.

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

Để vận dụng định dạng thời hạn, dấn Ctrl + 1 để msinh hoạt hộp thoại Format Cells, lựa chọn Custom từ Category các mục với gõ mã thời hạn vào vỏ hộp Type.

Bây giờ đồng hồ, giúp xem phương pháp tính chênh lệch thời hạn cùng mã thời gian chuyển động ra sao vào bảng tính thực thụ. Với Start time trong cột A cùng End time trong cột B, bạn cũng có thể coppy công thức vào cột C với E:

=$B2-$A2

Thời gian chênh lệch được hiển thị khác nhau tùy theo định dạng thời hạn trong những cột:


*

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

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

Một nghệ thuật khác để tính chênh lệch thời hạn vào Excel là thực hiện hàm TEXT .

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


*

Chụ ý.

Kết quả của hàm TEXT luôn luôn là văn phiên bản. Các quý hiếm text được cnạp năng lượng lề trài trong số cột C, D, E trong hình bên trên. Trong vài tình huống nhất quyết, điều này có thể là 1 hạn chế bởi bạn sẽ bắt buộc thực hiện kết quả này để liên tục tính toán thù.

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

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

Phnghiền tính tiếng giữa nhị thời gian

=(End time – Start time) * 24

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

=(B2-A2) * 24

Để gồm số Complete hours, áp dụng hàm INT để triển khai tròn đến số nguyên hần nhất:

=INT((B2-A2) * 24)

 

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

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

=(End time – Start time) * 1440

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

=(B2-A2)*1440

 

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

Để tính số giây thân nhị 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ụ, bí quyết vẫn nhỏng sau:

=(B2-A2)* 86400


Crúc ý. Định dạng General cần được cấu hình thiết lập cho ô tính đựng cách làm tính chênh lệch thời gian để 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ị chức năng thời hạn và bỏ qua mất những đơn vị khác

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

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

=HOUR(B2-A2)

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

=MINUTE(B2-A2)

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

=SECOND(B2-A2)

lúc sử dụng hàm HOUR, MINUTE, SECOND, hãy nhớ rằng công dụng không được thừa vượt 24 giờ đồng hồ, 60 phút ít hoặc 60 giây.


Chụ ý. Nếu thời hạn xong xuôi to hơn thời gian bước đầu, cụ thể trường hợp kết quả là số âm, bí quyết vẫn hiển thị lỗi #NUM!

Công thức 5. Tính thời gian trôi qua kể từ thời gian bước đầu cho bây giờ

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

Giả sử ô A2 đựng thời khắc bước đầu, cách làm =NOW()-A2 đến công dụng sau với cột B được format thời gian tương xứng, trong ví dụ này nó gồm dạng h:mm

 

Trong ngôi trường đúng theo khoảng tầm thời gian trôi qua vượt vượt 24 giờ, áp dụng một trong các format vài ngày sau, ví dụ d ‘days’ h:mm:ss như vào hình sau:


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


Chú ý. Khoảng thời gian trôi qua sẽ không còn đượ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 mngơi nghỉ lại hoặc tính toán thù lại. Để cập nhật bí quyết, dấn Shift + F9 để tính lại trang tính hoặc nhấn F9 nhằm tính lại toàn cục bảng tính.

Xem thêm:

Công thức 6. Hiển thị chênh lệch thời hạn “XX days, XX hours, XX minutes và XX seconds”

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

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

 

Hình như, chúng ta có thể tính toán thù chênh lệch thời hạn bằng phương pháp đem thời hạn bắt đầu trừ thời hạn xong (ví dụ = B2-A2) rồi thiết lập định hình sau cho ô tính:

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


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

Khi tính toán chênhh lệch thờ gian, bạn cũng có thể bắt gặp lỗi ##### bởi vì độ chênh lệch này âm. Tuy nhiên, cũng có rất nhiều cách để hiển thị công dụng âm trong Excel.

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

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


Nhấp OK để lưu giữ thiết lập new, quý giá âm sẽ được hiển thị nhỏng sau:


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

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


Crúc ý. Hãy hãy nhớ là phương pháp trước tiên đã tạo ra kết quả âm là quý giá số âm, công dụng hàm TEXT luôn luôn là chuỗi văn bản và chẳng thể sử dụng vào phnghiền toán thù hoặc phương pháp không giống.

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

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

Sử dụng hàm TIMESử dụng phnghiền tân oán dựa vào khoảng 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) góp việc tính tân oán dễ dàng rộng, tuy nhiên nó ko cho phép vấn đề cùng trừ rộng 23h, 59 phút ít hoặc 59 giây. Nếu bạn có nhu cầu tính toán thù khoảng chừng thời hạn to hơn, hãy sử dụng những phép toán sau đây.

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

Cộng/trừ tiếng vào thời hạn vắt thể

Hàm thời gian để thêm vào đó dưới 24 giờ

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

lấy một ví dụ, vào ô A2 là thời gian bước đầu, bạn có nhu cầu thêm 2 tiếng đồng hồ vào kia, công thức sẽ là:

=A2 + TIME(2, 0, 0)

 

Công thức nhằm cộng bất kỳ thời gian làm sao (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ờ đồng hồ, thực hiện bí quyết tựa như như gắng vệt + bằng dấu –

Ví dụ, 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ừ thời gian lớn hơn 23, áp dụng cách làm thứ nhất.

Cách cộng/trừ phút ít vào thời hạn thế 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ố sản phẩm 2:

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

Đây là 1 trong vài bí quyết thực tiễn để tính số phút trong Excel:

Công 20 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ừ trên 60 phút

Trong phnghiền tính của bạn, chia số phút mang đến 1440 – số phút trong một ngày với thêm thời gian ban đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút ít, vắt vệt + 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 hạn nắm thể

Để thêm vào đó 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)

Để cộng thêm số giây lớn hơn 59, sử dụng công thức:

=Start time + (N seconds / 86400)

Để trừ số giây, tận dụng bí quyết tương tự như mà lại cố kỉnh lốt + thành vệt -.

Trên trang tính Excel, công thức 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 cho 40 giây: =A2 – TIME(0,0,40)Trừ ô A2 mang đến 900 giây: =A2 – (900/86400)Cách cùng thời hạn vào Excel

Phnghiền cùng Excel hay áp dụng hàm SUM, cùng áp dụng format tương xứng đến hiệu quả.

Giả sử bạn tất cả một vài thời hạn trong cột B với bạn có nhu cầu cộng bọn chúng cùng nhau. Bạn viết bí quyết SUM nlỗi sau =SUM(B2:B4), tác dụng hiển thị theo định hình mang định như hh:mm:ss

 

Trong một vài ba ngôi trường hòa hợp, định hình thời gian mang định hoàn toàn có thể ổn, mà lại thỉnh thoảng 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 ko cần phxay tính nào cả, chúng ta chỉ việc định hình lại thời gian đến ô tính đựng phương pháp SUM.

Nhấp nên vào ô tính cùng chọn Format Cells từ bỏ danh sách tùy chọn, dấn Ctrl + 1 để mlàm việc vỏ hộp thoại Format Cells. Chọn Custom từ bỏ Category danh mục cùng gõ một trong các định dạng sau vào 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 gian bên dưới dạng giây:

Kết đúng thật sau:


Cách cùng qua 24 giờ trong Excel

Để tính tân oán kết quả hơn 24 giờ, bạn cũng có thể sử dụng cách làm SUM, với lựa chọn 1 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 và giây
“hours”, mm “minutes”, ss “seconds”30 hours, 10 minutes, trăng tròn seconds
d h:mm:ss1 06:10:20Ngày, tiếng, 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à đôi mươi seconds

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

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

Chụ ý. Định dạng thời hạn chỉ áp dụng mang lại số dương. Nếu tác dụng tính toán là số âm, tức thị các bạn đem giá trị nhỏ hơn trừ mang lại quý hiếm to hơn, công dụng sẽ hiển thị #####.


Bài viết coi nhiều