Công thức 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 hạn trong Excel

Trong bài viết này, Học Excel Online sẽ khuyên bảo đều cách làm có ích nhằm cộng/trừ thời gian, chênh lệch thời gian, đếm giờ; bí quyết cộng trừ thời gian vào excel.

Bạn đang xem: Công thức tính 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

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

Công thức dễ dàng và đơn giản độc 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 gian thực tiễn có thể có 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 nhì cực hiếm 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 gian giữa nhị thời gian núm thể
=TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) – TIME(HOUR(B2), MINUTE(B2), SECOND(B2))Tính tân oán chênh lệch thời gian thân hai quý giá vào ô A2 và B2, quanh đó chênh lệch ngày tháng giả dụ ô tính chứa cả cực hiếm ngày tháng và thời gian

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


*

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


Phần thập phân trong cột D đúng cơ mà không có ý nghĩa. Để góp bọn chúng cung cấp thông tin có lợi rộng, chúng ta có thể áp dụng định hình thời gian với 1 trong các mã sau đây:

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

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

Bây giờ đồng hồ, để xem phương pháp tính chênh lệch thời gian với mã thời gian hoạt động thế nào trong bảng tính đích thực. Với Start time trong cột A và End time trong cột B, chúng ta cũng có thể xào luộc 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 biệt tùy vào định hình thời hạn trong mỗi cột:


*

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


Chú ý. Nếu thời hạn chênh lệch được hiển tỉnh thành #####, nghĩa là ô cất công thức cảm thấy không được rộng lớn nhằm hiển thị không còn thời hạn hoặc tác dụng phnghiền tính là một trong những cực hiếm âm.

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

Một nghệ thuật khác để tính chênh lệch thời hạn trong Excel là áp dụng hàm TEXT .

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

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


Crúc ý.

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

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

Để hiển thị chênh lệch thân hai thời gian bởi một đơn vị (giờ đồng hồ, phút ít, giây), bạn phải thực hiện các phép tính sau.

Phép tính giờ đồng hồ giữa hai thời gian

=(End time – Start time) * 24

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

=(B2-A2) * 24

Để có số Complete hours, thực hiện hàm INT để làm tròn đến số ngulặng hần nhất:

=INT((B2-A2) * 24)


*

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


 

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

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

=(End time – Start time) * 1440

Công thức rất có thể đến hiệu quả âm hoặc dương, tác dụng âm xẩy ra lúc thời gian xong xuôi lớn hơn thời gian ban đầu như hàng 5:

=(B2-A2)*1440


*

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


 

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

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

=(End time – Start time) * 86400

Trong ví dụ, phương pháp đã như sau:

=(B2-A2)* 86400


*

6 – cách tính thời gian trong Excel


Chú ý. Định dạng General phải được thiết lập cho ô tính chứa cách làm tính chênh lệch thời hạn 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 và một đơn vị chức năng thời gian với bỏ qua các đơn vị chức năng khác

Để tính chênh lệch giữa hai thời gian trong cùng một đơn vị chức năng, bỏ lỡ những đơ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ờ, làm lơ phút cùng giây:

=HOUR(B2-A2)

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

=MINUTE(B2-A2)

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

=SECOND(B2-A2)

Khi áp dụng hàm HOUR, MINUTE, SECOND, hãy hãy nhớ là tác dụng ko được quá thừa 24 giờ, 60 phút ít hoặc 60 giây.


*

7 – cách tính thời gian trong Excel


Chú ý. Nếu thời gian xong xuôi lớn hơn thời hạn ban đầu, cụ thể nếu như hiệu quả là số âm, công thức đã 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 mang đến bây giờ

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

Giả sử ô A2 chứa thời điểm bước đầu, công thức =NOW()-A2 cho công dụng sau với cột B được định hình thời gian tương xứng, trong ví dụ này nó bao gồm dạng h:mm


*

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


 

Trong trường hòa hợp khoảng chừng thời hạn trôi qua vượt vượt 24 tiếng, áp dụng một trong những định hình vài ngày sau, ví dụ d ‘days’ h:mm:ss như vào hình sau:


*

9 – cách tính thời hạn vào Excel


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

Xem thêm: Hướng Dẫn Chuyển Định Dạng Số Trong Excel (Number To Text), 4 Cách Chuyển Text To Number Trong Excel 5/2021

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


*

10- cách tính thời hạn vào Excel


Crúc ý. Khoảng thời hạn trôi qua sẽ không được update theo thời gian thực tiễn, nó chỉ cập nhật lúc bảng tính được mnghỉ ngơi lại hoặc tính tân oán lại. Để cập nhật bí quyết, thừa nhận Shift + F9 để tính lại trang tính hoặc thừa nhận F9 để 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 gian “XX days, XX hours, XX minutes & XX seconds”

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

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

 

*

Trong khi, bạn có thể tính toán thù chênh lệch thời gian bằng phương pháp mang thời hạn bắt đầu trừ thời hạn xong xuôi (ví dụ = B2-A2) rồi thiết lập format sau mang đến ô tính:

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


*

11 – cách tính thời hạn vào Excel


Cách tính cùng hiển thị thời gian âm trong Excel

khi tính toán 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ông ít cách để hiển thị kết quả âm vào Excel.

