Công thức chung
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Tóm lược
Để chuyển đổi văn bản ở định dạng ngày không được công nhận thành một ngày Excel thích hợp, bạn có thể phân tích cú pháp văn bản và tập hợp một ngày thích hợp với công thức dựa trên một số hàm: DATE, LEFT, MID và RIGHT. Trong ví dụ được hiển thị, công thức trong C6 là:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Công thức này trích xuất các giá trị năm, tháng và ngày một cách riêng biệt và sử dụng hàm DATE để tập hợp chúng thành ngày 24 tháng 10 năm 2000.
Lý lịch
Khi bạn đang làm việc với dữ liệu từ một hệ thống khác, bạn có thể gặp phải tình huống trong đó ngày tháng không được Excel nhận dạng đúng, thay vào đó, hệ thống này coi ngày tháng giống như văn bản. Ví dụ: bạn có thể có các giá trị văn bản như sau:
| Bản văn | Ngày đại diện |
| 20001024 | 24 tháng 10 năm 2000 |
| 20050701 | Ngày 1 tháng 7 năm 20115 |
| 19980424 | 24 tháng 4 năm 1998 |
| 28.02.2014 | 28 tháng 2, 2014 |
Khi Excel đã đánh giá giá trị ngày là văn bản, một tùy chọn là sử dụng công thức để phân tích văn bản thành các thành phần của nó (năm, tháng, ngày) và sử dụng các giá trị này để tạo ngày với hàm DATE. Như đã lưu ý ở trên, trước tiên tôi khuyên bạn nên thử các giải pháp bên dưới (thêm số không và sử dụng văn bản vào cột) trước khi sử dụng công thức. Cả hai cách giải quyết đều nhanh hơn và đòi hỏi ít nỗ lực hơn.
Giải trình
Hàm DATE tạo một ngày hợp lệ bằng cách sử dụng ba đối số: năm, tháng và ngày:
=DATE(year,month,day)
Trong ô C6, chúng tôi sử dụng các hàm LEFT, MID và RIGHT để trích xuất từng thành phần này từ chuỗi văn bản và cung cấp kết quả vào hàm DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Hàm LEFT trích xuất 4 ký tự ngoài cùng bên trái cho năm, hàm MID trích xuất các ký tự ở vị trí 5-6 cho tháng và hàm RIGHT trích xuất 2 ký tự ngoài cùng bên phải là ngày. Mỗi kết quả được trả về trực tiếp cho hàm DATE. Kết quả cuối cùng là một ngày Excel thích hợp có thể được định dạng theo bất kỳ cách nào bạn muốn.
Cách tiếp cận này có thể được tùy chỉnh khi cần thiết. Ví dụ: định dạng ngày không được công nhận trong hàng 8 là dd.mm.yyyy và công thức trong C8 là:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Văn bản dài
Đôi khi bạn có thể có các ngày ở dạng dài hơn như "Ngày 11 tháng 4 năm 2020 08:43:13" mà Excel không nhận dạng đúng. Trong trường hợp này, bạn có thể điều chỉnh chuỗi theo cách cho phép Excel nhận dạng chính xác ngày bằng hàm SUBSTITUTE. Công thức bên dưới thay thế phiên bản thứ hai của dấu cách ("") bằng dấu phẩy và dấu cách (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Khi chúng ta thêm dấu phẩy sau tên tháng, Excel sẽ hiểu ngày tháng, nhưng nó vẫn cần một chút "đá". Đó là lý do tại sao chúng tôi thêm số 0 vào cuối. Phép toán khiến Excel thử và chuyển đổi chuỗi thành một số. Khi thành công, điều này sẽ dẫn đến một ngày Excel hợp lệ. Lưu ý rằng bạn có thể cần áp dụng định dạng số ngày để hiển thị ngày một cách chính xác.
Không có công thức
Trước khi bạn sử dụng công thức để phân tích cú pháp và tạo ngày từ văn bản theo cách thủ công, hãy thử một trong các cách sửa lỗi bên dưới. Tùy chọn đầu tiên sử dụng một phép toán để "di chuyển" Excel một chút và buộc nó phải thử và đánh giá văn bản dưới dạng một số. Vì trên thực tế, ngày tháng trong Excel là những con số, điều này thường có thể thực hiện thủ thuật. Bạn có thể cần áp dụng định dạng ngày tháng nếu các thao tác thành công.
Thêm số 0 để sửa ngày
Đôi khi, bạn sẽ gặp các ngày ở định dạng văn bản mà Excel sẽ nhận ra. Trong trường hợp này, bạn có thể buộc Excel chuyển đổi giá trị văn bản thành ngày tháng bằng cách thêm số 0 vào giá trị. Khi bạn thêm số 0, Excel sẽ cố ép các giá trị văn bản thành số. Vì ngày tháng chỉ là số nên thủ thuật này là một cách tuyệt vời để chuyển đổi ngày tháng ở định dạng văn bản mà Excel thực sự nên hiểu.
Để chuyển đổi ngày tại chỗ bằng cách thêm số 0, hãy thử Dán Đặc biệt:
- Nhập không (0) vào ô không sử dụng và sao chép vào khay nhớ tạm
- Chọn những ngày có vấn đề
- Dán Đặc biệt> Giá trị> Thêm
- Áp dụng định dạng ngày (nếu cần)
Bạn cũng có thể thêm số 0 vào một công thức như sau:
=A1+0
trong đó A1 chứa một ngày không được công nhận.
Văn bản vào cột để sửa ngày tháng
Một cách khác để Excel nhận dạng ngày tháng là sử dụng Tính năng Văn bản thành Cột:
Chọn cột ngày, sau đó thử Dữ liệu> Văn bản thành cột> Cố định> Kết thúc
Nếu Excel nhận ra ngày tháng, nó sẽ sửa tất cả chúng trong một bước.
