Hàm tính thời gian trong Excel

Deal Score0
Deal Score0

Trong bài viết này, chúng tôi sẽ giới thiệu một số công thức cộng / trừ thời gian hữu ích là hàm tính thời gian trong Excel

Làm thế nào để tính toán 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 gian trong Excel thường được định dạng dưới dạng số thập phân để làm cho chúng giống như thời gian. bởi vì nó là một con số Bạn có thể cộng và trừ thời gian giống như bất kỳ giá trị số nào khác.

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

= end time – thời gian bắt đầu

tùy thuộc vào cấu trúc dữ liệu Chênh lệch thời gian thực có thể có nhiều dạng, ví dụ:

Trong Excel, thời gian được hiển thị dưới dạng số thập phân. Bạn sẽ nhận được kết quả sau:

Hàm thời gian trong Excel

Phần thập phân ở cột D đúng nhưng không thành vấn đề. để giúp họ cung cấp thêm thông tin hữu ích Bạn có thể sử dụng định dạng thời gian với một trong các mã sau:

Để sử dụng định dạng thời gian, bấm Ctrl + 1 để mở một hộp thoại. Định dạng ô, lựa chọn tập quán đến từ Danh sách chuyên mục và nhập mã thời gian vào ô in.

Bây giờ chúng ta hãy xem cách hoạt động của công thức chênh lệch thời gian và mã thời gian trong một bảng tính thực. Với thời gian bắt đầu trong cột A và thời gian kết thúc trong cột B, bạn có thể sao chép công thức vào cột C và E:

= $ B2- $ A2

Chênh lệch thời gian được hiển thị khác nhau theo định dạng thời gian trong mỗi cột:

Thời gian đã trôi qua được hiển thị khác nhau tùy thuộc vào định dạng thời gian được áp dụng.

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

Công thức thứ hai tính toán chênh lệch thời gian bằng cách sử dụng hàm TEXT.

Một kỹ thuật khác để tính chênh lệch thời gian trong Excel là sử dụng hàm TEXT.

Tính chênh lệch múi giờ với chức năng TEXT

chú ý.

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

Để biểu thị sự khác biệt giữa hai lần một đơn vị (giờ, phút, giây), bạn cần thực hiện các phép tính sau.

Tính giờ giữa hai thời điểm

= (Thời gian kết thúc – Thời gian bắt đầu) * 24

Giả sử thời gian bắt đầu trong ô A2 và thời gian kết thúc trong ô B2, sử dụng Tính toán đơn giản B2-A2 để tính chênh lệch thời gian, sau đó nhân với 24.

= (B2-A2) * 24

Để biết số giờ đầy đủ sử dụng chức năng NS để làm tròn thành số nguyên lớn nhất:

= INT ((B2-A2) * 24)

Tính số giờ giữa hai thời điểm trong Excel

Tổng số phút giữa hai lần:

Để tính số phút giữa hai thời điểm Nhân chênh lệch múi giờ với 1440.

= (Thời gian kết thúc – Thời gian bắt đầu) * 1440

Công thức có thể cho kết quả âm tính hoặc dương tính. Kết quả âm tính xảy ra khi thời gian kết thúc lớn hơn thời gian bắt đầu, ví dụ: hàng 5:

= (B2-A2) * 1440

Tính tổng số phút giữa hai thời điểm trong Excel

Tổng số giây giữa hai thời điểm:

Để tính số giây giữa hai thời điểm Nhân chênh lệch múi giờ với 86400.

= (Thời gian kết thúc – Thời gian bắt đầu) * 86400

Trong ví dụ, công thức sẽ như thế này:

= (B2-A2) * 86400

Tính tổng số giây giữa hai thời điểm.

chú ý. kiểu Tổng quan Công thức chênh lệch thời gian nên được đặt để các ô có công thức chênh lệch thời gian hiển thị kết quả một cách chính xác.

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 đơn vị khác.

Để tính toán sự khác biệt giữa hai thời điểm trong cùng một đơn vị không phụ thuộc vào các đơn vị thời gian khác Sử dụng một trong các chức năng sau.

chênh lệch giờ Bỏ qua phút và giây:

= giờ (B2-A2)

chênh lệch phút Bỏ qua giờ và giây:

= phút (B2-A2)

sự khác biệt thứ hai Bỏ qua giờ và phút:

= giây (B2-A2)

khi sử dụng chức năng giờ phút giâyXin lưu ý rằng kết quả không được vượt quá 24 giờ, 60 phút hoặc 60 giây.

Tính chênh lệch múi giờ ở một đơn vị bỏ qua các đơn vị khác.

