Cách tính thời gian trong Excel – chênh lệch thời gian, cộng/trừ thời gian

Trong bài viết này, Học Excel Online sẽ hướng dẫn những công thức hữu ích để cộng/trừ thời gian, chênh lệch thời gian, đếm giờ; cách cộng trừ thời gian trong excel.

Cách tính chênh lệch thời gian trong Excel

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

Như bạn đã biết, thời hạn trong Excel thường được định dạng số thập phân để trông giống thời hạn. Vì chúng là số, nên bạn hoàn toàn có thể cộng trừ thời hạn như các giá trị số khác .
Công thức đơn thuầ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 tài liệu, chênh lệch thời hạn thực tiễn hoàn toàn có thể có nhiều dạng khác nhau, ví dụ :
Công thức
Giải thích
=A2-B2
Tính toán chênh lệch thời gian giữa hai giá trị trong ô 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 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 giá trị trong ô A2 và B2, không tính chênh lệch ngày tháng nếu ô tính chứa cả giá trị ngày tháng và thời gian

Trong mạng lưới hệ thống Excel, thời hạn biểu lộ ở phần thập phân, bạn sẽ có tác dụng như sau :
1 - cách tính thời gian trong Excel
Phần thập phân trong cột D đúng nhưng không có ý nghĩa. Để giúp chúng phân phối thông tin có ích hơn, bạn hoàn toàn có thể vận dụng định dạng thời hạn với một trong các mã sau đây :
Mã thời gian
Giải thích

h
Số giờ trôi qua, ví dụ 4

h:mm
Số giờ và số phút trôi qua, ví dụ 4:10

h:mm:ss
Số giờ, số phút và số giây trôi qua, ví dụ 4:10:20.

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

Bây giờ, để xem công thức tính chênh lệch thời hạn và mã thời hạn hoạt động giải trí như thế nào trong bảng tính thực sự. Với Start time trong cột A và End time trong cột B, bạn hoàn toàn có thể sao chép công thức vào cột C và E :

=$B2-$A2

Thời gian chênh lệch được hiển thị khác nhau tùy vào định dạng thời hạn trong mỗi cột :
2 - cách tính thời gian trong Excel

Chú ý. Nếu thời gian chênh lệch được hiển thị thành #####, nghĩa là ô chứa công thức không đủ rộng để hiển thị hết thời gian hoặc kết quả phép tính là một giá trị âm.

Xem thêm : tài liệu học Excel cơ bản 2020

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

Một kỹ thuật khác để tính chênh lệch thời hạn trong Excel là sử dụng 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 giữa hai thời gian: =TEXT(B2-A2, “h:mm”)
  • Tính toán chênh lệch giờ, phút và giây giữa 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 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 tình huống nhất định, điều này có thể là một hạn chế vì bạn sẽ không thể sử dụng kết quả này để tiếp tục tính toán.
  • Nếu kết quả là một số âm, công thức TEXT sẽ hiển thị lỗi #VALUE!.

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 giữa hai thời hạn bằng một đơn vị chức năng ( giờ, phút, giây ), bạn cần triển khai các phép tính sau .

Phép tính giờ giữa hai thời gian

=(End time – Start time) * 24

Giả sử thời hạn mở màn trong ô A2 và thời hạn kết thúc trong ô B2, bạn hãy sử dụng phép tính đơn thuần là B2-A2 để đo lường và thống kê chênh lệch thời hạn, sau đó nhân với 24 .

=(B2-A2) * 24

Để 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)

4 - cách tính thời gian trong Excel

 

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

Để tính số phút giữa hai 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 hoàn toàn có thể cho hiệu quả âm hoặc dương, hiệu quả âm xảy ra khi thời hạn kết thúc lớn hơn thời hạn khởi đầu như hàng 5 :

=(B2-A2)*1440

5 - cách tính thời gian trong Excel

 

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

Để tính số giây giữa 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ẽ như sau :

=(B2-A2)* 86400

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

Chú ý. Định dạng General nên được thiết lập cho ô tính chứa công thức tính chênh lệch thời gian để kết quả hiển thị đúng.

Công thức 4. Tính chênh lệch thời gian trong cùng một đơn vị thời gian và bỏ qua các đơn vị khác  

