Hướng dẫn cách sửa lỗi ngày tháng của dữ liệu được trích xuất từ phần mềm ra excel

Khi kết xuất dữ liệu từ phần mềm khác (ví dụ như phần mềm chấm công) sang excel chúng ta thường gặp một vấn đề đó là dữ liệu ngày giờ thường bị lỗi, không thể sử dụng để tính toán. Phải. Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách khắc phục lỗi ngày kết xuất dữ liệu từ phần mềm ra excel. Bạn có thể tải xuống tệp ví dụ tại đây: http://bit.ly/2PVCpDv

Đặc điểm dữ liệu thời gian trích xuất từ ​​phần mềm

Trong file ví dụ ta có bảng dữ liệu được trích xuất từ ​​phần mềm chấm công như sau:

Trong những cột nào Ngày Và Đúng giờ là dữ liệu có dạng Ngày Và Giờ. Tuy nhiên, những dữ liệu này không thể được sử dụng để tính toán ngay lập tức vì nó không phải là dữ liệu chính xác ở định dạng thời gian do Excel chỉ định. Ta có thể kiểm tra bằng hàm IsNumber cho cột B và C như sau: Tại ô E2 nhập công thức =ISNUMBER(B2) => Kết quả là FALSE Như vậy về cơ bản dữ liệu tại ô B2 là Text chứ không phải text. không phải Số. Do đó, nó không thể được sử dụng trong tính toán. => Đặc điểm dữ liệu thời gian được trích xuất từ ​​phần mềm khác sang Excel thường ở dạng TEXT.

Cách sửa lỗi định dạng thời gian

Phương pháp 1: Sử dụng GIÁ TRỊ . chức năng

Hàm VALUE giúp chuyển đổi dữ liệu từ định dạng Văn bản sang Số. Về bản chất, dữ liệu thời gian trong Excel là định dạng Số. Đây là cách thực hiện:

Bước 1: Tại cột E (cột con) ta sử dụng hàm VALUE, đối tượng của hàm VALUE là giá trị tại cột B

Ví dụ: E2=VALUE(B2) Kết quả của văn bản 1/Jun/2018 khi chuyển sang Number có giá trị là 43252

Bước 2: Copy kết quả của hàm Value dán lại vào cột BELL

Bước 3: Định dạng dữ liệu bằng Định dạng ô Chúng ta sẽ định dạng lại dữ liệu ở cột B để trở về đúng định dạng dd/mm/yyyy bằng cách:

• Chọn phạm vi dữ liệu trong cột XÓA
• Mở Format Cells (phím tắt Ctrl + 1)
• Trong tab Number chọn Custome, trong trường Type nhập dd/mm/yyyy

Như vậy kết quả trả về sẽ là dữ liệu thời gian có định dạng ngày/tháng/năm theo đúng chuẩn của Excel.

Cách 2: Sử dụng công cụ TextToColumn

TextToColumn là một công cụ khá hữu ích trong Excel. Ngoài việc tách dữ liệu thành nhiều cột nó còn có tác dụng chuyển đổi dữ liệu về đúng định dạng chuẩn trong Excel. Tiến hành như sau:

Bước 1: Chọn bảng dữ liệu cần chuyển đổi, trong tab Data chọn TextToColumn

Bước 2: Trong cửa sổ TextToColumn (bước 1/3) nhấn Next để sang Bước 2/3

Bước 3: Nhấn Next một lần nữa để chuyển sang Bước 3/3

Tại bước này, chúng ta nhấn vào mục Date và chọn cách hiển thị thời gian tương ứng theo định dạng DMY (D là ngày, M là tháng, Y là năm) Sau đó nhấn Finish để hoàn tất.

Chú ý

• Cả hai phương pháp đều có thể áp dụng cho dữ liệu Ngày, Giờ
• Cách sử dụng hàm VALUE sẽ tuân theo cài đặt ngày tháng mặc định trong Region của máy tính (Control pannel/Region/Date-Time)
• Sử dụng TextToColumn cho phép linh hoạt hơn trong việc chọn cách hiển thị Tháng trước Ngày sau hoặc Ngày trước tháng sau. Kết quả trả về ở vị trí ban đầu, không cần copy/paste như cách dùng VALUE. chức năng

Tìm hiểu thêm nội dung tương tự:

Hướng dẫn chuyển đổi định dạng dữ liệu từ số (Number) sang văn bản (Text) trong Excel

Hướng dẫn cách chuyển đổi từ định dạng ngày tháng (Date) sang số (Number) hoặc văn bản (Text) trong Excel

Sử dụng hàm VALUE và các tùy chọn khác để chuyển đổi định dạng của một số từ văn bản sang số