Chuyển đổi từ dãy số sang ngày tháng năm sinh (1 người xem)

[ndu96081631]

=

Trên thực tế, cách người ta thường dùng nhất là lộn ngược text thành dạng yyyymmdd. Khi ấy các trị chỉ toàn năm hay năm + tháng vẫn có thể làm việc chung với các trị kia.

Chính xác là cách này và cũng là cách viết tắt hiệu quả nhất

 

[khaothibaclieu]

Cám ơn các bạn đã quan tâm đến chủ đề này. Đúng là mình ít có kinh nghiệm. Mình thiết kế bản để các trường học gửi dữ liệu về cho thống nhất. Tuy nhiên, trước giờ cái vụ nhập ngày tháng năm sinh làm mình rất đau đầu. Vì mình đưa mẫu là một chuyện, nhưng người nhập lại có tuân theo hay không lại là một chuyện. Mình đã từng ràng buộc mọi người nhập định dạng là dd/mm/yyyy thì có khi dữ liệu khi mình tổng hợp bị đảo ngược thành mm/dd/yyyy. Chưa kể là có nhiều học sinh chỉ có năm sinh, hoặc chỉ có tháng và năm sinh. Sau này mình lại bị yêu cầu nhập cho đúng để in các mẫu giấy chứng nhận theo quy định (ngày nhỏ hơn 10 thì phải là hai số, tháng 01, 02 thì phải hai số, nhưng tháng từ 3 đến 9 thì một số). Rất là đau đầu. Cuối cùng mình nghĩ ra một cách là nhập ngày, tháng, năm mỗi thứ 1 cột và dùng validation để ràng buộc. Sau đó mình ghép lại. Cách này khỏe cho mình nhưng những người nhập liệu lại k thích. Vì vậy mình mới nghĩ đến yêu cầu nhập dữ liệu theo kiểu làm biếng như trên để xử lý thành ngày tháng năm sinh. Tuy nhiên mình suy nghĩ chưa thấu đáo vì cách nhập này cũng có quá nhiều sơ hở. Chắc là phải quay lại cách nhập mỗi thứ 1 cột. Rất vui vì các bạn đã góp ý. Mình hiện đang loay hoay tìm cách gỡ đây.

 

[ndu96081631]

Vì mình đưa mẫu là một chuyện, nhưng người nhập lại có tuân theo hay không lại là một chuyện. Mình đã từng ràng buộc mọi người nhập định dạng là dd/mm/yyyy thì có khi dữ liệu khi mình tổng hợp bị đảo ngược thành mm/dd/yyyy.

Cái này không đúng nha!
Chỉ yêu cầu nhập đúng chuẩn Date là được, còn chuyện nó hiển thị ngày trước hay tháng trước lại chẳng liên quan gì đến chuyện nhập cả mà do ta Format Cells trong Excel mà ra
Nói tóm lại:
- Nhập liệu phải đúng chuẩn Date. Nhập ngày trước tháng sau hay tháng trước ngày sau là do tùy thiết lập của từng máy (cụ thể là trong Control Panel) ---> Điều này có nghĩa là: Nếu bạn buộc ngày ta nhập theo quy định dd/mm/yyyy nghĩa là bạn cũng sai luôn (tùy từng máy)
- Excel chỉ có thể can thiệp việc hiển thị (ngày trước hay tháng trước) chứ không quản vụ nhập liệu

 

[khaothibaclieu]

Cái này không đúng nha!
Chỉ yêu cầu nhập đúng chuẩn Date là được, còn chuyện nó hiển thị ngày trước hay tháng trước lại chẳng liên quan gì đến chuyện nhập cả mà do ta Format Cells trong Excel mà ra
Nói tóm lại:
- Nhập liệu phải đúng chuẩn Date. Nhập ngày trước tháng sau hay tháng trước ngày sau là do tùy thiết lập của từng máy (cụ thể là trong Control Panel) ---> Điều này có nghĩa là: Nếu bạn buộc ngày ta nhập theo quy định dd/mm/yyyy nghĩa là bạn cũng sai luôn (tùy từng máy)
- Excel chỉ có thể can thiệp việc hiển thị (ngày trước hay tháng trước) chứ không quản vụ nhập liệu

