Công thức chuyển đổi dạng text sang dang ngày tháng (1 người xem)

[phamhientb]

Mình muốn chuyển đổi dạng text: "Ngày 17 tháng 05 năm 2008" thành dạng ngày tháng là: 17/05/2008 thì dùng hàm gì? Bác nào biết chỉ cho mình với.
Thanks!

 

[huycuongxd]

Nếu là dạng text thì bạn chỉ việc tách chuỗi ra thôi. Giả sử chuỗi "Ngày 17 tháng 05 năm 2008" nằm ở ô A1 thì bạn nhập ở ô kết quả là =MID(A1,6,2)&"/"&MID(A1,15,2)&"/"&MID(A1,22,4)

 

[anhtuan1066]

Nếu là dạng text thì bạn chỉ việc tách chuỗi ra thôi. Giả sử chuỗi "Ngày 17 tháng 05 năm 2008" nằm ở ô A1 thì bạn nhập ở ô kết quả là =MID(A1,6,2)&"/"&MID(A1,15,2)&"/"&MID(A1,22,4)

Ngày tháng năm mà chuyển theo kiểu này thì làm sao mà tính toán (thôi thà để nguyên, chuyển chi cho mệt)
Dùng hàm DATE các đồng chí à!

 

[huycuongxd]

Ngày tháng năm mà chuyển theo kiểu này thì làm sao mà tính toán (thôi thà để nguyên, chuyển chi cho mệt)
Dùng hàm DATE các đồng chí à!

E nghĩ tại ô kết quả nếu được định dạng là kiểu DATE thì kết quả đó vẫn tính toán được chứ? E dùng hàm DATE nhưng ko được, có gì bác cứ chỉ dạy cho e sáng mắt...Cảm ơn bác.

 

[anhtuan1066]

E nghĩ tại ô kết quả nếu được định dạng là kiểu DATE thì kết quả đó vẫn tính toán được chứ? E dùng hàm DATE nhưng ko được, có gì bác cứ chỉ dạy cho e sáng mắt...Cảm ơn bác.

Bạn cứ thử xem công thức của bạn có tính dc ko thì biết liền! (thử trên máy tính có định dạng trong Control Panel là mm/dd/yyyy ấy)
Lý ra công thức phải là:

=DATE(MID(A1,22,4),MID(A1,15,2),MID(A1,6,2))

và cái này luôn đúng với mọi setting trong Control Panel

 

[BNTT]

Bạn cứ thử xem công thức của bạn có tính dc ko thì biết liền! (thử trên máy tính có định dạng trong Control Panel là mm/dd/yyyy ấy)
Lý ra công thức phải là:

=DATE(MID(A1,22,4),MID(A1,15,2),MID(A1,6,2))

và cái này luôn đúng với mọi setting trong Control Panel

Nhưng không luôn đúng với mọi font chữ.
Ví dụ, dùng font VNI, gõ cái "ngày tháng năm" í vào A1, thử xem công thức trên ra cái gì?

 

[anhtuan1066]

Nhưng không luôn đúng với mọi font chữ.
Ví dụ, dùng font VNI, gõ cái "ngày tháng năm" í vào A1, thử xem công thức trên ra cái gì?

He... he... Bác cứ nói thế!
Đương nhiên là phải tùy thuộc vào dử liệu chứ!
Tôi chỉ sợ họ gõ vầy mới chết nè:
Ngày 5 tháng 8 năm 07
Lúc đó ngày và tháng có thể là 1 hoặc 2 chử số, còn năm thì 2 chử số... He... he... công thức mệt luôn

 

[BNTT]

Tôi dùng cách dài dòng hơn, nhưng đúng với mọi loại font nhập vào:

Trước hết, phải phân tích cái ngày tháng năm.

Giả sử tôi có cái này: "Ngày 17 tháng 6 năm 2008" đang ở ô A1.

Bạn xem nhé, cho dù dùng font chữ gì và kiểu gõ nào, 4 số cuối cùng luôn là năm. Vậy, để trích ra năm, tôi dùng = RIGHT(A1,4)

Tiếp nha. Cuối cùng của chữ tháng là ký tự "g", vậy ta tìm vị trí xuất hiện của ký tự "g" này rồi cộng thêm 2, sẽ ra vị trí của phần tháng:

Để tìm vị trí của ký tự "g" này, tôi dùng công thức: =SEARCH("g", A1, 3)

Ở đây, tôi dùng SEARCH() mà không dùng FIND(), để đề phòng trường hợp có người gõ toàn là chữ hoa (NGÀY 17 THÁNG 6...), và bắt đầu tìm từ vị trí thứ 3 trong A1 để né ký tự "g" của chữ "Ngày".

Và để lấy ra số tháng, tôi dùng hàm: =MID(A1, SEARCH("g", A1, 3) + 2, 2)

Tương tự, để tìm ra số ngày, tôi tìm vị trí của ký tự "y" (chỉ có duy nhất 1 cái), cộng thêm 2, và lồng trong hàm MID như sau: =MID(A1, SEARCH("y", A1) + 2, 2)

Cuối cùng, tôi dùng 3 thành phần năm, tháng, ngày đã trích ra được đó làm tham số cho hàm DATE():

=DATE(RIGHT(A1, 4), MID(A1, SEARCH("g", A1, 3) + 2, 2), MID(A1, SEARCH("y", A1) + 2, 2))​

Các bạn thử lại xem có đúng không nha.

Tôi viết bài này, bởi, không phải ai cũng xài UNICODE, mà ở ngoài Bắc thì hay dùng TCVN3, trong Nam thì hay dùng VNI...

Chỉ yêu cầu duy nhất: Số năm phải dùng 4 chữ số.


--------------------------
Thêm tí, lỡ mà có ai đó gõ đến những 2 dấu cách giữa các giá trị ...
À, bạn cứ lồng thêm TRIM() vào là xong thôi.

 

[phamduylong]

He... he... Bác cứ nói thế!
Đương nhiên là phải tùy thuộc vào dử liệu chứ!
Tôi chỉ sợ họ gõ vầy mới chết nè:
Ngày 5 tháng 8 năm 07
Lúc đó ngày và tháng có thể là 1 hoặc 2 chử số, còn năm thì 2 chử số... He... he... công thức mệt luôn

Nếu dữ liệu đầu vào đa dạng thì dùng UDF dễ hơn. Hàm TextToDate này cho nhận dạng được text viết theo dạng:
- ngày, tháng 1 hoặc 2 số
- năm 1, 2 hoặc 4 số (nhận dạng theo quy định của Excel, Vd 1 > 2001, 97 > 1997)
- Thứ tự theo Việt Nam : ngày tháng năm.

Tôi thử nhận dạng được các trường hợp sau:
12/09/80 > 12/09/1980
ngày 12 tháng 3 năm 2008 > 12/03/2008
12 tháng 3 năm 09 > 12/03/2009
31 tháng 2 năm 1990 > #VALUE! (không có ngày 31 tháng 2)
28-2-97 > 28/02/1997
1-2-1 > 01/02/2001
1-2-11 > 01/02/2011
1-2-111 > ######## (năm nhập 3 số)
1-2-1800 > ####### (năm < 1900)
1-2-1987 > 01/02/1987
ngày 31/12/09 > 31/12/2009
ngày 31 tháng12/09 > 31/12/2009
ngày 31-12 năm 09 > 31/12/2009
ngày 31-12-09 > 31/12/2009
31/12 năm 1990 > 31/12/1990

 

[nêonguyn]

Anh Duy Long ơi!

Sao em vẫn làm giống như Anh, nhưng nó báo lỗi là #NAME?

 

[phamduylong]

Anh Duy Long ơi!

Sao em vẫn làm giống như Anh, nhưng nó báo lỗi là #NAME?

Làm giống như anh là làm thế nào ?
Hàm TextToDate viết trong file TextToDate.xls nên nó chỉ làm việc với các sheet của nó thôi. Nếu đã mở TextToDate, gõ đúng tên hàm mà báo lỗi NAME thì có khả năng Excel không cho macro hoạt động (Chọn Tools > Macro > Security > hạ cấp bảo vệ xuống Medium)
Muốn cho hàm TextToDate làm việc với tập tin Excel khác thì có 2 cách:
1. Ghi đầy đủ tên tập tin chứa hàm vào công thức =TextToDate.xls!texttodate(D1)
2. Lưu TextToDate.xls thành dạng *.xla

 

