Xử lý dữ liệu ngày tháng bị sai định dạng - Chia sẻ kinh nghiệm

Chào mọi người,

Dạo gần đây mình làm việc với một file excel mà các cột ngày tháng cứ bị nhảy lung tung, lúc thì là số, lúc thì là định dạng khác. Khá là khó chịu vì ảnh hưởng đến việc sắp xếp và phân tích dữ liệu.

Mình đã thử nhiều cách nhưng không ăn thua, mãi hôm qua mới tìm ra được nguyên nhân và cách xử lý khá đơn giản. Chia sẻ lại cho anh em nào gặp tình huống tương tự nhé.

Vấn đề: Dữ liệu ngày tháng (Date) bị Excel nhận dạng sai, thường là kiểu Text hoặc Number thay vì Date.

Nguyên nhân thường gặp:

• Nhập liệu thủ công sai định dạng.
• Copy/paste dữ liệu từ nguồn khác mà không đúng định dạng.
• Cài đặt vùng miền/ngôn ngữ của máy tính khác với định dạng ngày tháng mong muốn.

Cách xử lý:

1. Kiểm tra định dạng cột: Chọn cột chứa dữ liệu ngày tháng, chuột phải chọn Format Cells..., xem định dạng hiện tại. Nếu không phải Date thì đổi lại.
2. Sử dụng chức năng Text to Columns:
• Chọn cột dữ liệu bị sai.
• Vào tab Data -> Text to Columns.
• Chọn Delimited hoặc Fixed width (thường là Delimited nếu không có gì đặc biệt), nhấn Next.
• Ở bước này, quan trọng là chọn General, sau đó nhấn Finish. Excel sẽ cố gắng nhận diện lại định dạng.
• Nếu vẫn chưa được, quay lại bước 1 để chỉnh định dạng cột.
3. Sử dụng công thức (nếu cần giữ nguyên định dạng gốc hoặc xử lý phức tạp hơn):
• Ví dụ, nếu ngày tháng đang ở dạng Text như "2023-10-27" và bạn muốn chuyển nó thành Date thực sự để dùng hàm DATEVALUE, bạn có thể dùng:

=DATEVALUE(SUBSTITUTE(A1,"-","/"))

• Hoặc nếu định dạng là "27/10/2023" (kiểu Text) và bạn muốn chuyển đổi:

=DATEVALUE(A1)

• Sau khi có cột dữ liệu Date đúng, bạn có thể copy và paste Special (Values) đè lên cột cũ hoặc tạo cột mới.

Mình thấy cách dùng Text to Columns là nhanh và hiệu quả nhất trong hầu hết các trường hợp. Hy vọng chia sẻ này giúp ích được cho mọi người!

Có anh em nào có cách nào khác hay hơn thì chia sẻ thêm nhé.

Mình cũng từng gặp tình trạng này rồi, đúng là bực mình thật! Nhất là khi cần sắp xếp theo thứ tự thời gian hay dùng các hàm liên quan đến ngày tháng.

Bạn có thể chia sẻ cụ thể hơn cách bạn xử lý được không? Mình tò mò xem có khác với cách mình hay làm không. Thường thì mình sẽ dùng chức năng "Text to Columns" rồi chọn định dạng Date, hoặc dùng công thức như =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)) (nếu định dạng DD/MM/YYYY) để chuyển đổi. Đôi khi phải kết hợp với việc kiểm tra xem ô đó có đang ở định dạng Text hay không nữa.

Rất mong được học hỏi thêm từ bạn!