Những bài viết về Định dạng giờ, ngày, tháng, năm được post ở đây (2 người xem)

[kid112]

xin giúp em sắp xếp theo tháng sinh,nếu trùng tháng sinh thì sắp xếp theo ngày

 

[gaunuicr]

xin giúp em sắp xếp theo tháng sinh,nếu trùng tháng sinh thì sắp xếp theo ngày

Bạn tạo thêm 2 cột phụ. Ví dụ: - Ngày, tháng, năm sinh ở cột D (bắt đầu từ D5). - Ở cột nào đó, M chẳng hạn, nhập tại M5 công thức: =MONTH(D5) - Ở N5 nhập công thức: =DAY(D5) - Bạn Sort... vùng dữ liệu với Sort by là Column M, Then by thư nhất là Column N (Vì không mở được File của bạn nên không nói cụ thể được)

 

[havietchuong]

Những bài viết về Định dạng ngày, tháng, năm được post ở đây

Trong danh sách học sinh dự thi khá đông, do các đơn vị gởi danh sách đến không đúng quy đinh (ngày sinh của HS phải đủ 8 chữ số ngoại trừ trường hợp không có ngày, tháng sinh). Để điều chỉnh tôi có 2 cách suy nghĩ.
1-.Tạo cột phụ dùng công thức =VALUE(A1) rồi định dạng xong chép lại rồi xóa cột phụ nhưng với năm sinh không có ngày tháng (1999 nó sẽ thành 21/06/1905, không ổn).
2-.Sau khi định dạng, tôi dùng Conditional Formating: từ 21/06/1905 thành 1999 bị báo lỗi.
Nhờ các bạn chỉ giúp thế nào để điều chỉnh danh sách này có ngày, tháng, năm sinh đúng quy định nhanh hơn.
Chân thành cám ơn.

 

[dat_butmuc]

Trong danh sách học sinh dự thi khá đông, do các đơn vị gởi danh sách đến không đúng quy đinh (ngày sinh của HS phải đủ 8 chữ số ngoại trừ trường hợp không có ngày, tháng sinh). Để điều chỉnh tôi có 2 cách suy nghĩ.
1-.Tạo cột phụ dùng công thức =VALUE(A1) rồi định dạng xong chép lại rồi xóa cột phụ nhưng với năm sinh không có ngày tháng (1999 nó sẽ thành 21/06/1905, không ổn).
2-.Sau khi định dạng, tôi dùng Conditional Formating: từ 21/06/1905 thành 1999 bị báo lỗi.
Nhờ các bạn chỉ giúp thế nào để điều chỉnh danh sách này có ngày, tháng, năm sinh đúng quy định nhanh hơn.
Chân thành cám ơn.

Không biết dữ liệu của bạn có nhiều và khác gì nữa không, chứ nếu chỉ có vậy thì thử xem cái này sao nhé? (Vd: 1999 thì cho là 01/01/1999)

=IF(LEN(A1)=4,DATE(A1,1,1),DATE(YEAR(A1),MONTH(A1),DAY(A1)))

Đúng ý không bạn?
P/s: Nhớ mặc định lại ngày tháng năm sau khi dùng công thức,...

 

[ndu96081631]

Không biết dữ liệu của bạn có nhiều và khác gì nữa không, chứ nếu chỉ có vậy thì thử xem cái này sao nhé? (Vd: 1999 thì cho là 01/01/1999)

=IF(LEN(A1)=4,DATE(A1,1,1),DATE(YEAR(A1),MONTH(A1),DAY(A1)))

Đúng ý không bạn?
P/s: Nhớ mặc định lại ngày tháng năm sau khi dùng công thức,...

Cũng may là hiện tại chắc chắn hổng có học sinh nào sinh vào khoảng 26/09/1902 đến 17/05/1927 ---> Nếu không thì... Ẹc... Ẹc....

 

[dat_butmuc]

Cũng may là hiện tại chắc chắn hổng có học sinh nào sinh vào khoảng 26/09/1902 đến 17/05/1927 ---> Nếu không thì... Ẹc... Ẹc....