Phương thơm pháp 1. Thay 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ô hanh cuốn nắn xuống When calculating this workbook cùng ghi lại chọn Use 1904 date system.


*

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


Nhấp OK nhằm lưu lại setup mới, quý giá âm sẽ được hiển thị nlỗi sau:

*

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

Nếu không muốn thực hiện cách thức 1, bạn có thể sử dụng công thức sau:

=IF(A2-B2>0, A2-B2, “-” và TEXT(ABS(A2-B2),”h:mm”))

=IF(A2-B2>0, A2-B2, TEXT(ABS(A2-B2),”-h:mm”))

Cả hai cách làm đang chất vấn chênh lệch thời hạn (A2-B2) có lớn hơn 0 hay là không và nó bao gồm hiện nay tác dụng hay là không. Nếu chênh lệch nhỏ nhiều hơn 0, bí quyết đầu tiên công thêm trị hoàn hảo nhất rồi thêm lốt trừ. Công thức máy nhì mang đến hiệu quả tương tự như bằng format thời hạn âm “-h:: mm”.

*

Chú ý. Hãy nhớ rằng cách thức đầu tiên đã tạo ra kết quả âm là cực hiếm số âm, kết quả hàm TEXT luôn là chuỗi văn phiên bản và chẳng thể thực hiện vào phnghiền 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 dạng, tất cả 2 bí quyết cộng/trừ thời gian vào Excel:

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

Hàm TIME(hour, minute, second) góp vấn đề tính toán thù dễ dàng hơn, tuy vậy nó ko có thể chấp nhận được câu hỏi 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 thù khoảng thời hạn to hơn, hãy sử dụng những phxay tân oán tiếp sau đây.

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

Cộng/trừ tiếng vào thời hạn cố kỉnh thể

Hàm thời hạn nhằm 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 hạn ban đầu, bạn muốn thêm 2 tiếng đồng hồ vào kia, cách làm sẽ là:

=A2 + TIME(2, 0, 0)

 

*

Công thức để cùng bất kỳ thời gian làm sao (dưới/trên 24 giờ)

=Start time + (N hours/24)

ví dụ như, để cộng thêm 28 tiếng vào ô A2, nhập cách làm sau:

=A2 + (28/24)

*

Để trừ đi một số trong những tiếng, sử dụng công thức tương tự nhỏng rứa vết + bởi dấu –

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

=A2-(3/24)

=A2-TIME(3,0,0)

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

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

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

Sử dụng hàm TIME nhằm cộng trừ số phút ít bạn muốn vào tham mê số đồ vật 2:

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

Đây là 1 vài ba phương pháp thực tiễn để tính số phút ít vào Excel:

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

Trừ A2 đến 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 doanh nghiệp, phân chia số phút ít đến 1440 – số phút ít vào một ngày với thêm thời gian bắt đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút ít, vậy lố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 hạn rứa thể

Để cộng thêm số giây thấp hơn 60 vào thời gian rõ ràng, á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, thực hiện công thức:

=Start time + (N seconds / 86400)

Để trừ số giây, tận dụng công thức tương tự như nhưng nạm vết + thành lố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 đến 900 giây: =A2 – (900/86400)Cách cộng thời gian trong Excel

Phxay cộng Excel thường thực hiện hàm SUM, cùng vận dụng định dạng phù hợp cho tác dụng.

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

 

*

Trong một vài ngôi trường phù hợp, định dạng thời hạn khoác định rất có thể ổn, cơ mà thỉnh thoảng bạn có nhu cầu hiển thị tổng thời gian bên dưới dạng phút ít với giây, hoặc chỉ hiển thị giây. Lúc đó, chúng ta ko nên phnghiền tính nào cả, bạn chỉ cần format lại thời hạn mang lại ô tính đựng bí quyết SUM.

Nhấp cần vào ô tính với chọn Format Cells từ bỏ list tùy lựa chọn, nhấn Ctrl + 1 nhằm mnghỉ ngơi vỏ hộp thoại Format Cells. Chọn Custom tự Category menu cùng gõ một trong các format sau vào vỏ hộp Type:

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

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

Kết quả thật sau:

*

Cách cùng qua 24 giờ vào Excel

Để tính toán công dụng hơn 24 giờ đồng hồ, bạn có thể thực hiện phương pháp SUM, và chọn 1 trong số định hình sau:

Định dạngHiển thịGiải thích
:mm30:10Giờ và phút
:mm:ss30:10:20Giờ, phút ít cùng giây
“hours”, milimet “minutes”, ss “seconds”30 hours, 10 minutes, 20 seconds
d h:mm:ss1 06:10:20Ngày, giờ đồng hồ, 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 và đôi mươi seconds

Để coi hiệu quả định dạng trong trang tính thực, xem hình tiếp sau đây (cách làm SUM được nhtràn vào ô A9 tới ô A13:

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

Chú ý. Đị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 thù là số âm, nghĩa là bạn lấy quý giá nhỏ tuổi rộng trừ mang đến quý giá to hơn, kết quả vẫn hiển thị #####.

*

Đó là toàn cục cách để những chúng ta cũng có thể phương pháp cùng trừ thời gian vào excel. Nếu còn bất kể thắc mắc gì thì đừng ngại nsát cmt xuống dưới và để được lời giải nhanh khô tuyệt nhất nhé!