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

Trong nội dung bài viết này, Học Excel Online tiếp tục chỉ dẫn những công thức hữu ích nhằm cộng/trừ thời hạn, chênh nghiêng thời hạn, kiểm đếm giờ; cơ hội nằm trong trừ thời hạn nhập excel.

Cách tính chênh nghiêng thời hạn nhập Excel

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

Như chúng ta đang được biết, thời hạn nhập Excel thông thường được format số thập phân nhằm nhìn như thể thời hạn. Vì bọn chúng là số, nên chúng ta cũng có thể nằm trong trừ thời hạn giống như những độ quý hiếm số không giống.

Bạn đang xem: Cách tính thời gian trong Excel – chênh lệch thời gian, cộng/trừ thời gian

Công thức giản dị nhất nhằm tính chênh nghiêng thời hạn là:

=End time – Start time

Tùy nhập cấu hình tài liệu, chênh nghiêng thời hạn thực tiễn rất có thể có không ít dạng không giống nhau, ví dụ:

Trong khối hệ thống Excel, thời hạn biểu thị ở trong phần thập phân, các bạn sẽ sở hữu thành phẩm như sau:

Phần thập phân nhập cột D đích thị tuy nhiên không tồn tại chân thành và ý nghĩa. Để gom bọn chúng hỗ trợ vấn đề tiện ích rộng lớn, chúng ta cũng có thể vận dụng format thời hạn với cùng một trong những mã sau đây:

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

Bây giờ, nhằm coi công thức tính chênh nghiêng thời hạn và mã thời hạn sinh hoạt thế nào nhập bảng tính thực sự. Với Start time nhập cột A và End time nhập cột B, chúng ta cũng có thể sao chép công thức nhập cột C và E:

=$B2-$A2

Thời gian ngoan chênh nghiêng được hiển thị không giống nhau tùy nhập format thời hạn trong những cột:

Chú ý. Nếu thời hạn chênh nghiêng được hiển thị trở thành #####, tức thị dù chứa chấp công thức ko đầy đủ rộng lớn nhằm hiển thị không còn thời hạn hoặc thành phẩm quy tắc tính là 1 độ quý hiếm âm.

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

Công thức 2. Tính chênh nghiêng thời hạn vì như thế hàm TEXT

Một nghệ thuật không giống nhằm tính chênh nghiêng thời hạn nhập Excel là dùng hàm TEXT .

• Tính toán chênh nghiêng giờ thân thiết nhì thời gian: =TEXT(B2-A2, “h”)
• Tính toán chênh nghiêng giờ và phút thân thiết nhì thời gian: =TEXT(B2-A2, “h:mm”)
• Tính toán chênh nghiêng giờ, phút và giây thân thiết nhì thời gian: =TEXT(B2-A2, “h:mm:ss”)

Chú ý.

• Kết ngược của hàm TEXT luôn luôn là văn phiên bản. Các độ quý hiếm text được căn lề trài trong những cột C, D, E nhập hình bên trên. Trong vài ba trường hợp chắc chắn, điều này rất có thể là 1 giới hạn vì như thế các bạn sẽ ko thể dùng thành phẩm này nhằm nối tiếp đo lường và tính toán.
• Nếu thành phẩm là một vài âm, công thức TEXT tiếp tục hiển thị lỗi #VALUE!.

Công thức 3. Tính giờ, phút, giây thân thiết nhì thời gian

Để hiển thị chênh nghiêng thân thiết nhì thời hạn vì như thế một đơn vị chức năng (giờ, phút, giây), bạn phải tiến hành những quy tắc tính sau.

Phép tính giờ thân thiết nhì thời gian

=(End time – Start time) * 24

Giả sử thời hạn chính thức nhập dù A2 và thời hạn kết đốc nhập dù B2, các bạn hãy dùng quy tắc tính giản dị là B2-A2 nhằm đo lường và tính toán chênh nghiêng thời hạn, tiếp sau đó nhân với 24.

=(B2-A2) * 24

Để sở hữu số Complete hours, dùng hàm INT để thực hiện tròn trĩnh cho tới số vẹn toàn hần nhất:

=INT((B2-A2) * 24)

Tổng số phút thân thiết nhì thời gian:

Để tính số phút thân thiết nhì thời hạn, nhân chênh nghiêng thời hạn với 1440

=(End time – Start time) * 1440

