Hỏi về cách chuyển ngày tháng năm (mm/dd/yyyy) từ dạng text sang dạng số (1 người xem)

[uyvienbotrai]

Do em dùng phần mềm Trích xuất dữ liệu ra được 1 file .TXT, em mở nó bằng Excel thì cột Thời gian có chỗ nó hiểu dạng số, có chỗ nó hiểu dạng TEXT. Các bác vui lòng chỉ giúp em cách chuyển các chỗ từ dạng text sang dạng số với ạ. Thanks các bác nhiều.
File mẫu:

 

[ndu96081631]

Do em dùng phần mềm Trích xuất dữ liệu ra được 1 file .TXT, em mở nó bằng Excel thì cột Thời gian có chỗ nó hiểu dạng số, có chỗ nó hiểu dạng TEXT. Các bác vui lòng chỉ giúp em cách chuyển các chỗ từ dạng text sang dạng số với ạ. Thanks các bác nhiều.
File mẫu:

Việc chuyển mấy text ấy thành Date không khó, vấn đề là tôi sợ Excel nó hiểu lầm
Trên hệ thống Windows đang thiết lập mm/dd/yyyy trong control panel thì chuổi 6/3/2013 sẽ được hiểu là ngày 3 tháng 6
Trên hệ thống Windows đang thiết lập dd/mm/yyyy trong control panel thì chuổi 6/3/2013 sẽ được hiểu là ngày 6 tháng 3
Vậy tốt nhất bạn nên cho file TXT lên đây, mọi người xử lý từ file gốc luôn cho chắc ăn

 

[uyvienbotrai]

Em cảm ơn bác ndu96081631 nhiều ạ. Có lẽ bác hiểu sai ý của em. Em có 1 file text, giờ em chuyển sang excel, trong cột lastlogintimes có ô (cell) thì excel hiểu là định dạng số, có ô thì excel lại hiểu là dạng text. em muốn cho ất cả nó qua dạng số để em dễ báo cáo và sort ạ. Em xin up cả 2 file text và excel lên mong các bác xem xét giúp em.
File đk:

Trong file excel các bác xem dùm em. Giá trị ở ô C4 là dạng text, ở ô C7 là dạng date? E muốn chuyển C4 thành dạng giống C7 thì em phải làm sao hở các bác?

Phiền các bác nhiều quá, Thanks các bác trước ạ

 

[leonguyenz]

...
Trong file excel các bác xem dùm em. Giá trị ở ô C4 là dạng text, ở ô C7 là dạng date? E muốn chuyển C4 thành dạng giống C7 ...

Thử công thức này xem (hơi dài dòng một chút):

=IF(C4="","",IF(ISTEXT(C4),DATE(MID(C4,FIND("/",C4,FIND("/",C4)+1)+1,4),LEFT(C4,FIND("/",C4)-1),MID(C4,FIND("/",C4)+1,FIND("/",C4,FIND("/",C4)+1)-FIND("/",C4)-1)),DATE(YEAR(C4),MONTH(C4),DAY(C4))))

 

[ndu96081631]

Thử công thức này xem (hơi dài dòng một chút):

=IF(C4="","",IF(ISTEXT(C4),DATE(MID(C4,FIND("/",C4,FIND("/",C4)+1)+1,4),LEFT(C4,FIND("/",C4)-1),MID(C4,FIND("/",C4)+1,FIND("/",C4,FIND("/",C4)+1)-FIND("/",C4)-1)),DATE(YEAR(C4),MONTH(C4),DAY(C4))))

Cái file text, cột ngày tháng người ta đang viết theo chuẩn MM/dd/yy
Khi import vào Excel thì sẽ tùy theo cài đặt trong control panel mà kết quả sẽ là text hoặc date
Ví dụ:
- Có chuổi 3/2/2013 trong file text (là ngày 2 tháng 3 năm 2013, chuẩn MM/dd/yy)
- Import chuổi này vào Excel thì sẽ có 2 trường hợp xảy ra:

a) Control Panel đang thiết lập dd/MM/yy thì kết quả sẽ là 3/2/2013, chính là ngày 3 tháng 2 năm 2013
b) Control Panel đang thiết lập MM/dd/yy thì kết quả cũng là 3/2/2013, nhưng phải hiểu đây là ngày 2 tháng 3 năm 2013
​

Vậy không thể nhìn vào kết quả đã import trong Excel mà đoán ra điều gì cả ---> Phải làm trực tiếp trên file text mới xong!

 

[yeudoi]

Anh ndu nói đúng rồi đó tại phải biết trong file và trong control panel nó như thế nào đã

 

[leonguyenz]

Em không đoán mà dựa vào file Excel họ tải lên. Nhìn vào file có thể xác định là máy của người đã import file text sang Excel đã thiết lập ngày tháng trong hệ thống là trường hợp a) dd/MM/yy.
Nếu chỉ có file text thì đúng là phải tách ra 2 trường hợp như Thầy nói.

 

[ndu96081631]

Em không đoán mà dựa vào file Excel họ tải lên. Nhìn vào file có thể xác định là máy của người đã import file text sang Excel đã thiết lập ngày tháng trong hệ thống là trường hợp a) dd/MM/yy.
Nếu chỉ có file text thì đúng là phải tách ra 2 trường hợp như Thầy nói.

