Xử lý dữ liệu có dấu phân cách / (dạng date)

[Thuyan.acc]

Em có 1 file dũ liệu cột a1 đến aZ như sau :
030107 (Ngày 01 tháng 01 năm 2007)
.......
......
311207 (Ngày 31/12/07)

Em muốn chèn dấu / giữa các con số 03 có dấu / và 01 có dấu / Vậy em phải làm sao ?

Ngày hôm qua mới bị quê quá, họ đưa em 1 file số liệu bằng *.dbf như em trình bày trên rồi họ bảo em làm báo cáo cho họ. Nhưng phải có chèn dấu phân cách / như em nói. Ngồi xem dữ liệu em lúng túng không lẻ mỗi cell em lại dùng F2 lên rồi lại gõ vào các dấu / này. Quá chậm !!! Trong khi công việc cần gấp, em mới dùng hàm left(A1,2) rồi kết hợp hàm mid và right nhưng làm không được.Em không hiểu sao file dbf của họ dưa cho kiểu dữ liệu ngày tháng gì lạ đời thế. Không rõ export ra thế nào mà mình mất công phải gia công lại số liệu của họ.

Thấy vậy người bạn mách cho em một chiêu như sau :
Giả sử A1 ta có số liệu 030107 ở cột B1 cài =left(A1,2) & "/01/07" thì kết quả như sau 03/01/07. Sau đó em copy B1....BZ rồi paste special vào cột A lại. Biết rằng cột A em định dạng dd/mm/yy
Trường hợp xử lý cách này cũng may số liệu nó chỉ có tháng 01 năm 2007 thôi còn nếu số liệu có nhiều tháng nhiều năm thì làm sao ?

Kính vui lòng Thầy/Cô cùng anh chỉ hướng dẫn cho.

Thanks

 

[viendo]

Em muốn chèn dấu / giữa các con số 03 có dấu / và 01 có dấu / Vậy em phải làm sao ?

Bạn dùng công thức này xem

B1=DATE(20&RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2))

 

[SA_DQ]

Thêm 1 tham khảo

=DATEVALUE(LEFT(B3;2)&"/"&MID(B3;3;2)&"/"&RIGHT(B3;2))

 

[anhtuan1066]

Nhất định ko dc dùng các hàm xử lý chuổi cho hàm DATE, vì sẽ gây sai lầm đáng tiếc.. điều này tôi đã từng nói trên diển đàn rồi...
Dử liệu của bạn nếu chắc chắc có 6 ký tự thì nên dùng hàm INT và MOD để tách số trước, sau đó dùng hàm DATE đễ ráp lại..
Đây là gợi ý:

=DATE(MOD(A1,100)+2000,INT(MOD(A1,10000)/100),INT(A1/10000))

Tất nhiên bạn còn phải làm thêm 1 chuyện nữa cho những năm <2000.. tự nghiên cứu nhé!
Công thức này luôn đúng với mọi dử liệu gốc là Text hay number
---------------------
Ai da.. sửa luôn nhé... Ta cứ quy định rằng 2 số sau cùng có giá trị từ 30 đến 99 thì xem là năm 19xx, còn ngược lại thì xem là năm 20xx
Vậy có công thức sau:

=DATE((MOD($A1,100)<30)*100+MOD($A1,100),INT(MOD($A1,10000)/100),INT($A1/10000))

Lưu ý: nếu A1 dc format là TEXT thì khi nhập 030508 sẽ ko có vấn đề, nhưng nếu A1 dc Format là number mà nhập 030508 thì nó sẽ ra 30508 và các hàm LEFT, RIGHT, MID sẽ tiêu ngay... Còn dùng INT và MOD thì chả có vấn đề gì, 030508 hay 30508 vẫn như nhau... he.. he...
Thử xem!
ANH TUẤN

 

[Thuyan.acc]

Kính nhờ Quý Thầy xem lại công thức

Em có thử cài công thức theo Quý Thầy chỉ dẫn.
Em gởi file lên để Quý Thầy xem lại nhe.
Em xin cám ơn Quý Thầy đã chỉ dẫn cho em.

 

[viendo]

Em có thử cài công thức theo Quý Thầy chỉ dẫn.
Em gởi file lên để Quý Thầy xem lại nhe.
Em xin cám ơn Quý Thầy đã chỉ dẫn cho em.

Chào bạn, nghe bạn gọi bằng Thầy tôi thấy thế nào ấy, vì nghề của tôi không phải là Giáo viên bạn ơi.
Tôi đã xem file bạn đưa lên tôi thấy mấy điểm sau:

1. Công thức ở cột A bạn phải tham chiếu đến cột E chứ không phải cột F nhé. Công thức đúng ở cột A :
   

   A2=DATE(20&RIGHT(E2,2),MID(E2,3,2),LEFT(E2,2))

2. Công thức ở cột B cũng bị tham chiếu sai đến cột F. Công thức đúng cột B là :
   

   B2=DATEVALUE(LEFT(E2,2)&"/"&MID(E2,3,2)&"/"&RIGHT(E2,2))

3. Công thức ở cột F bị sai tham chiếu ở phần Day.
   

   F2=DATE((MOD(E2,100)<30)*100+MOD(E2,100),INT(MOD(E2,10000)/100),INT(E21/10000)))

   E21 là sai, phải là E2
4. Bạn sửa lại chắc chắn với số liệu của bạn các công thức sẽ đúng với yêu cầu, vì công thức cột A và C sẽ đúng trong khoảng thời gian từ năm 2000 đến năm 2099, công thức cột B và F đúng với thời gian từ 1930 đến 2029.
5. Nên dùng công thức cột C hoặc F vì :
   

   Lưu ý: nếu A1 dc format là TEXT thì khi nhập 030508 sẽ ko có vấn đề, nhưng nếu A1 dc Format là number mà nhập 030508 thì nó sẽ ra 30508 và các hàm LEFT, RIGHT, MID sẽ tiêu ngay... Còn dùng INT và MOD thì chả có vấn đề gì, 030508 hay 30508 vẫn như nhau... he.. he...
   Thử xem!
   ANH TUẤN

 

[Thuyan.acc]

:
Công thức ở cột F bị sai tham chiếu ở phần Day.
E21 là sai, phải là E2

Vậy là do lỗi của em. Em đã sửa lại tham số E2 như sau : INT($E2/10000 rồi OK ->Chỗ sai INT($E22/10000)).

=DATE((MOD($E2,100)<30)*100+MOD($E2,100),INT(MOD($E2,10000)/100),INT($E2/10000)) -> OK

Cám ơn Thầy.

 

[Thuyan.acc]

Tôi đã xem file bạn đưa lên tôi thấy mấy điểm sau:

1. Công thức ở cột A bạn phải tham chiếu đến cột E chứ không phải cột F nhé. Công thức đúng ở cột A :

Phần công thức của Thầy viendo, em có sửa lại và cho kết quả đúng như sau

code
=DATE(20&RIGHT(F2,2),MID(F2,3,2),LEFT(F2,2)) -> Tham số do em sử dụng bị sai

=DATE(20&RIGHT(E2,2),MID(E2,3,2),LEFT(E2,2)) -> Tham số đúng

Cho em xin hỏi Thầy viendo, phần code của Thầy

