Trong bài viết này, Học Excel Online sẽ giải đáp những bí quyết hữu ích để cộng/trừ thời gian, chênh lệch thời gian, đếm giờ; bí quyết cộng trừ thời gian trong 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 gian trong ExcelCách tính với hiển thị thời hạn âm trong ExcelCộng/trừ thời gian trong ExcelCách cộng thời gian trong ExcelCách hiển thị khoảng tầm thời gian lớn hơn 24 giờ, 60 phút, 60 giây?Cách cộng trừ thời gian to hơn 24 giờ, 60 phút, 60 giây

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

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

Như chúng ta đã biết, thời hạn trong Excel thường xuyên được format số thập phân nhằm trông tương tự thời gian. Vày chúng là số, nên bạn có thể cộng trừ thời gian như các giá trị số khác.

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

Công thứcGiải thích
=A2-B2Tính toán chênh lệch thời hạn giữa hai giá trị trong ô A2 với 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 chênh lệch thời gian giữa hai cực hiếm trong ô A2 cùng B2, ko kể chênh lệch ngày tháng ví như ô tính cất cả cực hiếm ngày tháng cùng thời gian

Trong hệ thống Excel, thời gian bộc lộ ở phần thập phân, các bạn sẽ có tác dụng như sau:

*
1 – bí quyết tính thời gian trong Excel

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

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

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

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


*

*

=$B2-$A2

Thời gian chênh lệch được hiển thị khác biệt tùy vào định dạng thời gian trong từng cột:

*
2 – bí quyết tính thời hạn trong Excel

Chú ý. Nếu thời hạn chênh lệch được hiển đô thị #####, tức là ô chứa bí quyết không đủ rộng nhằm hiển thị hết thời hạn hoặc kết quả phép tính là 1 trong những giá trị â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ác để tính chênh lệch thời hạn trong Excel là thực hiện hàm TEXT .

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

Chú ý.

Kết quả của hàm TEXT luôn luôn là văn bản. Các giá trị text được căn lề trài trong các cột C, D, E trong hình trên. Trong vài trường hợp nhất định, điều này hoàn toàn có thể là một hạn chế vì các bạn sẽ không thể sử dụng hiệu quả này để liên tục tính toán.

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

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

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

=(End time – Start time) * 24

Giả sử thời gian ban đầu trong ô A2 với thời gian chấm dứt trong ô B2, bạn hãy sử dụng phép tính đơn giản là B2-A2 để đo lường và tính toán chênh lệch thời gian, kế tiếp 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 lại số nguyên hần nhất:

=INT((B2-A2) * 24)

*
4 – bí quyết tính thời hạn trong Excel

 

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

Để tính số phút thân hai 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, hiệu quả âm xảy ra khi thời gian chấm dứt lớn rộng thời gian bước đầu như hàng 5:

=(B2-A2)*1440

*
5 – giải pháp tính thời gian trong Excel

 

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

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

=(End time – Start time) * 86400

Trong ví dụ, phương pháp sẽ như sau:

=(B2-A2)* 86400

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

Chú ý. Định dạng General nên được tùy chỉnh cho ô tính chứa 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 thuộc một solo vị thời gian và vứt qua các đơn vị không giống

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

Chênh lệch giờ, bỏ qua mất phút và giây:

=HOUR(B2-A2)

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

=MINUTE(B2-A2)

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

=SECOND(B2-A2)

Khi sử dụng hàm HOUR, MINUTE, SECOND, hãy lưu giữ rằng công dụng không được vượt vượt 24 giờ, 60 phút hoặc 60 giây.

*
7 – giải pháp tính thời gian trong Excel

Chú ý. Nếu thời gian dứt lớn hơn thời gian bắt đầu, cụ thể nếu công dụng là số âm, cách làm sẽ hiển thị lỗi #NUM!

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

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

Giả sử ô A2 chứa thời khắc bắt đầu, công thức =NOW()-A2 cho hiệu quả sau với cột B được định dạng thời gian phù hợp, trong lấy ví dụ như này nó có dạng h:mm

*
8 – bí quyết tính thời gian trong Excel

 

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

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

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

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

*
10- biện pháp tính thời hạn trong Excel

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 mở lại hoặc đo lường và tính toán lại. Để update công thức, thừa nhận Shift + F9 để tính lại trang tính hoặc dấn F9 để 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 và XX seconds”

Bạn sử dụng các hàm HOUR, MINUTE SECOND để lấy hiệu quả thời gian tương tứng cùng 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 thuộc một bí quyết cùng những chuỗi text như sau :

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

 

*

Ngoài ra, bạn cũng có thể tính toán chênh lệch thời gian bằng cách lấy thời gian bắt đầu trừ thời gian dứt (ví dụ = B2-A2) rồi tùy chỉnh định dạng sau đến ô tính:

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

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

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