Công thức rất có thể cho tới thành phẩm âm hoặc dương, thành phẩm âm xẩy ra Khi thời hạn kết đốc to hơn thời hạn chính thức như sản phẩm 5:

=(B2-A2)*1440

Tổng số giây thân thiết nhì thời gian:

Để tính số giây thân thiết nhì thời hạn, nhân chênh nghiêng thời hạn với 86400

=(End time – Start time) * 86400

Trong ví dụ, công thức tiếp tục như sau:

=(B2-A2)* 86400

Chú ý. Định dạng General nên được thiết lập cho tới dù tính chứa chấp công thức tính chênh nghiêng thời hạn nhằm thành phẩm hiển thị đích thị.

Công thức 4. Tính chênh nghiêng thời hạn nhập và một đơn vị chức năng thời hạn và bỏ lỡ những đơn vị chức năng khác  

Để tính chênh nghiêng thân thiết nhì thời hạn nhập và một đơn vị chức năng, bỏ lỡ những đơn vị chức năng thời hạn không giống, hãy dùng một trong những hàm sau:

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

=HOUR(B2-A2)

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

=MINUTE(B2-A2)

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

=SECOND(B2-A2)

Khi dùng hàm HOUR, MINUTE, SECOND, hãy ghi nhớ rằng thành phẩm ko được vượt lên trên vượt 24 giờ, 60 phút hoặc 60 giây.

Chú ý. Nếu thời hạn kết đốc to hơn thời hạn chính thức, rõ ràng nếu như thành phẩm là số âm, công thức tiếp tục hiển thị lỗi #NUM!

Công thức 5. Tính thời hạn trôi qua quýt Tính từ lúc thời hạn chính thức cho tới bây giờ

Để tính thời hạn đang được trôi qua quýt, chúng ta cũng có thể dùng hàm Now và Time. Trường hợp ý khoảng chừng thời hạn trôi qua quýt dưới 24 giờ, bạn chỉ cần dùng hàm NOW nhằm lấy thành phẩm tháng ngày và thời hạn thời điểm hiện tại, tiếp sau đó trừ cút ngày và thời hạn chính thức.

Giả sử dù A2 chứa chấp thời gian chính thức, công thức =NOW()-A2 cho thành phẩm sau với cột B được format thời hạn tương thích, nhập ví dụ này nó sở hữu dạng h:mm

Trong tình huống khoảng chừng thời hạn trôi qua quýt vượt lên trên vượt 24 giờ, dùng một trong những format thời hạn sau, ví dụ d ‘days’ h:mm:ss như nhập hình sau:

Nếu thời hạn chính thức chỉ mất giờ khắc nhưng mà không tồn tại ngày, bạn phải dùng hàm Time để đo lường và tính toán khoảng chừng thời hạn trôi cút một cơ hội đúng mực. Ví dụ, công thức sau trả về khoảng chừng thời hạn kể từ dù A2 cho tới giờ.

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

Chú ý. Khoảng thời hạn trôi qua quýt sẽ không còn được update theo đuổi thời hạn thực tiễn, nó chỉ update Khi bảng tính được mở ra hoặc đo lường và tính toán lại. Để update 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 cỗ bảng tính.

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

Bạn dùng những hàm HOUR, MINUTE và SECOND nhằm lấy thành phẩm thời hạn tương tứng và hàm INT nhằm tính chênh nghiêng ngày.Sau tê liệt, chúng ta đan xen những hàm nhập và một công thức với mọi chuỗi text như sau :

=INT(B2-A2) & ” days, ” & HOUR(B2-A2) & ” hours, ” & MINUTE(B2-A2) & ” minutes and ” & 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) & ” 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”,””)

Ngoài đi ra, chúng ta cũng có thể đo lường và tính toán chênh nghiêng thời hạn bằng phương pháp lấy thời hạn chính thức trừ thời hạn kết đốc (ví dụ = B2-A2) rồi thiết lập format sau cho tới dù tính:

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

Cách tính và hiển thị thời hạn âm nhập Excel

Khi đo lường và tính toán chênhh nghiêng thờ gian ngoan, chúng ta cũng có thể phát hiện lỗi ##### vì chừng chênh nghiêng này âm. Tuy nhiên, cũng đều có nhiều phương pháp để hiển thị thành phẩm âm nhập Excel.

Phương pháp 1. Thay thay đổi hệ thông tháng ngày của Excel trở 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 lựa chọn Use 1904 date system.