Để tính chênh lệch giữa hai thời hạn trong cùng một đơn vị chức năng, bỏ lỡ các đơn vị chức năng thời hạn khác, hãy sử dụng một trong các hàm sau :
Chênh lệch giờ, bỏ lỡ phút và giây :

=HOUR(B2-A2)

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

=MINUTE(B2-A2)

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

=SECOND(B2-A2)

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

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

Chú ý. Nếu thời gian kết thúc lớn hơn thời gian bắt đầu, cụ thể nếu kết quả là số âm, công thức sẽ 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ắt đầu đến bây giờ

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

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

8 - cách tính thời gian trong Excel

 

Trong trường hợp khoảng chừng thời hạn trôi qua vượt quá 24 giờ, sử dụng một trong các định dạng thời hạn sau, ví dụ d ‘ days ’ h : mm : ss như trong hình sau :
9 - cách tính thời gian trong Excel

Nếu thời gian bắt đầu chỉ có giờ phút mà không có ngày, bạn cần sử dụng hàm Time để 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 gian từ ô A2 đến giờ.

= TIME ( HOUR ( NOW ( ) ), MINUTE ( NOW ( ) ), SECOND ( NOW ( ) ) ) – A2
10- cách tính thời gian trong Excel

Chú ý. Khoảng thời gian trôi qua sẽ không được cập nhật theo thời gian thực tế, nó chỉ cập nhật khi bảng tính được mở lại hoặc tính toán lại. Để cập nhật công thức, nhấn Shift + F9 để tính lại trang tính hoặc nhấn F9 để 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 gian “XX days, XX hours, XX minutes and XX seconds”

Bạn sử dụng các hàm HOUR, MINUTE SECOND để lấy kết quả thời gian tương tứng và hàm INT để tính chênh lệch ngày.Sau đó, bạn lồng ghép các hàm vào cùng một công thức cùng các chuỗi text như sau :

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

 A user-friendly Excel time difference formula

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

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

 Excel time difference formula that ignores zero values

Ngoài ra, bạn hoàn toàn có thể giám sát chênh lệch thời hạn bằng cách lấy thời hạn mở màn trừ thời hạn kết thúc ( ví dụ = B2-A2 ) rồi thiết lập định dạng sau cho ô tính :

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

11 - cách tính thời gian trong Excel

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

Khi tính toán 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ó nhiều cách để hiển thị kết quả âm trong Excel.

Phương pháp 1. 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 cuốn xuống When calculating this workbook và đánh dấu chọn Use 1904 date system.

12 - cách tính thời gian trong Excel
Nhấp OK để lưu setup mới, giá trị âm sẽ được hiển thị như sau :

In the 1904 date system, negative times are displayed like negative numbers

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

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

A formula to calculate negative times in Excel

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

Cộng/trừ thời gian trong Excel

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

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

Hàm TIME(hour, minute, second) giúp việc tính toán dễ dàng hơn, tuy nhiên 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 muốn tính toán khoảng thời gian lớn hơn, hãy sử dụng các phép toán dưới đây.

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

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

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

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

Ví dụ, trong ô A2 là thời hạn mở màn, bạn muốn thêm 2 giờ vào đó, công thức sẽ là :

=A2 + TIME(2, 0, 0)

 TIME function to add under 24 hours

Chú ý. Nếu bạn thử cộng hơn 23 giờ bằng hàm TIME, số giờ được chỉ định sẽ chia cho 24 và phần nguyên sẽ thêm vào thời gian bắt đầu. Ví dụ, nếu bạn cộng thêm 25 giờ vào 6/2/20155 10:00 AM (ô A4) bằng công thức =A4 + TIME(25, 0, 0), kết quả sẽ là: 06/02/2015 11:00, cụ thể là A4 + 1 giờ

Công thức để cộng bất kỳ số giờ nào (dưới/trên 24 giờ)

Công thức sau không có số lượng giới hạn về số giờ bạn muốn cộng thêm :

=Start time + (N hours/24)

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

=A2 + (28/24)

Universal formula to add hours to a given time in Excel

Để trừ đi một số giờ, sử dụng công thức tương tự như thay dấu + bằng dấu –

Ví dụ, trừ ô A2 cho 3 giờ, nhập một trong các công thức sau :