Vậy thì bạn chỉ cho mình với. Cái này mình hơi bị ngu đây. Tại sao "buộc người ta nhập theo quy định dd/mm/yyyy nghĩa là bạn cũng sai luôn (tùy từng máy)?".
Yêu cầu của mình là chính đáng mà. Mình đâu thể yêu cầu mọi người chỉnh Control Panel cho thống nhất với máy của mình. Hơn nữa mình cũng đã nói dữ liệu cũng khá lộn xộn, trong đó có học sinh chỉ có năm sinh, có học sinh chỉ có tháng và năm sinh. Trên GPE cũng đã có một bạn có nhu cầu tương tự như mình nhưng hình như cuối cùng cũng chưa được toại nguyện. (http://www.giaiphapexcel.com/forum/showthread.php?20764-Định-dạng-ngày-tháng-năm-theo-chuẩn)
Mình cũng không muốn người ta phải nhớ quy ước nhập yyyymmdd. Nếu vậy chẳng thà nhập mỗi thứ một cột còn dễ nhớ cho người nhập hơn.

 

[khaothibaclieu]

Hay là cứ quy ước nhập dạng text ("000000"). Sau đó sẽ chuyển về dd/mm/yyyy (nếu là tháng 01, 02, 10, 11, 12) hoặc chuyển về dd/m/yyyy (nếu từ tháng 3 đến tháng 9). Nếu ngày không có thì nhập 00, khi đó chỉ có tháng và năm thì chỉ ra mm/yyyy (có thể cho các con số 00 của dd thành màu trắng được không?). Tương tự nếu chỉ có năm thì sẽ hiện ra yyyy.
Nhưng nếu vậy thì phải kiểm soát thêm thông tin nhập ngày tháng cho chính xác. Ví dụ ngày không lớn hơn 31 (các tháng 01, 3, 5, 7, 8, 10, 12), hoặc không lớn hơn 30 (các tháng còn lại, trừ tháng 02). Tháng 02 thì không lớn hơn 28 (năm không nhuận) hoặc 29 (năm nhuận). Năm thì theo yêu cầu từ năm (19)90 đến (19)99.
Dữ liệu xuất ra ở dạng text là được.
Cám ơn các bạn quan tâm. Bên dưới là file ví dụ.
P/s: Các đơn vị của mình vẫn còn xài MS Excel 2003.

 

[ndu96081631]

Vậy thì bạn chỉ cho mình với. Cái này mình hơi bị ngu đây. Tại sao "buộc người ta nhập theo quy định dd/mm/yyyy nghĩa là bạn cũng sai luôn (tùy từng máy)?".
.

Tức là thế này: Nếu Control Panel của Windows thiết lập kiểu gì (dd/mm/yyyy hay mm/dd/yyy) thì BẮT BUỘC phải nhập đúng thứ tự như vậy.
Vậy nên trên máy mà Control Panel đang thiết lập mm/dd/yyyy nhưng bạn lại kêu người ta nhập theo chuẩn dd/mm/yyyy thì sai tét tè lè rồi còn gì

Yêu cầu của mình là chính đáng mà. Mình đâu thể yêu cầu mọi người chỉnh Control Panel cho thống nhất với máy của mình. Hơn nữa mình cũng đã nói dữ liệu cũng khá lộn xộn, trong đó có học sinh chỉ có năm sinh, có học sinh chỉ có tháng và năm sinh. Trên GPE cũng đã có một bạn có nhu cầu tương tự như mình nhưng hình như cuối cùng cũng chưa được toại nguyện. (http://www.giaiphapexcel.com/forum/showthread.php?20764-Định-dạng-ngày-tháng-năm-theo-chuẩn)
Mình cũng không muốn người ta phải nhớ quy ước nhập yyyymmdd. Nếu vậy chẳng thà nhập mỗi thứ một cột còn dễ nhớ cho người nhập hơn.

Có 2 cách nhập luôn luôn đúng với mọi thiết lập trong Control Panel, đó là:
- Nhập theo định dạng: yyyy/mm/dd
- Nhập theo định dạng: dd-MMM-yyyy (ví dụ: 15-Jul-2013)
Chỉ có 2 cách đó thôi, ngoài ra không có cách nào khác (đương nhiên là ngoài trừ cách mà thông thường ta vẫn nhập Date hàng ngày thì bắt buộc phải là người biết đôi chút về Windows, Control Panel)
--------------------------------------
Ở đây tôi đang bàn về chuyện làm sao để nhập cho đúng... Còn nếu bạn đang có mở dữ liệu lộn xộn và muốn đưa nó về "chuẩn" thì đấy lại là chuyện khác
--------------------------------------
Nếu cảm thấy khó khăn quá trong chuyện nhập liệu, vậy thôi ta làm luôn chương trình có cái Calendar: Nó hiện ra và ta chỉ việc chọn ngày (khỏi nhập gì cả)... Bảo đảm với cách này thì khỏi sợ nhập sai rồi
Nói thêm 1 chút về cái file của bạn đưa ở trên: Dữ liệu như thế là không CHUẨN rồi (vì không lý nào không 1 cột lại có quá trời kiểu dữ liệu ----> Mai này tính toán làm sao đây?)

 

[khaothibaclieu]

Dữ liệu như thế là không CHUẨN rồi (vì không lý nào không 1 cột lại có quá trời kiểu dữ liệu ----> Mai này tính toán làm sao đây?)

Mình chỉ cần hiển thị cho đúng theo yêu cầu như trên là được. Không cần phải tính toán gì.
Bạn có thể chỉ cho mình cách tô màu trắng của ba ký tự 00/ trong một ô excel có dãy kí tự 00/03/1995 được không? Nghĩa là mình chỉ cần hiển thị 03/1995.

 

[ptm0412]

Hay là cứ quy ước nhập dạng text ("000000"). Sau đó sẽ chuyển về dd/mm/yyyy (nếu là tháng 01, 02, 10, 11, 12) hoặc chuyển về dd/m/yyyy (nếu từ tháng 3 đến tháng 9). Nếu ngày không có thì nhập 00, khi đó chỉ có tháng và năm thì chỉ ra mm/yyyy (có thể cho các con số 00 của dd thành màu trắng được không?). Tương tự nếu chỉ có năm thì sẽ hiện ra yyyy.
Nhưng nếu vậy thì phải kiểm soát thêm thông tin nhập ngày tháng cho chính xác. Ví dụ ngày không lớn hơn 31 (các tháng 01, 3, 5, 7, 8, 10, 12), hoặc không lớn hơn 30 (các tháng còn lại, trừ tháng 02). Tháng 02 thì không lớn hơn 28 (năm không nhuận) hoặc 29 (năm nhuận). Năm thì theo yêu cầu từ năm (19)90 đến (19)99.
Dữ liệu xuất ra ở dạng text là được.
Cám ơn các bạn quan tâm. Bên dưới là file ví dụ.
P/s: Các đơn vị của mình vẫn còn xài MS Excel 2003.

Nếu bạn chịu nghe góp ý chứ không phải như bài 1 "đừng góp ý",. thí có vô vàn cách. Bạn cứ buộc người dùng nhập đủ 6 ký số ddmmyy hoặc mmddyy. Bạn sẽ xử lý sau. Đơn giản cho người dùng và đơn giản cho bạn. Thang không có thì quy định tháng 1, ngày không có quy đinh ngày 1. 2003 hay 2007, 2010, chẳng phải vấn đề.

 

[SA_DQ]

Đúng là "chưa thấy quan tài, chưa đổ lệ!"

[thongBao]Thang không có thì quy định tháng 1, ngày không có quy đinh ngày 1.
2003 hay 2007, 2010, chẳng phải vấn đề.[/thongBao]

Trước tiên sẽ gặp khó cho học sinh (hay học viên), khi cầm giấy tốt nghiệp đi làm việc khác cho cuộc đời; Vì "Chủ nghĩa Lí lịch" nước ta chưa bỏ được lề thói đối chiếu với tài liệu gốc, như chứng mình thư, hay hộ khẩu,. . .
Lúc đó thì quýt làm cam chịu. là cái chắc.

Đừng có thiếu í thức & trách nhiệm như thế với các em học sinh (Học viên); chủ nhân tương lai của đất nước.

 

[ptm0412]

[thongBao]Thang không có thì quy định tháng 1, ngày không có quy đinh ngày 1.
2003 hay 2007, 2010, chẳng phải vấn đề.[/thongBao]

Trước tiên sẽ gặp khó cho học sinh (hay học viên), khi cầm giấy tốt nghiệp đi làm việc khác cho cuộc đời; Vì "Chủ nghĩa Lí lịch" nước ta chưa bỏ được lề thói đối chiếu với tài liệu gốc, như chứng mình thư, hay hộ khẩu,. . .
Lúc đó thì quýt làm cam chịu. là cái chắc.

Đừng có thiếu í thức & trách nhiệm như thế với các em học sinh (Học viên); chủ nhân tương lai của đất nước.

Bác không biết rồi, có những em khai sinh không có, ngày tháng không biết, nên khai lý lịch như thế. Hộ khẩu cũng như thế, CMND cũng sẽ như thế. Làm gì có tài liệu gốc nào?

 

[ptm0412]

Nếu nhập đủ 6 số, chứ không phải nhập 2, 3, 4, 5 số rồi định dạng, thì dùng công thức này:

C4 =IF(RIGHT(B4;2)*1<90;"sai năm";IF(MID(B4;3;2)*1>12;"sai tháng";IF(B4*1<100;1900+B4;IF(B4*1<10000;TEXT(INT(B4/100);"00")&"/"&RIGHT(B4;2)+1900;IF(TEXT(DATE(1900+RIGHT(B4;2);MID(B4;3;2);LEFT(B4;2));"mm")*1=MID(B4;3;2)*1;TEXT(DATE(1900+RIGHT(B4;2);MID(B4;3;2);LEFT(B4;2));"dd/mm/yyyy");"sai ngày")))))

 

[khaothibaclieu]

Nếu nhập đủ 6 số, chứ không phải nhập 2, 3, 4, 5 số rồi định dạng, thì dùng công thức này:

C4 =IF(RIGHT(B4;2)*1<90;"sai năm";IF(MID(B4;3;2)*1>12;"sai tháng";IF(B4*1<100;1900+B4;IF(B4*1<10000;TEXT(INT(B4/100);"00")&"/"&RIGHT(B4;2)+1900;IF(TEXT(DATE(1900+RIGHT(B4;2);MID(B4;3;2);LEFT(B4;2));"mm")*1=MID(B4;3;2)*1;TEXT(DATE(1900+RIGHT(B4;2);MID(B4;3;2);LEFT(B4;2));"dd/mm/yyyy");"sai ngày")))))

Mình đã kiểm tra. Vẫn chưa đúng lắm. Bạn kiểm tra lại dùm mình với (file đính kèm).

 

[ptm0412]

Mình đã kiểm tra. Vẫn chưa đúng lắm. Bạn kiểm tra lại dùm mình với (file đính kèm).

Điều kiện ban đầu tôi yêu cầu là: Nếu nhập đủ 6 số, chứ không phải nhập 2, 3, 4, 5 số rồi định dạng, thì ...

Do đó ô B4 phải là 000090 chứ không phải 90

 

[khaothibaclieu]

Điều kiện ban đầu tôi yêu cầu là: Nếu nhập đủ 6 số, chứ không phải nhập 2, 3, 4, 5 số rồi định dạng, thì ...

Do đó ô B4 phải là 000090 chứ không phải 90

Mình hiểu rồi. Phải format dạng text ở ô B4. Cám ơn bạn rất nhiều.

 

[khaothibaclieu]

Nếu nhập đủ 6 số, chứ không phải nhập 2, 3, 4, 5 số rồi định dạng, thì dùng công thức này:

C4 =IF(RIGHT(B4;2)*1<90;"sai năm";IF(MID(B4;3;2)*1>12;"sai tháng";IF(B4*1<100;1900+B4;IF(B4*1<10000;TEXT(INT(B4/100);"00")&"/"&RIGHT(B4;2)+1900;IF(TEXT(DATE(1900+RIGHT(B4;2);MID(B4;3;2);LEFT(B4;2));"mm")*1=MID(B4;3;2)*1;TEXT(DATE(1900+RIGHT(B4;2);MID(B4;3;2);LEFT(B4;2));"dd/mm/yyyy");"sai ngày")))))

Trước giờ mình xài công thức này rất ổn. Nhưng bây giờ đã có học sinh có năm sinh 2000 nên công thức này bị sai năm sinh. Ví dụ nhập "010100" thì sẽ ra ngày sinh "01/01/1990" chứ không phải là "01/01/2000" như mình mong muốn. Các bạn giúp mình sửa công thức với (xem file gửi kèm)

 

[Phuocam]

Trước giờ mình xài công thức này rất ổn. Nhưng bây giờ đã có học sinh có năm sinh 2000 nên công thức này bị sai năm sinh. Ví dụ nhập "010100" thì sẽ ra ngày sinh "01/01/1990" chứ không phải là "01/01/2000" như mình mong muốn. Các bạn giúp mình sửa công thức với (xem file gửi kèm)

Thử công thức này:
=TEXT(DATE(1900+RIGHT(B4,2)+(RIGHT(B4,2)*1<90)*100,MID(B4,LEN(B4)-3,2),LEFT(B4,LEN(B4)-4)),"dd/mm/yyyy")

 

[khaothibaclieu]

Thử công thức này:
=TEXT(DATE(1900+RIGHT(B4,2)+(RIGHT(B4,2)*1<90)*100,MID(B4,LEN(B4)-3,2),LEFT(B4,LEN(B4)-4)),"dd/mm/yyyy")

Công thức của bạn chỉ đúng với trường hợp sinh năm 2000, nhưng còn các năm khác thì vẫn chưa đúng.

 

[Phuocam]

Công thức của bạn chỉ đúng với trường hợp sinh năm 2000, nhưng còn các năm khác thì vẫn chưa đúng.

Công thức này đúng trong trường hợp năm sinh từ 1990 đến 2014
Nếu ngày sinh từ 1 dến 9 chỉ cần nhập 1 số, ví dụ 09/10/2001 chỉ cần nhập 91001 (5 số)

 

[khaothibaclieu]

Công thức này đúng trong trường hợp năm sinh từ 1990 đến 2014
Nếu ngày sinh từ 1 dến 9 chỉ cần nhập 1 số, ví dụ 09/10/2001 chỉ cần nhập 91001 (5 số)

Vậy có thể làm cho đúng trong mọi trường hợp năm sinh không bạn? Thứ hai là nếu học sinh chỉ có năm sinh mà không có ngày tháng sinh thì công thức của bạn vẫn chưa đúng (cái vụ chỉ có năm sinh không thì ở địa phương tui vẫn còn nhiều học sinh trong khai sinh chỉ có năm sinh).

 

[Phuocam]

Vậy có thể làm cho đúng trong mọi trường hợp năm sinh không bạn? Thứ hai là nếu học sinh chỉ có năm sinh mà không có ngày tháng sinh thì công thức của bạn vẫn chưa đúng (cái vụ chỉ có năm sinh không thì ở địa phương tui vẫn còn nhiều học sinh trong khai sinh chỉ có năm sinh).

Công thức này chỉ đúng trong trường hợp nhỏ hơn 100 tuổi:
=IF(B4<=YEAR(TODAY()),B4,TEXT(DATE(1900+MOD(B4,100)+(MOD(B4,100)<=MOD(YEAR(TODAY()),100))*100,INT(MOD(B4,10000)/100),INT(B4/10000)),"dd/mm/yyyy"))