Nhấp OK nhằm lưu setup mới nhất, độ 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 vì như thế công thức

Nếu không thích dùng cách thức 1, chúng ta cũng có thể 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ả nhì công thức tiếp tục đánh giá chênh nghiêng thời hạn (A2-B2) sở hữu to hơn 0 hay là không và nó sở hữu hiện nay thành phẩm hay là không. Nếu chênh nghiêng bé nhiều hơn 0, công thức trước tiên tiếp tục tính trị vô cùng rồi tăng vết trừ. Công thức loại nhì cho tới thành phẩm tương tự động vì như thế format thời hạn âm “-h:: mm”.

Chú ý. Hãy ghi nhớ rằng cách thức trước tiên đã cho ra thành phẩm âm là độ quý hiếm số âm, thành phẩm hàm TEXT luôn luôn là chuỗi văn phiên bản và ko thể dùng nhập quy tắc toán hoặc công thức không giống.

Cộng/trừ thời hạn nhập Excel

Về cơ phiên bản, sở hữu 2 cơ hội cộng/trừ thời hạn nhập Excel:

• Sử dụng hàm TIME
• Sử dụng quy tắc toán dựa vào số giờ (24), số phút (1440), số giây (86400) nhập một ngày.

Hàm TIME(hour, minute, second) giúp việc đo lường và tính toán đơn giản và dễ dàng rộng lớn, tuy vậy nó ko được chấp nhận việc nằm trong trừ rộng lớn 23 giờ, 59 phút hoặc 59 giây. Nếu bạn thích đo lường và tính toán khoảng chừng thời hạn to hơn, hãy dùng những quy tắc toán sau đây.

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

Cộng/trừ giờ nhập thời hạn cụ thể

Hàm thời hạn nhằm thêm vào đó bên dưới 24 giờ

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

Ví dụ, nhập dù A2 là thời hạn chính thức, bạn thích tăng 2 tiếng đồng hồ nhập tê liệt, công thức tiếp tục là:

=A2 + TIME(2, 0, 0)

Chú ý. Nếu chúng ta test nằm trong rộng lớn 23 giờ vì như thế hàm TIME, số giờ được chỉ định và hướng dẫn tiếp tục phân tách cho tới 24 và phần vẹn toàn tiếp tục thêm nữa thời hạn chính thức. Ví dụ, nếu khách hàng thêm vào đó 25 giờ nhập 6/2/20155 10:00 AM (ô A4) vì như thế công thức =A4 + TIME(25, 0, 0), thành phẩm tiếp tục là: 06/02/2015 11:00, rõ ràng là A4 + 1 giờ

Xem thêm: Đoạn văn bảo vệ môi trường bằng tiếng Anh có bản dịch và từ vựng

Công thức nhằm nằm trong ngẫu nhiên số giờ nào là (dưới/trên 24 giờ)

Công thức sau không tồn tại số lượng giới hạn về số giờ bạn thích nằm trong thêm:

=Start time + (N hours/24)

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

=A2 + (28/24)

Để trừ cút một vài giờ, dùng công thức tương tự động như thay cho vết + vì như thế vết –

Ví dụ, trừ dù A2 cho tới 3h, 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, dùng công thức trước tiên.

Cách cộng/trừ phút nhập thời hạn cụ thể

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

Sử dụng hàm TIME để nằm trong trừ số phút bạn thích nhập thông số loại 2:

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

Đây là 1 vài ba công thức thực tiễn nhằm tính số phút nhập Excel:

Công trăng tròn phút nhập A2: =A2 + TIME(0,20,0)

Trừ A2 cho tới 30 phút: =A2 – TIME(0,30,0)

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

Trong quy tắc tính của khách hàng, phân tách số phút cho tới 1440 – số phút nhập một ngày và tăng thời hạn chính thức vào :

=Start time + (N minutes / 1440)

Để trừ số phút, thay cho vết + trở thành vết -. Ví dụ :

Cộng tăng 200 phút: =A2 + (200/1440)

Trừ cút 300 phút: =A2 -(300/1440)

Cách cộng/trừ giây nhập thời hạn cụ thể

Để thêm vào đó số giây thấp hơn 60 nhập thời hạn rõ ràng, dùng hàm TIME:

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

Để thêm vào đó số giây to hơn 59, 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ự động tuy nhiên thay cho vết + trở thành vết -.

