Xử lý dữ liệu ngày tháng năm không chuẩn (text) về dạng Date trong Excel

Chào các anh chị, em có một vấn đề hơi khó nhằn với dữ liệu ngày tháng mà em muốn nhờ mọi người chỉ giáo ạ.

Dữ liệu của em xuất ra từ một hệ thống khác, nó toàn ở dạng text chứ không phải dạng ngày tháng chuẩn. Ví dụ có các kiểu như:

• '2023/10/26
• 26-10-2023
• October 26, 2023
• 26.10.2023

Mỗi lần muốn dùng các hàm liên quan đến ngày tháng (như tính toán khoảng thời gian, sắp xếp theo ngày...) thì em lại phải ngồi copy-paste rồi dùng Text to Columns, hoặc dùng các hàm chuyển đổi thủ công rất mất thời gian, mà số lượng dữ liệu lại khá lớn.

Em có thử dùng hàm DATEVALUE nhưng nó chỉ nhận một số định dạng nhất định. Có cách nào để tự động hóa việc chuyển đổi tất cả các định dạng ngày tháng dạng text này về đúng dạng Date của Excel không ạ? Em dùng Excel bản 365.

Em có tìm hiểu qua Power Query nhưng chưa rành lắm, có thể hướng dẫn em cách dùng Power Query để xử lý vụ này không ạ? Hoặc có công thức nào xử lý triệt để hơn không?

Em xin cảm ơn!

Cái vụ dữ liệu ngày tháng lộn xộn này đúng là ám ảnh thật! Mình cũng từng vật lộn với nó rồi.

Ngoài cách dùng hàm DATEVALUE mà bạn kia chia sẻ, mình còn hay dùng cách này nữa:

1. Tạo một cột mới.
2. Trong cột mới đó, nhập công thức: =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)) (giả sử ngày tháng dạng DD.MM.YYYY hoặc DD/MM/YYYY nằm ở A1). Công thức này cần tùy chỉnh một chút tùy theo định dạng cụ thể của bạn.
3. Với các định dạng khác như "October 26, 2023", bạn có thể dùng kết hợp các hàm như LEFT, RIGHT, MID, FIND để tách từng phần năm, tháng, ngày ra rồi dùng hàm DATE để ghép lại.

Quan trọng là mình cần xác định được các "mẫu" định dạng ngày tháng phổ biến nhất trong file của bạn để có công thức xử lý phù hợp. Bạn có thể chia sẻ thêm một vài định dạng "khó nhằn" khác mà bạn đang gặp phải không? Biết đâu lại có cách tối ưu hơn nữa!

Chào bạn,

Mình hiểu vấn đề này lắm, đúng là dữ liệu "text" dạng ngày tháng kiểu này làm đau đầu thật. Thay vì dùng Text to Columns hay các hàm chuyển đổi thủ công, bạn thử cách này xem sao:

Đầu tiên, tạo một cột phụ. Sau đó, nhập công thức sau, giả định dữ liệu ngày tháng của bạn nằm ở ô A1:

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

Công thức này sẽ giúp chuẩn hóa các định dạng phổ biến như 26.10.2023, 2023/10/26 về dạng số mà Excel hiểu là ngày tháng. Với định dạng October 26, 2023, Excel thường tự nhận diện được.

Bạn kéo công thức xuống cho toàn bộ cột. Sau đó, bạn có thể định dạng cột này là Date hoặc dùng trực tiếp.

Hy vọng cách này giúp bạn tiết kiệm thời gian!

Công thức =DATEVALUE(SUBSTITUTE(SUBSTITUTE(A1,".","-"),"/","-")) mà bạn kia chia sẻ đúng là cứu cánh cho các định dạng như 26.10.2023 và 2023/10/26 đấy!

Với trường hợp "October 26, 2023", Excel khá thông minh nên thường tự nhận diện được. Tuy nhiên, nếu có những định dạng "dị" hơn nữa, mình nghĩ có thể dùng kết hợp LEFT, MID, RIGHT và FIND để tách các phần năm, tháng, ngày ra, sau đó dùng hàm DATE để ghép lại.

Bạn có thể chia sẻ thêm một vài định dạng "khó nhằn" mà bạn đang gặp không? Biết đâu lại có cách xử lý tinh gọn hơn nữa!