Khi đo lường chênhh lệch thờ gian, bạn cũng có thể bắt chạm chán lỗi ##### vì độ chênh lệch này âm. Mặc dù nhiên, cũng có thể có nhiều cách để hiển thị tác dụng âm trong Excel.

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

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

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

Nhấp OK để lưu thiết lập mới, quý hiếm âm sẽ được hiển thị như sau:

*

Phương pháp 2. Giám sát và đo lường thời gian âm bởi công thức

Nếu không muốn sử dụng phương thức 1, bạn cũng 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 sẽ đánh giá 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 công dụng hay không. Nếu chênh lệch nhỏ nhiều hơn 0, công thức đầu tiên sẽ tính trị tuyệt vời và hoàn hảo nhất rồi thêm lốt trừ. Phương pháp thứ hai cho công dụng tương tự bằng định dạng thời gian âm “-h:: mm”.

*

Chú ý. Hãy lưu giữ rằng phương thức đầu tiên mang đến ra kết quả âm là quý giá số âm, tác dụng hàm TEXT luôn luôn là chuỗi văn phiên bản và ko thể sử dụng trong phép toán hoặc công thức khác.

Xem thêm: 0 ( Số 0 Có Phải Là Số Nguyên Không ? Câu Hỏi Dễ Hack Não 1K Người

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

Về cơ bản, có 2 phương pháp cộng/trừ thời hạn trong Excel:

Sử dụng hàm TIMESử dụng phép toán dựa trên số giờ đồng hồ (24), số phút (1440), số giây (86400) trong một ngày.

Hàm TIME(hour, minute, second) giúp bài toán tính toán thuận lợi hơn, tuy vậy nó không cho phép việc 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 toán khoảng thời hạn lớn hơn, hãy sử dụng những phép toán bên dưới đây.

Tham khảo ngay: Giáo trình Excel 2020

Cộng/trừ tiếng vào thời gian cụ thể

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

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

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

=A2 + TIME(2, 0, 0)

 

*

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

=Start time + (N hours/24)

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

=A2 + (28/24)

*

Để trừ đi một số trong những giờ, áp dụng công thức tựa như như nắm dấu + bằng dấu –

Ví dụ, trừ ô A2 mang lại 3 giờ, nhập một trong những công thức sau:

=A2-(3/24)

=A2-TIME(3,0,0)

Nếu chúng ta trừ số giờ to hơn 23, sử dụng công thức đầu tiên.

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

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

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

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

Đây là một vài công thức thực tế để tính số phút vào Excel:

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

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

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

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

=Start time + (N minutes / 1440)

Để trừ số phút, vắt dấu + 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 cụ thể

Để thêm vào đó số giây ít hơn 60 vào thời gian cụ thể, sử dụng hàm TIME:

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

Để thêm vào đó 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 tối đa công thức tương tự như nhưng cầm cố dấu + thành vết -.

Trên trang tính Excel, bí quyết 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 mang đến 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 trong Excel

Phép cộng Excel thường áp dụng hàm SUM, và áp dụng định dạng phù hợp cho kết quả.

Giả sử chúng ta có một vài thời gian trong cột B và bạn có nhu cầu cộng bọn chúng với nhau. Bạn viết công thức SUM như sau =SUM(B2:B4), kết quả hiển thị theo format mặc định như hh:mm:ss

 

*

Trong một vài trường hợp, định dạng thời hạn mặc định hoàn toàn có thể ổn, tuy vậy đôi khi bạn muốn 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. Lúc đó, bạn không cần phép tính nào cả, bạn chỉ việc định dạng lại thời gian cho ô tính chứa cách làm SUM.

Nhấp cần vào ô tính và lựa chọn Format Cells từ danh sách tùy chọn, nhấn Ctrl + 1 để mở hộp thoại Format Cells. Lựa chọn Custom từ Category menu và gõ một trong các định dạng sau vào vỏ hộp Type:

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

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

Kết đúng thật sau:

*

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

Để tính toán công dụng hơn 24 giờ, chúng ta cũng có thể sử dụng bí quyết SUM, và 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”, mm “minutes”, ss “seconds”30 hours, 10 minutes, trăng tròn seconds
d h:mm:ss1 06:10:20Ngày, giờ, phút với 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 20 seconds

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

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

Chú ý. Định dạng thời hạn chỉ vận dụng cho số dương. Nếu kết quả tính toán là số âm, nghĩa là bạn lấy giá trị bé dại hơn trừ mang lại giá trị bự hơn, hiệu quả sẽ hiển thị #####.

*

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

Để hiện lên một khoảng tầm thời gian lớn hơn 24 giờ, 60 phút, 60 giây, hãy sử dụng custom time format nơi nhưng mã 1-1 vị thời hạn tương ứng phía trong dấu ngoặc vuông, chẳng hạn như , , tốt . Tiếp sau đây là các bước hướng dẫn đưa ra tiết:

Chọn một hay nhiều ô mà bạn muốn định dạng.Nhấp chuột cần vào các ô được lựa chọn rồi lại click chuột phải vào Format Cells, hay bạn có thể nhấn Ctrl + 1, thao tác này để giúp mở vỏ 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 dạng thời gian tiếp sau đây trong hộp Type:

Lớn hơn 24 giờ: :mm:ss giỏi :mm

Lớn hơn 60 phút: :ss

Lớn hơn 60 giây:

Ảnh chụp màn hình hiển thị dưới đây cho biết định dạng thời gian “lớn hơn 24 giờ” trên thực tế:

Dưới đây là một vài định dạng khác. Bạn có thể sử dụng nó nhằm hiển thị những khoảng tầm thời gian lớn hơn chiều dài đối kháng vị thời hạn chuẩn.

Miêu tảMã định dạng
Tổng số giờ
Số giờ với phút:mm
Số giờ, phút, giây:mm:ss
Tổng số phút
Số phút cùng giây:ss
Tổng số giây

Khi được áp dụng cho tài liệu mẫu của chúng ta (Thời gian tổng số 50:40 trong hình ảnh chụp screen trên), rất nhiều định dạng thời hạn này vẫn cho kết quả như sau:

ABC
1Miêu tảThời gian hiển thịĐịnh dạng
2Số giờ50
3Số giờ và phút50:40:mm
4Số giờ, phút, giây50:40:30:mm:ss
5Số phút3040
6Sốphút cùng giây3040:30:ss
7Số giây182430

Để gần như khoảng thời gian được hiển thị làm việc trên dễ hiểu hơn so với người dùng, bạn có thể bổ sung đơn vị thời hạn bằng các 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ờ cùng phút50 giờ và 40 phút “số giờ đồng hồ và” mm “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 và giây3040 phút cùng 30 giây “số phút và” ss “số giây”
6Số giây182430 giây “số giây”

Lưu ý. Mặc dù hồ hết khoảng thời hạn trên trông y hệt như chuỗi ký kết tự, cơ mà chúng vẫn chính là giá trị số, chính vì định dạng văn bản số vào Excel chỉ đổi khác về vẻ ngoài chứ bản chất không cố gắng đổi. Vị thế, bạn có thể tự bởi cộng xuất xắc trừ những khoảng thời hạn đã được định dạng một phương pháp bình thường, hãy tham chiếu chúng trong bí quyết và sử dụng trong những phép tính khác.

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

Để cộng khoảng thời hạn mong mong muốn cho một khoảng thời hạn cho sẵn, hãy mang số giờ, phút, hay giây mà bạn có nhu cầu cộng cho đối kháng vị thời gian tương ứng vào một ngày (24 giờ, 1440 phút, 86400 giây), rồi cộng số yêu thương cho thời gian bắt đầu.

Cộng rộng 24 giờ.

Thời gian ban đầu + (N/24)

Cộng rộng 60 phút.

Thời gian bước đầu + (N/1440)

Cộng hơn 60 giây.

Thời gian bước đầu + (N/86400)

Trong đó, N là số giờ, phút, hay giây mà bạn muốn cộng.

Đây là 1 trong vài ví dụ bí quyết trên thực tế:

Để cộng 45 giờ mang lại thời gian bước đầu trong ô A2:

=A2+(45/24)

Để cùng 100 phút mang đến thời gian bắt đầu trong ô A2:

=A2+(100/1440)

Để cùng 200 giây đến thời gian bắt đầu trong ô A2:

=A2+(200/86400)

Hay là, chúng ta cũng có thể nhập khoảng thời gian cần cộng vào trong 1 ô đơn lẻ rồi tham chiếu ô kia trong công thức bao gồm dạng như trong ảnh chụp màn hình hiển thị dưới đây:

Để trừ khoảng thời gian trong Excel, hãy thực hiện công thức tương tự nhưng hãy thế dấu cộng bởi dấu trừ nhé!

Trừ rộng 24 giờ

Thời gian ban đầu – (N/24)

Trừ hơn 60 phút.

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

Trừ hơn 60 giây

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

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

Lưu ý:

Nếu khoảng thời gian được tính toán có dạng số thập phân, hãy sử dụng định dạng thời gian/ngày để định dạng ô.Nếu sau khi sử dụng định dạng với ô hiển thị #####, thì thường thì ô không được rộng nhằm hiển thị giá bán trị thời gian trong ngày. Để xử lý điều này, hãy không ngừng mở rộng chiều lâu năm ô bằng phương pháp nhấp đúp con chuột hay kéo form bên phải của cột.

Đó là toàn bộ cách để các chúng ta có thể cách cộng trừ thời gian trong excel. Trường hợp còn bất kể thắc mắc gì thì đừng ngại ngần cmt xuống dưới và để được giải đáp nhanh nhất có thể nhé!