Giúp sửa dữ liệu ngày tháng nhập sai ngày thành tháng (1 người xem)

[jbkid]

Mình làm việc với một phần mềm Quản lý cơ sở dữ liệu, khi xuất dữ liệu từ phần mềm ra file Excel thì một số trường dữ liệu bị lỗi:
1. các cột ngày tháng bị lỗi như sau:
- các ngày lớn hơn 12 thì hiển thị đúng và có dạng text
- các ngày nhỏ hơn 12 thì hiển thị dạng date nhưng bị sai, tháng biến thành ngày và ngày biến thành tháng.
2. Cột Trình độ học vấn thay vì hiển thị dạng text "12/12, 9/12..." thì lại hiển thị dạng date

Mình muốn chuẩn hóa dữ liệu trong file này, định dạng dữ liệu trong một cột phải đồng nhất với nhau, cột ngày tháng hoặc là kiểu text hết hoặc là kiểu date hết (và phải đúng ngày), cột Trình độ học vấn là kiểu text hiển thị dạng "12/12, 9/12...". Các bạn giúp đỡ mình với, nếu ko phải nhập lại dữ liệu của mấy nghìn người chắc mình die sớm mất Cảm ơn các bạn trước nha.

 

[songqueqb]

1. các cột ngày tháng bị lỗi như sau:
- các ngày lớn hơn 12 thì hiển thị đúng và có dạng text
- các ngày nhỏ hơn 12 thì hiển thị dạng date nhưng bị sai, tháng biến thành ngày và ngày biến thành tháng.

dự liệu của bạn là một thứ hỗn độn
ở cột "ngày Sinh" lỗi do định dạng lúc nhập liệu rồi
bạn định dạng tháng trước - ngày sau > bạn lại nhập ngày trước tháng sau
- theo mình dùng công thức day() và month () cắt ra và nối lại )

2. Cột Trình độ học vấn thay vì hiển thị dạng text "12/12, 9/12..." thì lại hiển thị dạng date

Bạn định dạng ngày tháng rồi
chỉ việc cắt ngày bằng công thức
=day(...)&"/"&month(...) là OK theo ý bạn

 

[jbkid]

Cảm ơn bạn đã quan tâm. Dữ liệu đó là kết xuất từ phần mềm chứ ko phải mình nhập. Vấn đề (1), do dữ liệu lẫn lộn giữ kiểu text và kiểu date, hàm day() và month() vẫn nhận tham số là text trong khi mình chỉ muốn sửa lại dữ liệu date thôi, vì dữ liệu date mới bị sai ngày. Cái khó là ở chỗ đó.
Vấn đề (2), mình có thể cho hiển thị dạng 12/12 ok, nhưng bản chất nó vẫn là kiểu date, khi mình format cell sang dạng text thì nó biến thành một chuỗi số. Mình muốn định dạng dạng text nhưng vẫn hiển thị đúng "12/12, 9/12, v.v..."

 

[solomon2211]

Dữ liệu cột C của bạn là dạng tháng, ngày, năm (theo windows cài đặt). Còn ô định dạng là ngày tháng năm. Do vậy, bạn sửa lại như sau:
- Tại ô G2, bạn nhập: =DATEVALUE(IF(C2<>0,TEXT(C2,"dd/mm/yyyy"),C2))
- Copy đến hết bảng tính, dán giá trị lại cột C.

Dữ liệu cột D có 2 dạng: trình độ x/12 hoặc x/10. Bạn đến H2 và nhập =TEXT(D2,"mm/dd")
Copy đến hết bảng tính sau đó dán giá trị vào cột D

 

[jbkid]

Cột trình độ học vấn thì mình làm theo cách của bạn songqueqb và bạn solomon2211 đều được. Nhưng cột ngày sinh vẫn không đúng theo ý mình. Mình muốn dòng đầu tiên "Nguyễn Thị Luân" thì ngày sinh phải là "9/1/1978", dòng thứ 2 "Cung Thị Phương An" thì ngày sinh là "13/2/1972".
Cách của bạn songqueqb thì làm đổi luôn cả những ngày >12, "13/2/1972" --> "2/13/1972"
Cách của bạn solomon 2211 thì những ngày <=12 thì đúng nhưng >12 đều cho kết quả #VALUE!
-----------------------------------------------------------------------------------------------------------------------
Các bạn chú ý giùm mình là mình muốn sửa bản chất của dữ liệu, chứ ko phải chỉ là hiển thị thôi nha. Nếu chỉ là hiển thị thì mình chọn kiểu dữ liệu Custom rồi nhập "mm/dd/yyyy" là ok. Nhưng mình cần dữ liệu chuẩn để còn thống kê, nên phải sửa cho dữ liệu thống nhất và đúng ngày. Mình muốn hỏi là có cách nào kiểm tra dữ liệu của 1 ô là kiểu text hay kiểu date ko? Nếu kiểm tra được thì có thể kết hợp với cách của bạn solomon2211 là ra kết quả.

 

[Ba Tê]

Các bạn chú ý giùm mình là mình muốn sửa bản chất của dữ liệu, chứ ko phải chỉ là hiển thị thôi nha. Nếu chỉ là hiển thị thì mình chọn kiểu dữ liệu Custom rồi nhập "mm/dd/yyyy" là ok. Nhưng mình cần dữ liệu chuẩn để còn thống kê, nên phải sửa cho dữ liệu thống nhất và đúng ngày. Mình muốn hỏi là có cách nào kiểm tra dữ liệu của 1 ô là kiểu text hay kiểu date ko? Nếu kiểm tra được thì có thể kết hợp với cách của bạn solomon2211 là ra kết quả.

- Vậy thì kiểm tra nếu Text thì đúng ngày tháng, nếu không thì tháng thành ngày, phải vậy không?
- Trình độ có 10/7 không? Nếu cứ lấy ngày tháng thì sẽ có trường hợp này.
Xem file, nếu còn gì khác thì sửa tiếp.

 

[sealand]

Bạn mượn tạm cột nào còn trống để tính giá trị trung gian (Ví dụ cột K chẳng hạn). Tại ô K2 gõ công thức sau rồi chép hết xuống:

=IF(ISTEXT(C2),DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)),DATE(YEAR(C2),DAY(C2),MONTH(C2)))

Chọn vùng kết quả trên cột K rồi Copy. Sau đó chọn ô C2 --paste speacial---Value
Xóa cột tạm K

Đối với trình độ thì bạn dung công thức sau rồi làm như trên là được:

=DAY(D2)&"/"&MONTH(D2)

 

[ngyuentin]

bạn sử dụng chuỗi công thức này để trả vể (ngày/tháng/năm) sinh

=TEXT(IF(DAY(C88)<=12;MONTH(C88);DAY(C88));"00")&"/"&TEXT(IF(DAY(C88)<=12;DAY(C88);MONTH(C88));"00")&"/"&YEAR(C88)

và sử dụng chuỗi này để trả về trình độ phổ thông

=TEXT(MONTH(E88);"00")&"/"&DAY(E88)

 

[jbkid]

Mình làm được rồi. Cảm ơn các bạn nhiều nha. Nhất là 2 bạn BaTê và sealand, cho mình biết thêm có hàm istext(). Còn vướng mắc gì nữa mình sẽ hỏi thêm sau.