Dữ liệu ngày tháng năm không đúng định dạng trong Excel là một vấn đề thường gặp, gây khó khăn cho việc xử lý và phân tích. Bài viết này sẽ hướng dẫn bạn cách sử dụng hàm SUBSTITUTE kết hợp với hàm DATE để sửa lỗi ngày sai trong Excel, giúp chuẩn hóa dữ liệu và tăng hiệu quả công việc.
Mô tả cách sử dụng hàm SUBSTITUTE để sửa lỗi ngày tháng trong Excel
Như hình minh họa, ô A3 hiển thị ngày tháng theo định dạng dd/mm/yyyy. Tuy nhiên, giá trị thực tế là ngày 12 tháng 2, chứ không phải ngày 2 tháng 12. Chúng ta sẽ sử dụng hàm SUBSTITUTE để tách riêng ngày, tháng và năm, sau đó dùng hàm DATE để sắp xếp lại theo đúng định dạng.
Đối với dữ liệu dạng text như ô A2, năm thường nằm ở 4 ký tự cuối. Do đó, chúng ta có thể lấy năm bằng hàm RIGHT(A2,4), kết quả là 2018. Vậy là chúng ta đã có năm cho hàm DATE(Năm, Tháng, Ngày).
Vậy làm thế nào để tách tháng và ngày? Dấu “/” được sử dụng để phân cách ngày, tháng và năm. Dựa vào đó, chúng ta có các phương pháp sau:
Định dạng dd/mm/yyyy – “00/00/0000”
Ví dụ: “02/12/2018” hoặc “01/02/2018”
Với định dạng này, việc tách ngày và tháng khá đơn giản. Chúng ta sử dụng hàm LEFT("01/02/2018", 2) để lấy ngày (01) và hàm MID("02/12/2018",4,2) để lấy tháng (12).
Kỹ năng Excel rất quan trọng, ngay cả khi đã có phần mềm hỗ trợ. Bạn đã thành thạo Excel chưa? Tham khảo khóa học Excel để nâng cao kỹ năng:
Quảng cáo khóa học Excel
Định dạng d/m/yyyy – “0/0/0000”
Ví dụ: “12/1/2018” hoặc “14/2/2018”
Trong trường hợp này, số ký tự của ngày và tháng không cố định, vị trí cũng thay đổi. Vì vậy, chúng ta sẽ sử dụng hàm SUBSTITUTE để tách ngày và tháng. Công thức như sau:
- Tháng:
=TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",9)),1*9+1,9)) - Ngày:
=TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",9)),0*9+1,9))
Tại sao lại sử dụng số 9? Số 9 được sử dụng để tạo khoảng trắng đủ lớn, giúp tách các thành phần ngày, tháng, năm một cách hiệu quả. Việc sử dụng 0*9+1 và 1*9+1 giúp xác định vị trí bắt đầu của tháng và ngày sau khi đã thay thế dấu “/” bằng chuỗi khoảng trắng.
Cuối cùng, chúng ta kết hợp ba thành phần trên với hàm DATE để có kết quả mong muốn.
=IFERROR(
DATE(RIGHT(A2,4),TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",9)),1*9+1,9)),TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",9)),0*9+1,9))),
DATE(YEAR(A2),DAY(A2),MONTH(A2)))Hàm IFERROR được sử dụng để xử lý trường hợp dữ liệu ngày tháng bị nhập ngược định dạng mm/dd. Trong trường hợp này, chúng ta sẽ sử dụng các hàm DAY, MONTH, và YEAR để lấy ngày, tháng và năm tương ứng.
Sử dụng Text to Columns
Ngoài cách sử dụng hàm, bạn cũng có thể sử dụng công cụ Text to Columns để sửa lỗi ngày tháng nhanh chóng:
Hướng dẫn sử dụng Text to Columns để sửa lỗi ngày tháng
Tóm lại, bài viết đã hướng dẫn bạn cách sử dụng hàm SUBSTITUTE và DATE, cũng như công cụ Text to Columns để sửa lỗi ngày tháng sai trong Excel. Hy vọng những kiến thức này sẽ giúp bạn xử lý dữ liệu hiệu quả hơn. Hãy khám phá thêm các thủ thuật Excel khác trên website Thủ Thuật để nâng cao kỹ năng của bạn!
Discussion about this post