Cám ơn Bác, test lại thì thấy lỗi mà cũng chẳng biết tại sao?
Vì sao từ 26/09/1902 đến 17/05/1927 lại như vậy ha NDu?

 

[ptm0412]

Vì sao từ 26/09/1902 đến 17/05/1927 lại như vậy?

26/09/1902 = 1000
17/05/1927 = 9999

Đó là giới hạn dưới và trên của ngày tháng có giá trị theo Excel là 4 chữ số. Vậy theo hàm If, Len(A1) = 4 Sẽ có giá trị True.

 

[ptm0412]

Còn 1 chuyện nữa, mọi người xem thử:
1. Tại sao Date(1000, 1, 1) không phải 01/01/1000 mà lại là 01/01/2900?
2. Tình trạng này kéo dài đến giá trị ngày nào mới hết?

Chuyện này tương đối dễ.

 

[Lê Duy Thương]

Còn 1 chuyện nữa, mọi người xem thử:
1. Tại sao Date(1000, 1, 1) không phải 01/01/1000 mà lại là 01/01/2900?
2. Tình trạng này kéo dài đến giá trị ngày nào mới hết?

Chuyện này tương đối dễ.

hình như trong EXcL định dạng ngày bắt đầu từ 01/01/1900 trở đi. nên khi chúng ta goc năm 1000 thì máy nó cộng thêm 1000 năm nữa
có đúng không đại ca

 

[ptm0412]

hình như trong EXcL định dạng ngày bắt đầu từ 01/01/1900 trở đi. nên khi chúng ta goc năm 1000 thì máy nó cộng thêm 1000 năm nữa
có đúng không đại ca

Đúng rồi, nhưng 1000 + 1000 = có 2000 à. Anh Bill cộng tới 1900 lận.

Còn câu 2 nữa quý vị!

 

[havietchuong]

Còn việc của tôi, nhờ các bạn có ý kiến giúp. Với một danh sách vài 1000 học sinh thì làm thế nào để thay đổi được ngày 21/06/1905 trở về năm 1999.

 

[ptm0412]

Còn việc của tôi, nhờ các bạn có ý kiến giúp. Với một danh sách vài 1000 học sinh thì làm thế nào để thay đổi được ngày 21/06/1905 trở về năm 1999.

Bạn dùng công thức của Datbutmuc được mà?

 

[havietchuong]

Tôi đã dùng công thức của dat_butmuc thì kết quả như file đính kèm (tôi trích danh sách của 1 đơn vị).
Nhờ các bạn có ý kiến giúp để thế nào định dạng vẫn giữ được năm sinh là 1999.

 

[ndu96081631]

Còn 1 chuyện nữa, mọi người xem thử:
1. Tại sao Date(1000, 1, 1) không phải 01/01/1000 mà lại là 01/01/2900?
2. Tình trạng này kéo dài đến giá trị ngày nào mới hết?

Chuyện này tương đối dễ.

Thì xem trong Help ---> Bấm nút Fx, chọn hàm DATE rồi bấm vào chữ Help on this function
(Đương nhiên, cái gì thuộc về quy định thì bác Bill phải có hướng dẩn chứ)

For the 1900 date system

If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108).

If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, DATE(2008,1,2) returns January 2, 2008.

For the 1904 date system

If year is between 4 and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108).

If year is between 1904 and 9999 (inclusive), Excel uses that value as the year. For example, DATE(2008,1,2) returns January 2, 2008.

If year is less than 4 or is 10000 or greater or if year is between 1900 and 1903 (inclusive), Excel returns the #NUM! error value.

 

[dat_butmuc]

Tôi đã dùng công thức của dat_butmuc thì kết quả như file đính kèm (tôi trích danh sách của 1 đơn vị).
Nhờ các bạn có ý kiến giúp để thế nào định dạng vẫn giữ được năm sinh là 1999.

Tự nhiên lại thêm cái dấu "," ở phía sau 1999 nữa cơ à?
Thế thì đây:

=IF(LEN(SUBSTITUTE(D4,",",""))=4,DATE(SUBSTITUTE(D4,",",""),1,1),DATE(YEAR(D4),MONTH(D4),DAY(D4)))

Hoặc đánh dấu cột D, nhấn Ctrl+H -> Replace (mục Find what gõ dấu phẩy, mục Replace with để trống) sau đó Replace All ->Xong

 

[havietchuong]

Tôi đã xóa được các dấu phẩy (,) dấu chấm (.) -có một số đơn vị còn ghi cách nhau bởi dấu chấm nữa - Không có cách nào để có kết quả chỉ 1999 mà không là 01/01/1999.
Cám ơn.

 

[dat_butmuc]

Tôi đã xóa được các dấu phẩy (,) dấu chấm (.) -có một số đơn vị còn ghi cách nhau bởi dấu chấm nữa - Không có cách nào để có kết quả chỉ 1999 mà không là 01/01/1999.
Cám ơn.

Lúc đầu thì bạn,

điều chỉnh danh sách này có ngày, tháng, năm sinh đúng quy định

=> Vậy quy định này có khác

Không có cách nào để có kết quả chỉ 1999 mà không là 01/01/1999.

không?
Thế bây giờ dữ liệu của bạn như thế nào, gồm những cái gì bạn đưa hết lên để mọi người xử lý một lần luôn, thống nhất với 1999 là để vậy hay sao???
Bạn nói rõ từ đầu phải hay hơn không ?

 

[ndu96081631]

Tôi đã xóa được các dấu phẩy (,) dấu chấm (.) -có một số đơn vị còn ghi cách nhau bởi dấu chấm nữa - Không có cách nào để có kết quả chỉ 1999 mà không là 01/01/1999.
Cám ơn.

Nếu vậy có nghĩa là... ĐỂ NGUYÊN, khỏi công thức nào cả ---> MÈO VẪN HOÀN MÈO
-----------------------
Để khỏi mất công người trợ giúp, tôi nghĩ bạn nên ghi ra ở cột cạnh bên kết quả mà bạn cần (ở tất cả các trường hợp) ---> Chứ nói lòng vòng 1 hồi, có khi lại.. chẳng hiểu gì ráo

 

[havietchuong]

Ở bài số 1, tôi có ghi rõ "Trong danh sách học sinh dự thi khá đông, do các đơn vị gởi danh sách đến không đúng quy đinh (ngày sinh của HS phải đủ 8 chữ số ngoại trừ trường hợp không có ngày, tháng sinh).", các bạn xem chưa kĩ đấy thôi.
Sau đây là yêu cầu tôi ghi rõ trong file đính kèm, nhờ các bạn giúp vậy.

 

[ndu96081631]

Ở bài số 1, tôi có ghi rõ "Trong danh sách học sinh dự thi khá đông, do các đơn vị gởi danh sách đến không đúng quy đinh (ngày sinh của HS phải đủ 8 chữ số ngoại trừ trường hợp không có ngày, tháng sinh).", các bạn xem chưa kĩ đấy thôi.
Sau đây là yêu cầu tôi ghi rõ trong file đính kèm, nhờ các bạn giúp vậy.

Vì trong file của bạn lẩn lộn giữa dạng TEXT và DATE, vậy để chuyển sang DATE chuẩn, bạn nên làm như sau:
- Vào Control Panel\Regional Language Options xem thử DATE đang được định dạng kiểu gì ---> hãy chuyển nó sang dd/mm/yyyy
- Mở bảng tính của bạn ra, copy 1 cell rông rồi quét chọn toàn bộ cột Ngày sinh và PasteSpecial, check 2 mục Values và Add
- AutoFilter cột ngày sinh, bấm mũi tên xổ xuống, chọn Custom, chọn mục Is Greater than và gõ vào ô bên cạnh số 10000
- Quét chọn cột Ngày sinh, định dạng cho nó dạng dd/mm/yyyy
- Show All dữ liệu
-----------------------
Tôi chỉ cần vài thao tác thế là xong, khỏi công thức nào cả