=A2-(3/24)

=A2-TIME(3,0,0)

Nếu bạn trừ số giờ lớn hơn 23, sử dụng công thức tiên phong .

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

Cộng/trừ 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ố thứ 2:

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

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

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

Trừ A2 cho 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, chia số phút cho 1440 – số phút trong một ngày và thêm thời hạn mở màn vào :

=Start time + (N minutes / 1440)

Để trừ số phút, thay 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 tính thời gian trong Excel – chênh lệch thời gian, cộng/trừ thời gian 5

Cách cộng/trừ giây vào thời gian cụ thể

Để cộng thêm 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)

Để 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 công thức tương tự như nhưng thay dấu + thành dấu – .
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 cho 900 giây: =A2 – (900/86400)

Cách cộng thời gian trong Excel

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

Giả sử bạn có một vài thời gian trong cột B và bạn muốn cộng 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 định dạng mặc định như hh:mm:ss

 A SUM formula to add up times in Excel

Trong một vài trường hợp, định dạng thời gian mặc định có thể ổn, nhưng đô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. Khi đó, bạn không cần phép tính nào cả, bạn chỉ cần định dạng lại thời gian cho ô tính chứa công thức SUM.

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

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

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

Kết quả như sau :

Apply the custom time format to display total time as minutes and seconds, or seconds only.

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

Để tính toán kết quả hơn 24 giờ, bạn có thể sử dụng công thức SUM, và chọn một trong các định dạng sau:

Định dạng
Hiển thị
Giải thích

[h]:mm
30:10
Giờ và phút

[h]:mm:ss
30:10:20
Giờ, phút và giây

[h] “hours”, mm “minutes”, ss “seconds”
30 hours, 10 minutes, 20 seconds

d h:mm:ss
1 06:10:20
Ngày, giờ, phút và giây