Trường hợp import vào Excel mà cột ngày tháng nó toàn là text thì khỏe rồi. Ở đây có vài cell nó trở thành Date luôn, trường hợp có sai sót thì rất nguy hiểm ---> Ai mà biết được 3/2 là ngày 3 hay ngày 2
Tóm lại: Bài này không thể xử lý trên file Excel được đâu, phải dùng code đọc file txt rồi xử lý trực tiếp trên đó ---> Trong file txt, ta đã biết chắc ngày tháng được sắp xếp theo thứ tự MM/dd/yyyy (tháng trước ngày sau). Vậy cứ theo đó mà làm... Còn cái chuyện nhìn vào dữ liệu trên Excel thì chẳng có đoán ra được cái gì đâu (nó đã bị biến đổi trong quá trình import)

 

[ndu96081631]

Mình xin nói thêm: Ai rảnh hãy nghiên cứu viết code import file txt ở bài 3 xem thử
Mình đã thử nhưng file txt này nó kỳ lạ thế nào ấy, viết code mãi vẫn không chạy được (hình như có vấn đề với ký tự xuống dòng thì phải)

 

[VetMini]

Trường hợp import vào Excel mà cột ngày tháng nó toàn là text thì khỏe rồi. Ở đây có vài cell nó trở thành Date luôn, trường hợp có sai sót thì rất nguy hiểm ---> Ai mà biết được 3/2 là ngày 3 hay ngày 2
Tóm lại: Bài này không thể xử lý trên file Excel được đâu, phải dùng code đọc file txt rồi xử lý trực tiếp trên đó ---> Trong file txt, ta đã biết chắc ngày tháng được sắp xếp theo thứ tự MM/dd/yyyy (tháng trước ngày sau). Vậy cứ theo đó mà làm... Còn cái chuyện nhìn vào dữ liệu trên Excel thì chẳng có đoán ra được cái gì đâu (nó đã bị biến đổi trong quá trình import)

Khi import dữ liệu từ text, Excel sẽ tự động dùng format "General". Với format này, những dữ liệu có mẫu mã giống Date sẽ bị đổi thành Date. Cái khổ ở đây là Exxcel hơi ngu lúc chuyển đổi. Nếu cái nào chuyển được thì nó chuyển, cái nào không đổi được thì nó giữ dạng Text. Cái khổ hơn nữa là file này chứa ngày tháng theo dạng US. Vì vậy "2/22/2013 8:31:47 AM" bị giữ là text (không dịch được tháng 22) trong khi "03/06/2013 9:59" được đổi thành Date

Có hai cách để xử lý:

1. Tạm dổi hệ thống thành US, chuyển đổi file. Xong đổi trở lại

2. Trong lúc chuyển bằng Text Import Wizard, chọn đổi cột này là "Text" thay vì "General". Đổi xong rồi thì dùng hàm đổi lại thành Date.

 

[ndu96081631]

Ah... ah... làm được rồi. Thì ra vấn đề nằm ở chổ file txt đang lưu theo chuẩn Unicode
Tác giả copy code dưới đây cho vào 1 Module trong file TotalSize.xls nhé:

Sub Main()
  Dim tmpArr, Arr(), Item
  Dim txtFile As String
  Dim i As Long, j As Long, n As Long
  Dim tmpDate As Double
  On Error Resume Next
  txtFile = ThisWorkbook.Path & "\TotalSize.txt"
  Range("A:D").Clear
  With CreateObject("Scripting.FileSystemObject")
    With .OpenTextFile(txtFile, 1, , -2)
      tmpArr = Split(Trim(.ReadAll), vbCrLf)
      ReDim Arr(1 To UBound(tmpArr) + 2, 1 To 4)
      For i = 0 To UBound(tmpArr)
        n = n + 1
        Item = Split(tmpArr(i), vbTab)
        For j = 0 To 4
          Arr(n, j + 1) = Replace(Item(j), """", "")
        Next
      Next
      .Close
    End With
  End With
  Range("A1").Resize(n, 4).Value = Arr
End Sub

Lưu ý:
- Đặt file TotalSize.xls và TotalSize.txt cùng 1 thư mục.
- Bấm Alt + F8, chạy code sẽ có két quả chính xác

 

[ndu96081631]

Khi import dữ liệu từ text, Excel sẽ tự động dùng format "General". Với format này, những dữ liệu có mẫu mã giống Date sẽ bị đổi thành Date. Cái khổ ở đây là Exxcel hơi ngu lúc chuyển đổi. Nếu cái nào chuyển được thì nó chuyển, cái nào không đổi được thì nó giữ dạng Text. Cái khổ hơn nữa là file này chứa ngày tháng theo dạng US. Vì vậy "2/22/2013 8:31:47 AM" bị giữ là text (không dịch được tháng 22) trong khi "03/06/2013 9:59" được đổi thành Date

Có hai cách để xử lý:

1. Tạm dổi hệ thống thành US, chuyển đổi file. Xong đổi trở lại

2. Trong lúc chuyển bằng Text Import Wizard, chọn đổi cột này là "Text" thay vì "General". Đổi xong rồi thì dùng hàm đổi lại thành Date.

Không cần phải đổi hệ thống gì đâu
Chuổi trong file TXT nếu bố trí theo thứ tự MM/dd/yy sẽ có cái lợi là khỏi cần phải xử lý gì cả ---> VBA nó tự hiểu luôn
(cũng giống như VBA luôn hiểu dấu thập phân là dấu chấm dù Control Panel thiết lập kiểu gì)

 

[uyvienbotrai]

Thanks các bác nhiều. Em xin ngàn lần cảm ơn các bác