Trên trang tính Excel, công thức nhìn như sau:

• Thêm 30 giây nhập dù A2: =A2 + TIME(0,0,31)
• Thêm 1200 giây nhập dù A2: =A2 + (1200/86400)
• Trừ dù A2 cho tới 40 giây: =A2 – TIME(0,0,40)
• Trừ dù A2 cho tới 900 giây: =A2 – (900/86400)

Cách nằm trong thời hạn nhập Excel

Phép nằm trong Excel hay sử dụng hàm SUM, và vận dụng format tương thích cho tới thành phẩm.

Giả sử chúng ta sở hữu một vài ba thời hạn nhập cột B và bạn thích nằm trong bọn chúng cùng nhau. Quý Khách viết lách công thức SUM như sau =SUM(B2:B4), thành phẩm hiển thị theo đuổi format khoác tấp tểnh như hh:mm:ss

Trong một vài ba tình huống, format thời hạn khoác tấp tểnh rất có thể ổn định, tuy nhiên nhiều khi bạn thích hiển thị tổng thời hạn bên dưới dạng phút và giây, hoặc chỉ hiển thị giây. Khi tê liệt, chúng ta ko cần thiết quy tắc tính nào là cả, chúng ta chỉ việc format lại thời hạn cho tới dù tính chứa chấp công thức SUM.

Nhấp nên nhập dù tính và lựa chọn Format Cells kể từ list tùy lựa chọn, nhấn Ctrl + 1 để hé vỏ hộp thoại Format Cells. Chọn Custom từ Category list và gõ một trong những format sau nhập vỏ hộp Type:

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

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

Kết ngược như sau:

Cách nằm trong qua quýt 24 giờ nhập Excel

Để đo lường và tính toán thành phẩm rộng lớn 24 giờ, chúng ta cũng có thể dùng công thức SUM, và lựa chọn 1 trong những format sau:

Để coi thành phẩm format nhập trang tính thực, coi hình sau đây (công thức SUM được nhập nhập dù A9 cho tới dù A13:

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

Chú ý. Định dạng thời hạn chỉ vận dụng cho tới số dương. Nếu thành phẩm đo lường và tính toán là số âm, tức thị chúng ta lấy độ quý hiếm nhỏ rộng lớn trừ cho tới độ quý hiếm to hơn, thành phẩm tiếp tục hiển thị #####.

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

Để hiện nay thị một khoảng chừng thời hạn to hơn 24 giờ, 60 phút, 60 giây, hãy sử dụng custom time format nơi nhưng mà mã đơn vị chức năng thời hạn ứng trực thuộc vết ngoặc vuông, ví dụ điển hình như [h], [m], hoặc [s]. Dưới đấy là quá trình chỉ dẫn chi tiết:

1. Chọn một hoặc  nhiều dù nhưng mà bạn thích format.
2. Nhấp con chuột nên nhập những dù được lựa chọn rồi lại nhấp con chuột nên vào Format Cells, hoặc chúng ta cũng có thể nhấn Ctrl + 1, thao tác này sẽ hỗ trợ hé vỏ hộp thoại Format Cells.
3. Trong Number tab, mặt mũi dưới Category, hãy chọn Custom rồi gõ một trong mỗi format thời hạn sau đây nhập vỏ hộp Type:

Lớn rộng lớn 24 giờ: [h]:mm:ss hoặc [h]:mm

Lớn rộng lớn 60 phút: [m]:ss

Lớn rộng lớn 60 giây: [s]

Ảnh chụp screen sau đây đã cho chúng ta biết format thời hạn “lớn rộng lớn 24 giờ” bên trên thực tế:

Dưới đấy là một vài ba format không giống. Chúng tao rất có thể dùng nó nhằm hiển thị những khoảng chừng thời hạn to hơn chiều lâu năm đơn vị chức năng thời hạn chuẩn chỉnh.

Khi được dùng cho tới tài liệu kiểu mẫu của tất cả chúng ta (Thời gian ngoan tổng số 50:40 nhập hình họa chụp screen trên), những format thời hạn này tiếp tục cho tới thành phẩm như sau:

Để những khoảng chừng thời hạn được hiển thị phía trên dễ nắm bắt rộng lớn so với người tiêu dùng, chúng ta cũng có thể bổ sung cập nhật đơn vị chức năng thời hạn vì như thế những kể từ viết lách tắt ứng, ví dụ:

Lưu ý. Mặc mặc dù những khoảng chừng thời hạn bên trên nhìn tương tự chuỗi ký tự động, tuy nhiên bọn chúng vẫn chính là độ quý hiếm số, cũng chính vì tấp tểnh dạng văn bản số nhập Excel chỉ thay cho thay đổi về mẫu mã chứ thực chất bất biến. Vì thế, chúng ta cũng có thể tự tại nằm trong hoặc trừ những khoảng chừng thời hạn và được format một cơ hội thông thường, hãy tham lam chiếu bọn chúng nhập công thức và dùng trong những quy tắc tính không giống.

Cách nằm trong trừ thời hạn to hơn 24 giờ, 60 phút, 60 giây

Để nằm trong khoảng chừng thời hạn mong ước cho 1 khoảng chừng thời hạn cho tới sẵn, hãy lấy số giờ, phút, hoặc giây nhưng mà bạn thích nằm trong cho tới đơn vị chức năng thời hạn ứng nhập một ngày (24 giờ, 1440 phút, 86400 giây), rồi nằm trong số thương cho tới thời hạn chính thức.

Cộng rộng lớn 24 giờ.

Thời gian ngoan chính thức + (N/24)

Cộng rộng lớn 60 phút.

Thời gian ngoan chính thức + (N/1440)

Cộng rộng lớn 60 giây.

Thời gian ngoan chính thức + (N/86400)

Trong tê liệt, N là số giờ, phút, hoặc giây nhưng mà bạn thích nằm trong.

Đây là 1 vài ba ví dụ công thức bên trên thực tế:

Để nằm trong 45 giờ cho tới thời hạn chính thức nhập dù A2:

=A2+(45/24)

Để nằm trong 100 phút cho tới thời hạn chính thức nhập dù A2:

=A2+(100/1440)

Để nằm trong 200 giây cho tới thời hạn chính thức nhập dù A2:

=A2+(200/86400)

Hay là, chúng ta cũng có thể nhập khoảng chừng thời hạn cần thiết nằm trong vào trong 1 dù riêng lẻ rồi tham lam chiếu dù tê liệt nhập công thức sở hữu dạng như nhập hình họa chụp screen bên dưới đây:

Để trừ khoảng chừng thời hạn nhập Excel, hãy dùng công thức tương tự động nhưng mà hãy thay cho vết nằm trong vì như thế vết trừ nhé!

Trừ rộng lớn 24 giờ

Thời gian ngoan chính thức – (N/24)

Trừ rộng lớn 60 phút.

Thời gian ngoan chính thức – (N/1440)

Trừ rộng lớn 60 giây

Thời gian ngoan chính thức – (N/86400)

Ảnh chụp screen sau đây hiển thị những kết quả:

Xem thêm: Cách vẽ máy bay chỉ với 7 bước đơn giản phổ biến

Lưu ý:

1. Nếu khoảng chừng thời hạn được xem toán sở hữu dạng số thập phân, hãy dùng format thời gian/ngày nhằm format dù.
2. Nếu sau thời điểm dùng format và dù hiển thị #####, thì thường thì dù ko đầy đủ rộng lớn nhằm hiển thị độ quý hiếm thời hạn trong thời gian ngày. Để xử lý điều này, hãy không ngừng mở rộng chiều lâu năm dù bằng phương pháp nhấp lưu ban con chuột hoặc kéo khuông ở bên phải của cột.

Đó là toàn cỗ phương pháp để những chúng ta cũng có thể cách nằm trong trừ thời hạn nhập excel. Nếu còn bất kể vướng mắc gì thì chớ ngần ngại cmt xuống bên dưới sẽ được trả lời sớm nhất nhé!

Những kỹ năng chúng ta đang được coi nằm trong khóa học Excel kể từ cơ phiên bản cho tới nâng lên của Học Excel Online. Khóa học tập này hỗ trợ cho mình kỹ năng một cơ hội khá đầy đủ và sở hữu khối hệ thống về những hàm, những khí cụ nhập excel, phần mềm excel nhập công việc… Hiện ni khối hệ thống đang xuất hiện nhiều ưu đãi khi chúng ta ĐK nhập cuộc khóa đào tạo và huấn luyện này. Hãy nhập cuộc ngay lập tức bên trên địa chỉ: Học Excel Online