Lỗi dữ liệu về thời gian là một lỗi khá hay gặp trong Excel. Trong bài viết này, chúng ta hãy cùng Học Excel Online tìm hiểu cách sửa định dạng thời gian bị lỗi về dạng chuẩn cả giờ phút giây. Đây là một lỗi thường gặp khi lấy dữ liệu từ các phần mềm ra Excel. Để làm rõ hơn nội dung này, chúng ta cùng xét ví dụ sau:

Những dữ liệu dưới đây được trích xuất từ phần mềm khác ra Excel. Trong đó những dữ liệu tại cột A thể hiển các giá trị thời gian bao gồm cả giờ, phút, giây

Tuy nhiên những dữ liệu trên lại không dùng ngay được để tính toán, báo cáo bởi dữ liệu có lỗi. Một số dữ liệu không đúng loại dữ liệu là thời gian mà là loại text, văn bản. Để giúp xác định các lỗi và làm chuẩn hóa lại dữ liệu, chúng ta thực hiện các bước như sau:

Kiểm tra lỗi dữ liệu thời gian

Đặc trưng của dữ liệu thời gian là nó thuộc loại dữ liệu kiểu số chứ không phải kiểu text. Chúng ta kiểm tra dữ liệu với hàm IsText hoặc hàm IsNumber như sau

Trong ví dụ trên khi sử dụng hàm IsNumber kiểm tra loại dữ liệu tại cột A thì phát hiện có tới 4 nội dung ra kết quả False. Như vậy 4 vị trí đó không phải loại dữ liệu thời gian chuẩn mà là dạng text. Đặc trưng của loại dữ liệu này là có định dạng mặc định nằm về bên trái trong ô.

Xem thêm: Cách phân biệt dạng dữ liệu trong Excel với nhóm hàm IsNumber, IsNonText, IsText

Sau khi phát hiện ra dữ liệu có lỗi, chúng ta sẽ phải tiến hành các bước sửa lỗi dữ liệu này.

Sửa lỗi dữ liệu ngày tháng với hàm VALUE kết hợp hàm TEXT

Hàm TEXT là một hàm giúp đưa tất cả các dữ liệu dạng số về dạng text nhưng vẫn có thể thiết lập định dạng theo ý muốn của dạng Số, Ngày tháng.

Hàm VALUE giúp chuyển dữ liệu từ dạng Text về dạng giá trị (Số hoặc Ngày tháng)

Trong yêu cầu này, chúng ta nhận thấy các dữ liệu vừa có dạng Text, vừa có dạng Ngày tháng. Do đó để có thể động nhất dữ liệu thì chúng ta làm như sau:

Bước 1: Dùng hàm Text để chuyển hết các giữ liệu về dạng Text nhưng vẫn giữ nguyên cách định dạng

Trong cách làm này, chúng ta cần phải xác định đúng loại định dạng thời gian của dữ liệu trong trường hợp này là “dd/mm/yyyy hh:mm:ss”, tức là gồm đủ ngày tháng năm + giờ phút giây ngăn cách với nhau bởi dấu cách.

Các giá trị ngày tháng năm ngăn cách nhau bởi dấu gạch chéo (/)

Các giá trị giờ phút giây ngăn cách nhau bởi dấu hai chấm (:)

Khi đó toàn bộ dữ liệu đã được chuyển về dạng Text.

Bước 2: Chuyển các dữ liệu dạng Text về dạng Value

Sử dụng hàm VALUE để chuyển toàn bộ kết quả của hàm TEXT về dạng giá trị thời gian như sau:

Tại cột D dùng hàm VALUE. Đối tượng cần chuyển là các giá trị tương ứng tại cột B

Tại cột E kiểm tra bằng hàm IsNumber ra kết quả đều là TRUE

Bước 3: Định dạng dữ liệu kết quả về dạng thời gian

CHúng ta thấy trong cột D dữ liệu được biểu diễn dưới dạng con số. Đây chính là dạng quy ước của dữ liệu thời gian khi trong chế độ định dạng General (mặc định của Excel). Để chuyển dữ liệu này về dạng thời gian đầy đủ ngày giờ thì chúng ta thực hiện Format Cells như sau:

• Chọn vùng dữ liệu cần định dạng
• Mở cửa sổ Format Cells chọn Custom
• Trong mục Type viết các ký tự sau: dd/mm/yyyy hh:mm:ss

Như vậy chúng ta đã có được kết quả như mong muốn rồi.

Ngoài ra chúng ta có thể lồng ghép hàm Text trực tiếp vào bên trong hàm Value để có thể rút ngắn các bước như sau:

Sửa lỗi dữ liệu thời gian bằng Paste Special

Ngoài ra chúng ta còn có 1 cách nữa khá đơn giản nhưng lại rất hiệu quả, đó là sử dụng tính năng Paste Special. Cách làm như sau:

Bước 1: Chọn toàn bộ vùng dữ liệu (click chuột vào vị trí bắt đầu trong cột dữ liệu rồi bấm phím tắt Ctrl+Shift+ Mũi tên xuống)

Bước 2: Copy vùng dữ liệu được chọn

Bước 3: Tại vị trí muốn dán kết quả, bấm chọn Paste Special

Bước 4: Trong cửa sổ Paste Special chọn Add trong phần Operation rồi bấm OK

Kết quả là chúng ta có được dữ liệu mới đã sửa lại đúng định dạng theo yêu cầu

Kết luận

Qua bài viết này, chúng ta biết tới 1 phương pháp rất nhanh và hiệu quả giúp xử lý lỗi dữ liệu thời gian: đó là sử dụng hàm VALUE kết hợp với hàm TEXT. Khi kết hợp 2 hàm này thì việc xử lý lỗi dữ liệu thời gian không còn khó nữa.

Kết hợp thêm với phương pháp Paste Special chúng ta có thêm được 1 cách rất đơn giản và hiệu quả, giúp việc xử lý lỗi dữ liệu thời gian, dữ liệu dạng số trở nên đơn giản hơn bao giờ hết.

Để tìm hiểu thêm các kiến thức khác về dữ liệu thời gian trong Excel, các bạn có thể tham khảo các bài viết sau:

Hướng dẫn cách xử lý lỗi dữ liệu ngày tháng trong Excel

Cách thiết lập chỉ nhập dữ liệu dạng ngày tháng trong ô trên Excel

Những kiến thức này đều có trong khóa học Excel từ cơ bản tới chuyên gia của Học Excel Online. Bạn muốn biết thêm nhiều hàm trong Excel, sử dụng tốt các công cụ có trên thanh công cụ của Excel? Hãy tham gia ngay khóa học để tìm hiểu nhé. Ngoài ra có rất nhiều ưu đãi khi bạn đăng ký khóa học tại địa chỉ dưới đây: