Khi trích xuất dữ liệu từ các phần mềm khác (ví dụ như phần mềm chấm công) ra excel, chúng ta thường gặp phải 1 vấn đề đó là dữ liệu ngày tháng, thời gian thường bị lỗi, không thể sử dụng để tính toán ngay. Trong bài viết này Học Excel Online sẽ hướng dẫn các bạ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.

Các bạn có thể tải file mẫu 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ụ chúng ta có bảng dữ liệu được trích xuất từ phần mềm chấm công như sau:

Trong đó các cột Date và InTime là dữ liệu dạng Ngày và Giờ. Tuy nhiên những dữ liệu này lại không dùng để tính toán ngay được vì nó không phải dữ liệu đúng chuẩn dạng thời gian mà Excel quy định.

Chúng ta có thể kiểm tra bằng cách sử dụ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ề bản chất dữ liệu trong ô B2 là Text chứ không phải Number. Do đó không dùng trong tính toán được.

=> Đặc điểm của dữ liệu thời gian trích xuất từ phần mềm khác ra Excel thường ở dạng TEXT.

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

Cách 1: Dùng hàm VALUE

Hàm VALUE giúp chuyển dữ liệu từ dạng Text về dạng Number. Về bản chất, dữ liệu thời gian trong Excel là dạng Number.

Cách làm như sau:

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

Ví dụ: E2=VALUE(B2)

Kết quả của đoạn text 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 trở lại vào cột B

Bước 3: Định dạng dữ liệu bằng Format cells

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

• Chọn vùng dữ liệu trong cột B
• Mở Format Cells (phím tắt Ctrl + 1)
• Trong thẻ Number chọn Custome, trong mục Type nhập dd/mm/yyyy

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

Cách 2: 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 ra nhiều cột thì nó còn có tác dụng chuyển đổi dữ liệu về đúng định dạng tiêu chuẩn trong Excel. Cách thực hiện như sau:

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

Bước 2: Trong cửa sổ TextToColumn (step 1 of 3) bấm Next để sang Step 2 of 3

Bước 3: Bấm Next một lần nữa để sang Step 3 of 3

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

Sau đó bấm Finish để hoàn thành.

Chú ý

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

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

Hướng dẫn chuyển kiểu định dạng dữ liệu từ số (Number) sang chữ (Text) trong Excel

Hướng dẫn cách chuyển từ ngày tháng (Date) về định dạng số (Number) hoặc chữ (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 số từ text sang number