DATE(20

Thầy ghi có số 20 ở đây công dụng của nó để làm gì vậy.

Phần code của Thầy SA-DQ
B2=DATEVALUE(LEFT(E2;2)&"/"&MID(E2;3;2)&"/"&RIGHT(E2;2)) nhưng máy báo lỗi The formula you typed contains error - Em không hiểu sao ???

Cám ơn Thầy Anh Tuấn, viendo,SA-DQ.

 

[SA_DQ]

Phần code của Thầy SA-DQ
B2=DATEVALUE(LEFT(E2;2)&"/"&MID(E2;3;2)&"/"&RIGHT(E2;2)) nhưng máy báo lỗi The formula you typed contains error - Em không hiểu sao ???
Cám ơn Thầy Anh Tuấn, viendo,SA-DQ.

Bạn ThuyAn phải tìm hiểu các hàm đã nêu thêm nữa;
Xin nêu 1 số í về hàm =DATEVALUE()
* Hàm này sẽ biến 1 chuỗi chữ dạng ngày sang 1 ngày nào đó cụ thể;
VD: ta có chuỗi '10/10/10' ; Khi đó =DATEVALUE("10/10/10") hàm sẽ trả về là ngày 10/10/2010;
Nếu chuỗi là '10/13/10' ; Kết quả sẽ là ngày 13/10/2010 (hệ Pháp) - có nghĩa là #10/13/2010# ( Hệ Mẽo);
Chú í con số 13 nha!

Vì có sự khác biệt trong cách thức biểu thị ngày của 2 hệ; nên khi dùng công thức (hàm) này cần biết chuỗi đó là:
1*/ StrC= 'DDMMYY' => =DATEVALUE(MID(StrC;3;2)&"/"&LEFT(StrC;2)&"/"&RIGHT(StrC;2))
2*/ StrC= 'MMDDYY' => =DATEVALUE(LEFT(StrC;2)&"/"&MID(StrC;3;2)&"/"&RIGHT(StrC;2))

Gọi mình là 'Sư Chính' được rồi; Mình chỉ là thầy Lười thôi!

Chúc Vui Nhân Dịp Xuân Về!​

 

[anhtuan1066]

Anh Sa ơi... Hàm DATEVALUE sẽ bị sai trong 1 số trường hợp nào đó.. chẳng hạn trên hệ thống dùng định dạng m/d/y mà ta lại có DATEVALUE("15/05/07") thì nó sẽ chẳng hiểu gì cã... cái này giống y chang như gõ tháng trước hay ngày trước tùy thuộc vào định dạng của RLO trên máy tính vậy!
Với bạn Thuyan.acc: Hàm DATE(20&... ) tất nhiên là đễ thêm số 20 vào... ví dụ: 150508 khi dùng RIGHT đễ tách 2 số cuối ta dc số 08, thêm 20 vào thì dc 2008... đơn giãn vậy thôi
ANH TUẤN

 

[salam]

Hàm datevalue phụ thuộc vào ngày hệ thống nếu ngày hệ thống định dạng là dd/mm/yyyy thì theo file của Thuyan ta dùng hàm lần lượt left,mid,right còn định dạng là mm/dd/yyyy thì ta dùng là mid, left, right.

 

[Thuyan.acc]

Em xin xóa file trước đây em đã gởi. Và em upload lại file mới sau khi được Thầy Anh Tuấn cùng Thầy viendo đã chỉ cái lỗi của em do cài sai tham số (bài số 7 và 8).

Kính

 

[viendo]

1. Ở công thức cột B bạn sửa dấu ; (chấm phẩy) thành dấu , (dấu phẩy)
2. Bỏ dấu ' trước dấu =
3. Kéo công thức xuống.
4. Định dạng lại là dd/MM/yy

 

[Thuyan.acc]

1. Ở công thức cột B bạn sửa dấu ; (chấm phẩy) thành dấu , (dấu phẩy)
2. Bỏ dấu ' trước dấu =
3. Kéo công thức xuống.
4. Định dạng lại là dd/MM/yy

Dạ em đã làm được. Cám ơn Quý Thầy nhiều

 

[nguyenmanhnam]

Em xin hỏi có cách nào khi người đánh 1102 nó hiểu là 01/01/2011 không?

(Theo cách của sư phụ Anhtuan1066 nó ra là 31/10/2002).

 

[trungvdb]

Thì bạn thêm mấy cái If vào là xong chứ có gì đâu

=DATE((MOD($A1,100)<30)*100+MOD($A1,100),IF(LEN(A1)>=5,INT(MOD($A1,10000)/100),INT(MOD($A1,1000)/100)),IF(LEN(A1)>=5,INT($A1/10000),INT($A1/1000)))

-----------
Công thức sau

IF(LEN(A1)>=5,DATE((MOD($A1,100)<30)*100+MOD($A1,100),INT(MOD($A1,10000)/100),INT($A1/10000)),DATE((MOD($A1,100)<30)*100+MOD($A1,100),INT(MOD($A1,1000)/100),INT($A1/1000)))

trông có vẻ dài hơn, nhưng rất có thể tốc độ nhanh hơn (do không phải kiểm tra 2 lần Len, đó là do mình suy luận vậy không biết có đúng không?)

 

[nguyenducthanh.bacninh]

Bạn dùng công thức này xem nhé: B1=LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2)