chú ý. Nếu thời gian kết thúc lớn hơn thời gian bắt đầu đặc biệt nếu kết quả là một số âm. Công thức sẽ hiển thị một lỗi. #NUM!

Công thức 5. Tính thời gian đã trôi qua từ lúc bắt đầu đến giờ.

để tính toán thời gian đã trôi qua Bạn có thể sử dụng chức năng Bây giờ và Thời gian nếu thời gian trôi qua ít hơn 24 giờ. hiện nay để lấy ngày và giờ hiện tại Sau đó xóa ngày và giờ bắt đầu.

Giả sử rằng ô A2 chứa thời gian bắt đầu của công thức. = Bây giờ () – A2 Nó cho kết quả sau với cột B. Được định dạng đúng. Trong ví dụ này, định dạng ừm

Tính toán thời gian đã trôi qua từ thời gian bắt đầu đến bây giờ

Trong trường hợp thời gian trôi qua vượt quá 24 giờ, hãy sử dụng một trong các định dạng thời gian sau, ví dụ: d ‘days’ h: mm: ss như thể hiện trong hình sau:

Tính thời gian trôi qua trong 24 giờ.

Nếu thời gian mặc định chỉ có giờ và phút mà không có ngày Bạn phải sử dụng. thời gian để tính toán chính xác khoảng thời gian trôi đi. Ví dụ: công thức sau trả về khoảng thời gian từ ô A2 tính bằng giờ.

= Thời gian (Giờ (Bây giờ ()), Phút (Bây giờ ()), Giây (Bây giờ ())) – A2

Tính thời gian đã trôi qua không tính ngày tháng

chú ý. Thời gian đã trôi qua không được cập nhật theo thời gian thực. Nó sẽ chỉ cập nhật khi một bảng tính mới được mở hoặc được tính toán lại. Shift + F9 để tính toán một trang tính mới hoặc nhấn F9 để tính toán lại toàn bộ bảng tính.

Công thức 6 cho thấy sự khác biệt về thời gian “XX ngày, XX giờ, XX phút và XX giây”.

bạn sử dụng chức năng giờ phút thứ hai để đạt được kết quả về thời gian và chức năng nhất quán. NS để tính toán chênh lệch ngày. Sau đó, bạn kết hợp các hàm. vào cùng một công thức với chuỗi văn bản như sau:

= INT (B2-A2) & ”DAY” & HOUR (B2-A2) & ”HOUR” & MINUTE (B2-A2) & ”MINUTE AND” & SECOND (B2-A2) & ”SECOND”

Công thức chênh lệch thời gian dễ sử dụng trong Excel

Để ẩn giá trị 0, hãy lồng bốn hàm. nếu như Tới đó:

= IF (INT (B2-A2)> 0, INT (B2-A2) & ”day“, ””) & IF (HOUR (B2-A2)> 0, HOUR (B2-A2) & ”Hour,“, ””) & IF (MINUTE (B2-A2)> 0, MINUTE (B2-A2) & ”phút và“, ””) & IF (SECOND (B2-A2)> 0, SECOND (B2-A2) & “ thứ hai”,””)

Công thức chênh lệch thời gian trong Excel bỏ qua các giá trị 0

Ngoài ra, bạn có thể tính toán chênh lệch thời gian bằng cách trừ thời gian kết thúc cho thời gian bắt đầu (ví dụ: = B2-A2) và đặt định dạng sau cho ô:

d “ngày” h “giờ” m “phút và” s “giây”

Trừ thời gian bắt đầu cho thời gian kết thúc  và áp dụng định dạng thời gian tùy chỉnh cho các ô.

Làm thế nào để tính toán và hiển thị thời gian âm trong Excel?

khi tính toán chênh lệch thời gian Bạn có thể gặp lỗi ##### Vì sự khác biệt này là âm, tuy nhiên, cũng có một 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 trong Excel thành hệ thống ngày tháng năm 1904.

đi vào Tệp> Tùy chọn> Nâng cao., kéo thanh thả xuống Khi tính toán sổ làm việc này và đánh dấu Sử dụng hệ thống ngày tháng năm 1904..

Chuyển sang hệ thống thông tin vào năm 1904.

Bấm OK để lưu cài đặt mới. Các giá trị âm được hiển thị như sau:

Trong hệ thống ngày năm 1904, thời gian âm được hiển thị dưới dạng số âm.

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

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

Công thức tính thời gian âm trong Excel

chú ý. Lưu ý rằng phương thức đầu tiên trả về giá trị âm dưới dạng giá trị số âm. Hàm TEXT trả về một chuỗi văn bản. và không thể được sử dụng trong toán học hoặc cá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 gian trong Excel:

quai hàm TIME (giờ, phút, giây) làm cho các phép tính dễ dàng hơn Nhưng không được phép cộng và trừ sau 23 giờ, 59 phút hoặc 59 giây nếu bạn muốn tính một khoảng thời gian dài hơn. Sử dụng phép toán dưới đây.

Cộng / Trừ giờ cho một thời gian cụ thể.

Thời gian thêm chức năng là ít hơn 24 giờ.

= thời gian bắt đầu + TIME (N giờ, 0, 0)

Ví dụ: trong ô A2, là thời gian bắt đầu Bạn muốn thêm 2 giờ cho nó, công thức như sau:

= A2 + thời gian (2, 0, 0)

Chức năng TIME có thể được mở rộng lên đến 24 giờ.

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

Công thức cộng số giờ (dưới đây / hơn 24 giờ)

Công thức sau không giới hạn số giờ bạn muốn thêm:

= thời gian bắt đầu + (N giờ / 24)

Ví dụ: để thêm 28 giờ trong ô A2, hãy nhập công thức sau:

= A2 + (28/24)

Công thức chung để thêm giờ vào một thời gian cụ thể trong Excel

Để trừ số giờ Sử dụng công thức tương tự như thay thế + bằng -.

Ví dụ: để xóa ô A2 trong 3 giờ, hãy nhập một trong các công thức sau:

= A2- (3/24)

= A2-TIME (3,0,0)

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

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

Cộng / Trừ Dưới 60 phút

Sử dụng chức năng thời gian để cộng và trừ số phút bạn muốn cho tham số thứ 2:

= thời gian bắt đầu + TIME (0, N phút, 0)

Dưới đây là một số công thức hữu ích để tính số phút trong Excel:

Mất 20 phút trên A2: = A2 + thời gian (0,20,0)

Loại bỏ A2 trong 30 phút: = A2 – Thời gian (0,30,0)

Cộng và trừ hơn 60 phút

trong tính toán của bạn Chia phút cho 1440 – số phút trong một ngày và thêm thời gian bắt đầu:

= Thời gian bắt đầu + (N phút / 1440)

Để xóa phút Thay đổi dấu + thành dấu -, ví dụ:

Cộng với 200 phút: = A2 + (200/1440)

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

Hàm thời gian trong Excel

Cách cộng / trừ giây cho một thời gian cụ thể

Để thêm giây nhỏ hơn 60 vào thời gian được chỉ định. Sử dụng chức năng. thời gian:

= thời gian bắt đầu + TIME (0, 0, N giây)

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

= thời gian bắt đầu + (N giây / 86400)

Để xóa giây sử dụng cùng một công thức Nhưng hãy thay dấu + bằng dấu -.

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

Thêm Excel thường sử dụng các hàm. tóm tắtvà áp dụng định dạng phù hợp với kết quả.

Giả sử bạn có cột B nhiều lần và bạn muốn kết hợp chúng, bạn viết một công thức. tóm tắt như sau = SUM (B2: B4)Kết quả được hiển thị ở định dạng mặc định, ví dụ: hh: mm: ss

Công thức SUM để tăng thời gian trong Excel

Trong một số trường hợp, định dạng thời gian mặc định có thể có sẵn. Nhưng đôi khi bạn muốn hiển thị tất cả thời gian bằng phút và giây. hoặc chỉ hiển thị giây Sau đó, bạn không cần phải thực hiện bất kỳ phép tính nào. Bạn chỉ cần định dạng lại thời gian cho các ô chứa công thức. tóm tắt.

Nhấp chuột phải vào ô và chọn Định dạng ô Từ danh sách các tùy chọn, nhấn Ctrl + 1 để mở một hộp thoại. Định dạng ô. lựa chọn tập quán đến từ Danh sách chuyên mục và nhập một trong các định dạng sau vào hộp in:

Hiển thị thời gian theo phút và giây: [m]:NS

Hiển thị thời gian tính bằng giây: [ss]

Các kết quả sau:

Sử dụng định dạng thời gian tùy chỉnh để hiển thị tất cả thời gian theo phút và giây.  hoặc chỉ giây

Làm cách nào để thêm hơn 24 giờ trong Excel?

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

Để xem kết quả định dạng trong trang tính thực tế Xem hình bên dưới (công thức tóm tắt được nhập vào ô A9 đến ô A13:

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

chú ý. Định dạng thời gian chỉ áp dụng cho các số dương. Nếu kết quả tính toán là âm tính Điều này có nghĩa là bạn lấy giá trị nhỏ hơn trừ đi giá trị lớn hơn. Kết quả sẽ được hiển thị. #####.

We will be happy to hear your thoughts

Leave a reply

Phần mềm FREE