[hanghoanght]

Em cũng làm giống anh mà bị lỗi #name? nhấn F2 vào file của anh thì cũng bị lỗi tương tự.
Liệu công thức này có phải cài 1 add-in vào hệ thống trước khi sử dụng (giống như hàm đổi số thành chữ vntools) không ạ?
Nếu có thì anh giơi thiệu và hướng dẫn sử dụng luôn được không?
Cảm ơn anh đã chia sẻ!

 

[Jennifer Yang]

Dear a,
Anh cho e hỏi trường hợp trên nếu e muốn cài thêm chữ Ngày tháng năm vào thêm nhưng có 1 số trường hợp nó hiển thị đúng dạng date ko phải dạng text. Nếu định dạng đồng loại thì nó sẽ bị lỗi. Có cách nào khắc phục ko a.

 

[Jennifer Yang]

Hiện nay em có bài toán như sau, mong các anh giúp đỡ ạ:
Có 10 ô đựng bi, lần lượt thả từng viên bi vào, được thì tính 1 điểm, nếu trượt thì là 0 điểm. Trường hợp 3 ô liên tục bị 0 điểm thì các kết quả sau đều ko tính.
Ví dụ: 1 1 1 0 0 0 1 1 1 1 kết quả sẽ là 3.
Có công thức nào giải quyết nhanh ko ạ nếu trường hợp số lượng nhiều như 1000, 2000 ô....
Cám ơn anh trước ạ

 

[Jennifer Yang]

Do texttodate là anh long dùng VBA để viết code nên nếu bạn ko cài vào excel sẽ ko hiểu nhé

 

[vu_tuan_manh_linh]

Dear a,
Anh cho e hỏi trường hợp trên nếu e muốn cài thêm chữ Ngày tháng năm vào thêm nhưng có 1 số trường hợp nó hiển thị đúng dạng date ko phải dạng text. Nếu định dạng đồng loại thì nó sẽ bị lỗi. Có cách nào khắc phục ko a.

Tôi thì vẫn làm thế này!!
1. Chọn vùng dữ liệu ngày tháng, định dạng general.
2. Bấm tổ hợp phím Ctrl +F, sau đó tiến hành replace các ký tự "-" thành "/" và "/" thành chính nó.
Ngay cả trường hợp text 2016/08/03 cũng sẽ được chuyển thành 03/08/2016.
Lưu ý: Máy được định dạng hệ thống mặc định là dd/mm/yyyy

 

[vanle33]

Dear a,
Anh cho e hỏi trường hợp trên nếu e muốn cài thêm chữ Ngày tháng năm vào thêm nhưng có 1 số trường hợp nó hiển thị đúng dạng date ko phải dạng text. Nếu định dạng đồng loại thì nó sẽ bị lỗi. Có cách nào khắc phục ko a.View attachment 164347

1) Bài #15 bạn tự trả lời cho chính bạn hay sao ý nhỉ?
2) Với bài này: Bạn hay sử dụng định dạng ngày tháng yyyy/mm/dd ah? Nếu không bắt buộc dùng định dạng tôi vừa nói thì bạn chuyển định dạng Short date trong Control Panel là: dd/MM/yyyy rồi định dạng như sau trong cell: "Hà Nội, ngày "dd" tháng "mm" năm "yyyy là OK.

 

[Jennifer Yang]

Dear anh,
Bài toán bắt buộc định dạng yyyy/mm/dd nếu dạng khác thì ko thành vấn đề a nhé.
Tks a

 

[vanle33]

Dear anh,
Bài toán bắt buộc định dạng yyyy/mm/dd nếu dạng khác thì ko thành vấn đề a nhé.
Tks a

Tôi thử định dạng như đã nói ở #17 trên máy tính của tôi thì với ngày tháng năm dạng: 2011/08/16 thì sẽ cho kết quả : Hà Nội, ngày 16 tháng 08 năm 2011.
Không biết trên máy bạn thế nào?
p/s: Mà dear anh là anh nào thế?

 

[Jennifer Yang]

Không, vấn đề của mình là dạng hỗn hợp (có lúc text lúc là date) chứ ko phải dạng cụ thể.