d “day” h:mm:ss
1 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 trong trang tính thực, xem hình dưới đây (công thức SUM được nhập vào ô A9 tới ô A13:

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

Chú ý. Đị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, nghĩa là bạn lấy giá trị nhỏ hơn trừ cho giá trị lớn hơn, kết quả sẽ hiển thị #####.

Adding up more than 24 hours in Excel

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

Để hiện thị một khoảng 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 mà mã đơn vị thời gian tương ứng nằm trong dấu ngoặc vuông, chẳng hạn như [h], [m], hay [s]. Dưới đây là các bước hướng dẫn chi tiết:

  1. Chọn một hay  nhiều ô mà bạn muốn định dạng.
  2. Nhấp chuột phải vào các ô được chọn rồi lại nhấp chuột phải vào Format Cells, hay bạn có thể nhấn Ctrl + 1, thao tác này sẽ giúp mở hộp thoại Format Cells.
  3. Trong Number tab, bên dưới Category, hãy chọn Custom rồi gõ một trong những định dạng thời gian dưới đây trong hộp Type:

Lớn hơn 24 giờ : [ h ] : mm : ss hay [ h ] : mm
Lớn hơn 60 phút : [ m ] : ss
Lớn hơn 60 giây : [ s ] Ảnh chụp màn hình hiển thị dưới đây cho thấy định dạng thời hạn “ lớn hơn 24 giờ ” trên thực tiễn :

Cách tính thời gian trong Excel – chênh lệch thời gian, cộng/trừ thời gian 6

Dưới đây là một vài định dạng khác. Chúng ta hoàn toàn có thể sử dụng nó để hiển thị những khoảng chừng thời hạn lớn hơn chiều dài đơn vị chức năng thời hạn chuẩn .

Miêu tả
Mã định dạng

Tổng số giờ
[h]

Số giờ và phút
[h]:mm

Số giờ, phút, giây
[h]:mm:ss

Tổng số phút
[m]

Số phút và giây
[m]:ss

Tổng số giây
[s]

Khi được sử dụng cho tài liệu mẫu của tất cả chúng ta ( Thời gian tổng số 50 : 40 trong ảnh chụp màn hình hiển thị trên ), những định dạng thời hạn này sẽ cho hiệu quả như sau :

A
B
C

1
Miêu tả
Thời gian hiển thị
Định dạng

2
Số giờ
50
[h]

3
Số giờ và phút
50:40
[h]:mm

4
Số giờ, phút, giây
50:40:30
[h]:mm:ss

5
Số phút
3040
[m]

6
Sốphút và giây
3040:30
[m]:ss

7
Số giây
182430
[s]

Để những khoảng chừng thời hạn được hiển thị ở trên dễ hiểu hơn so với người dùng, bạn hoàn toàn có thể bổ trợ đơn vị chức năng thời hạn bằng các từ viết tắt tương ứng, ví dụ :

A
B
C

1
Miêu tả
Thời gian hiển thị
Định dạng

2
Số giờ và phút
50 giờ và 40 phút
[h] “số giờ và” mm “số phút”

3
Số giờ, phút, giây
50h.40m.30s
[h] “h.” mm “m.” ss “s.”

4
Số phút
3040 phút
[m] “số phút”

5
Số phút và giây
3040 phút và 30 giây
[m] “số phút và” ss “số giây”

6
Số giây
182430 giây
[s] “số giây”

Lưu ý. Mặc dù những khoảng thời gian trên trông giống như chuỗi ký tự, nhưng chúng vẫn là giá trị số, bởi vì định dạng chữ số trong Excel chỉ thay đổi về hình thức chứ bản chất không thay đổi. Vì thế, bạn có thể tự do cộng hay trừ những khoảng thời gian đã được định dạng một cách bình thường, hãy tham chiếu chúng trong công thức và sử dụng trong các 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 chừng thời hạn mong ước cho một khoảng chừng thời hạn cho sẵn, hãy lấy số giờ, phút, hay giây mà bạn muốn cộng cho đơn vị chức năng thời hạn tương ứng trong một ngày ( 24 giờ, 1440 phút, 86400 giây ), rồi cộng số thương cho thời hạn khởi đầu .

Cộng hơn 24 giờ.

Thời gian mở màn + ( N / 24 )

Cộng hơn 60 phút.

Thời gian mở màn + ( N / 1440 )

Cộng hơn 60 giây.

Thời gian mở màn + ( N / 86400 )
Trong đó, N là số giờ, phút, hay giây mà bạn muốn cộng .
Đây là một vài ví dụ công thức trên thực tiễn :
Để cộng 45 giờ cho thời hạn mở màn trong ô A2 :
= A2 + ( 45/24 )
Để cộng 100 phút cho thời hạn khởi đầu trong ô A2 :
= A2 + ( 100 / 1440 )
Để cộng 200 giây cho thời hạn mở màn trong ô A2 :
= A2 + ( 200 / 86400 )
Hay là, bạn hoàn toàn có thể nhập khoảng chừng thời hạn cần cộng vào một ô riêng không liên quan gì đến nhau rồi tham chiếu ô đó trong công thức có dạng như trong ảnh chụp màn hình hiển thị dưới đây :

Cách tính thời gian trong Excel – chênh lệch thời gian, cộng/trừ thời gian 7

Để trừ khoảng chừng thời hạn trong Excel, hãy sử dụng công thức tương tự như nhưng hãy thay dấu cộng bằng dấu trừ nhé !

Trừ hơn 24 giờ

Thời gian mở màn – ( N / 24 )

Trừ hơn 60 phút.

Thời gian mở màn – ( N / 1440 )

Trừ hơn 60 giây

Thời gian mở màn – ( N / 86400 )
Ảnh chụp màn hình hiển thị dưới đây hiển thị các tác dụng :

Cách tính thời gian trong Excel – chênh lệch thời gian, cộng/trừ thời gian 8

Lưu ý:

  1. 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 ô.
  2. Nếu sau khi sử dụng định dạng và ô hiển thị #####, thì thông thường ô không đủ rộng để hiển thị giá trị thời gian trong ngày. Để xử lý điều này, hãy mở rộng chiều dài ô bằng cách nhấp đúp chuột hay kéo khung bên phải của cột.

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

Những kỹ năng và kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của Học Excel Online. Khóa học này phân phối cho bạn kiến thức và kỹ năng một cách khá đầy đủ và có mạng lưới hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong việc làm … Hiện nay mạng lưới hệ thống đang có nhiều khuyễn mãi thêm khi bạn ĐK tham gia khóa học này. Hãy tham gia ngay tại địa chỉ : Học Excel Online