Xin giúp chèn ký tự "/" vào dãy ký tự (1 người xem)

[quanloc]

Mình có 01 file dữ liệu ngày tháng năm, nhưng nó bị biến dạng thành như một dãy số liên tục . Ví dụ : 020307 - là ngày 02 tháng 03 năm 2007. Bây giờ mình muốn thêm ký tự "/" vào giữa thành : 02/03/07. Tôi có gởi file đính kèm.
Rất mong các bạn giúp đỡ tôi.
Xin chân thành cảm ơn các bạn.

 

[tedaynui]

Chào bạn
Bạn có thể dùng công thức này tại B2 và copy xuống
=IF(LEN(A2)=7,"0","")&LEFT(A2,2)&"/"&MID(A2,3,2)&"/"&RIGHT(A2,2)

TDN

 

[trandangkhoi]

Bó tay rồi. Bạn để dịnh dạng là số, bây giờ chuyển về ngày tháng thì chuyển thế nào được nữa.Bạn chỉ có cách chèn thêm ký tự vào thôi. Nhưng không dùng để tính toán được

 

[Mr Okebab]

Mình có 01 file dữ liệu ngày tháng năm, nhưng nó bị biến dạng thành như một dãy số liên tục . Ví dụ : 020307 - là ngày 02 tháng 03 năm 2007. Bây giờ mình muốn thêm ký tự "/" vào giữa thành : 02/03/07. Tôi có gởi file đính kèm.
Rất mong các bạn giúp đỡ tôi.
Xin chân thành cảm ơn các bạn.

Bạn xem nhé (có 2 TH đấy)

TH1 : Chỉ có các năm >=2000:

=DATE(RIGHT(A2;2)+2000;MID(A2;LEN(A2)-3;2);LEFT(A2;LEN(A2)-4))

TH 2: Nếu có các năm <2000:

=DATE(RIGHT(A2;2)+IF(VALUE(RIGHT(A2;2))<10;2000;1900);MID(A2;LEN(A2)-3;2);LEFT(A2;LEN(A2)-4))

Chào bạn
Bạn có thể dùng công thức này tại B2 và copy xuống
=IF(LEN(A2)=7,"0","")&LEFT(A2,2)&"/"&MID(A2,3,2)&"/"&RIGHT(A2,2)

TDN

Cái này sẽ cho ra ký tự, như vậy không tính toán được.

Thân!

 

[TranThanhPhong]

Bác xem thêm bài này nữa nè: (cập nhật)

=DATE(IF(VALUE(RIGHT(A2,2))<10,"20","19")&RIGHT(A2,2),MID(A2,IF(LEN(A2)<>6,2,3),2),LEFT(A2,IF(LEN(A2)<>6,1,2)))

TP.

 

[anhtuan1066]

Sao mà IF, với RIGHT, LEFT làm chi cho tôn công quá nhỉ... 1 hàm INT và 1 hàm DATE nữa là ra... chắc chắn nó là dạng số nên có thể dùng nó tính dc dễ dàng... Các bạn thử xem...
INT với số 10000 lấy ra dc ngày
INT với số 100 lấy ra dc tháng
Năm thì... quá dễ

 

[tuanhungnguyen]

• Tôi sẽ giúp bạn vì tôi mới lam cho trường của tôi có gì sai xin gọi số 0902088990 :

=concatenate(left(địa chỉ ô,2),"/",mid(địa chỉ ô,3,2),"/",right(địa chỉ ô,2))

Chúc bạn hạnh phúc !!!​

 

[anhtuan1066]

Bạn mà chèn mấy cái "/" này vào thì cell chỉ là TEXT thôi bạn à... Làm sao nó có thể là ngày tháng dc chứ

 

[Mr Okebab]

Sao mà IF, với RIGHT, LEFT làm chi cho tôn công quá nhỉ... 1 hàm INT và 1 hàm DATE nữa là ra... chắc chắn nó là dạng số nên có thể dùng nó tính dc dễ dàng... Các bạn thử xem...
INT với số 10000 lấy ra dc ngày
INT với số 100 lấy ra dc tháng
Năm thì... quá dễ

Cách của bác khá hay đó. Tuy nhiên phải nói rõ hơn:
VD : 80906
Tháng = int(80906;100)=809.
(như vậy phải thêm 1 hàm nữa bên trong hàm int phải không bác ??)

Năm cũng thế .

Thân!

 

[anhtuan1066]

Nói thế cho mọi người nghiên cứu chứ.. hi.. hi... Tóm lại tôi làm chỉ có 2 hàm: INT và DATE, vài phép công trừ nhân chia nữa là OK
Gợi ý chút cho mọi người nha:
Ngày = INT(A1/10000)
Tháng =INT((A1-B1*10000)/100)
Năm thì... tự suy nghĩ đi... he.. he.. Thế mới thú vị chứ

 

[quanloc]

chèn dấu cách

Rất cảm ơn bạn . Nhưng công thức của bạn nó sẽ sai khi dãy ký tự có 5 số : ví dụ : 90507 : ngày 9 tháng 05 năm 2007 nó lại thành 90/05/07. Do vậy những cel có ngày nhỏ hơn 10 thì đều bị sai.
Tuy nhiên cũng nhờ công thức của bạn mà mình phát triển ra công thức sau - Nó sẽ hoàn toàn đúng trong mọi trường hợp :
= IF(LEN(A2)=6,LEFT(A2,2)&"/"&MID(A2,3,2)&"/"&RIGHT(A2,2),LEFT(A2,1)&"/"&MID(A2,2,2)&"/"&RIGHT(A2,2)).
Rất cảm ơn sự hợp tác của bạn.

Chào bạn
Bạn có thể dùng công thức này tại B2 và copy xuống
=IF(LEN(A2)=7,"0","")&LEFT(A2,2)&"/"&MID(A2,3,2)&"/"&RIGHT(A2,2)

TDN

 

[quanloc]

chèn thêm dấu cách

Công thức của bạn nó làm sao ấy, mình không dám nói sai, nhưng mà không sử dụng để tính toán được. Mình thấy khi cộng trừ ngày tháng khác thì nó không cho kết quả đúng.
Cảm ơn bạn nhiều !

Bác xem thêm bài này nữa nè:
=DATE(RIGHT(A13,2),MID(A13,IF(LEN(A13)<>6,2,3),2),LEFT(A13,IF(LEN(A13)<>6,1,2)))

TP.

 

[quanloc]

Công thức của bạn cũng rất hay, nhưng cũng không đúng như bạn Tedaynui , nó cũng chỉ đúng đối với 6 ký tự, còn 5 ký tự là sai ngay.
Rất cảm ơn bạn, từ công thức này tôi cũng có thể làm được nhiều chuyện rồi đây.

• Tôi sẽ giúp bạn vì tôi mới lam cho trường của tôi có gì sai xin gọi số 0902088990 :

=concatenate(left(địa chỉ ô,2),"/",mid(địa chỉ ô,3,2),"/",right(địa chỉ ô,2))

Chúc bạn hạnh phúc !!!​

 

[quanloc]

chèn thêm dấu cách

Công thức của bạn cũng rất hay đó, nhưng chưa chắc gì đơn giản đâu, cần phải đảm bảo nó đúng trong mọi trường hợp : có 5 ký tự và có 6 ký tự. Nó cũng rất dễ sai giống như các bạn khác mà thôi.
Còn để cho nó đúng thì cũng phải if, cũng phải & không làm sao tránh khỏi.
Rất cảm ơn bạn .

Nói thế cho mọi người nghiên cứu chứ.. hi.. hi... Tóm lại tôi làm chỉ có 2 hàm: INT và DATE, vài phép công trừ nhân chia nữa là OK
Gợi ý chút cho mọi người nha:
Ngày = INT(A1/10000)
Tháng =INT((A1-B1*10000)/100)
Năm thì... tự suy nghĩ đi... he.. he.. Thế mới thú vị chứ

 

[anhtuan1066]

Đã bảo là dùng INT mà cứ ko nghe... cứ IF với LEFT hoài... Sai ráng chịu...
A1 là dử liệu---->
Ngày là B1 =INT(A1/10000)
Tháng là C1 =INT((A1-INT(A1/10000)*10000)/100)
Năm là D1 =A1-B1*10000-C1*100
Từ 3 dử liệu ngày tháng năm này, dùng hàm DATE đễ ráp lại thành ngày tháng năm hoàn chỉnh

 

[anhtuan1066]

Dù là 5 hay 6 ký tự gì thì cũng chắc chắn đúng... Nếu sai tôi xin chịu CHẶT ĐẦU... ha... ha... Chỉ yêu cầu đừng có cắc cớ nhập 4 ký tự thôi (như 6599---> ngày 6 tháng 5 năm 99 thì bó bột)
Thử xem

 

[ltmcustoms]

Xin Code trợ giúp nhập dữ liệu DD/MM/YY ?

Đã bảo là dùng INT mà cứ ko nghe...

A1 là dử liệu---->
Ngày là B1 =INT(A1/10000)
Tháng là C1 =INT((A1-INT(A1/10000)*10000)/100)
Năm là D1 =A1-B1*10000-C1*100
Từ 3 dử liệu ngày tháng năm này, dùng hàm DATE đễ ráp lại thành ngày tháng năm hoàn chỉnh

Chào bác Anhtuan1066 và toàn thể các pác!
Em đang rất cần công cụ làm được như trên, để hỗ trợ tốt hơn nhập liệu hàng ngày ! Bác giúp em Code mà khi người dùng tại các ô ở cột "Ngày tháng năm" chỉ cần nhập DDMMYY nhưng ra kết DD/MM/YY (Ko cần làm thêm cột phụ).
Cám ơn các bác !
LTMCustoms

 

[connhangheo]

Đã bảo là dùng INT mà cứ ko nghe... cứ IF với LEFT hoài... Sai ráng chịu...
A1 là dử liệu---->
Ngày là B1 =INT(A1/10000)
Tháng là C1 =INT((A1-INT(A1/10000)*10000)/100)
Năm là D1 =A1-B1*10000-C1*100
Từ 3 dử liệu ngày tháng năm này, dùng hàm DATE đễ ráp lại thành ngày tháng năm hoàn chỉnh

cách này em dùng với số 150507 thì B1 = 15,C1 = 5, D1= 7, nhưng sau khi dùng hàm date thì nó lại ra là 5/15/1907, mà nếu em gõ 1 ô là 5/22/07 thì nó ra đúng là 5/22/2007. Cho em hỏi tại sao lại thế ạ?

= IF(LEN(A2)=6,LEFT(A2,2)&"/"&MID(A2,3,2)&"/"&RIGHT(A2,2),LEFT(A2,1)&"/"&MID(A2,2,2)&"/"&RIGHT(A2,2)

dùng cách này của bác quanloc thì em lại không tính toán được bác ah
Mong các bác chỉ giáo

 

[anhtuan1066]

Mở 1 file mới, right click trên sheet tab, chọn View code... Paste code này vào:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim StrVal As String
Dim dDate As Date
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub

With Target
StrVal = Format(.Text, "000000")
If IsNumeric(StrVal) And Len(StrVal) = 6 Then
Application.EnableEvents = False

If Application.International(xlDateOrder) = 1 Then 'dd/mm/yy
dDate = DateValue(Left(StrVal, 2) & "/" & Mid(StrVal, 3, 2) & "/" & Right(StrVal, 2))
Else 'mm/dd/yy
dDate = DateValue(Mid(StrVal, 3, 2) & "/" & Left(StrVal, 2) & "/" & Right(StrVal, 2))
End If

.NumberFormat = "dd/mm/yyyy"
.Value = CDate(DateSerial(Year(dDate), Month(dDate), Day(dDate)))

End If
End With

Application.EnableEvents = True
End Sub

Đóng VBE lại và thử nhập 010105 vào xem...
Chú ý: Vùng hoạt động của macro là A1:A100 và Format Date là dd/mm/yyyy... Cái này bạn có thể sửa lại theo ý mình
Mến
ANH TUẤN

 

[anhtuan1066]

Với bạn Connhangheo: Thứ nhất, máy tính bạn đang định dang mm/dd/yy, cái này là tùy chọn có thể sửa dc... Thứ 2: Do Windows lấy móc thời gian từ 1 thời điểm nào đó, cụ thể trên máy tính của tôi là từ năm 1930... Điều này có nghĩa là nếu nhập 2 số cuối cùng < 30 thì nó sẽ hiểu năm là 20XX, ngược lai, 2 số cuối >= 30 thì nó sẽ hiểu năm là 19XX ... Có thể sửa lại ở cell D1 cho chính xác như sau:
D1= =IF(A1-B1*10000-C1*100<30,A1-B1*10000-C1*100+2000,A1-B1*10000-C1*100)
Mến
ANH TUẤN

 

[trung_dang]

Vấn đề này đơn giản thôi mà. Nếu mục đích thực sự chỉ chèn "/" vào thôi thì quá đơn giản hãy dùng công thức =LEFT(RIGHT(A2,6),2) & "/" & LEFT(RIGHT(A2,4),2) & "/" &RIGHT(A2,2). Còn sử dụng mục đích khác thì hồi sau sẽ phân giải

 

[duongsatdn]

Dù là 5 hay 6 ký tự gì thì cũng chắc chắn đúng... Nếu sai tôi xin chịu CHẶT ĐẦU... ha... ha... Chỉ yêu cầu đừng có cắc cớ nhập 4 ký tự thôi (như 6599---> ngày 6 tháng 5 năm 99 thì bó bột)
Thử xem

Với bạn Connhangheo: Thứ nhất, máy tính bạn đang định dang mm/dd/yy, cái này là tùy chọn có thể sửa dc... Thứ 2: Do Windows lấy móc thời gian từ 1 thời điểm nào đó, cụ thể trên máy tính của tôi là từ năm 1930... Điều này có nghĩa là nếu nhập 2 số cuối cùng < 30 thì nó sẽ hiểu năm là 20XX, ngược lai, 2 số cuối >= 30 thì nó sẽ hiểu năm là 19XX ... Có thể sửa lại ở cell D1 cho chính xác như sau:
D1= =IF(A1-B1*10000-C1*100<30,A1-B1*10000-C1*100+2000,A1-B1*10000-C1*100)

anhtuan ơi bây giờ mà sai thì mới chịu CHẶT ĐẦU. Hì... hì... Cảm ơn. Thân.

 

[anhtuan1066]

Sao lại sai chứ? Dù bạn làm bằng hàm date thì cũng thế thôi... Thử đi: DATE(07,05,15) là = mấy?

 

[ltmcustoms]

Code này thật kỳ diệu -"Không nhập (/) mà có kết quả như ý!"

Mở 1 file mới, right click trên sheet tab, chọn View code... Paste code này vào:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim StrVal As String
Dim dDate As Date
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub

With Target
StrVal = Format(.Text, "000000")
If IsNumeric(StrVal) And Len(StrVal) = 6 Then
Application.EnableEvents = False

If Application.International(xlDateOrder) = 1 Then 'dd/mm/yy
dDate = DateValue(Left(StrVal, 2) & "/" & Mid(StrVal, 3, 2) & "/" & Right(StrVal, 2))
Else 'mm/dd/yy
dDate = DateValue(Mid(StrVal, 3, 2) & "/" & Left(StrVal, 2) & "/" & Right(StrVal, 2))
End If

.NumberFormat = "dd/mm/yyyy"
.Value = CDate(DateSerial(Year(dDate), Month(dDate), Day(dDate)))

End If
End With

Application.EnableEvents = True
End Sub

Đóng VBE lại và thử nhập 010105 vào xem...
Chú ý: Vùng hoạt động của macro là A1:A100 và Format Date là dd/mm/yyyy... Cái này bạn có thể sửa lại theo ý mình
Mến
ANH TUẤN

Dear Mr Tuấn !
Code thật kỳ diệu -"Không nhập (/) mà có kết quả như ý!"
Em gửi file đính kèm - nhờ sư phụ Anhtuan1066 sửa lại chút và chỉ giáo!
Đa tạ!
Thân!
LTMCustoms

 

[connhangheo]

Sao lại sai chứ? Dù bạn làm bằng hàm date thì cũng thế thôi... Thử đi: DATE(07,05,15) là = mấy?

máy của em ra là 5/15/1907
còn nếu em gõ 5/15/07 thì nó sẽ hiện ra là 5/15/2007
Vấn đề em hỏi là tại sao em tự gõ thì ra năm 2007, còn dùng hàm date(07,05,15) hoặc lấy year là 1 ô tham chiếu nào đó thì lại ra là năm 1907.
Mong các bác giải thích giúp

 

[anhtuan1066]

Với connhangheo: Điều này tôi cũng ko biết, nhưng bạn cứ tự nghĩ đi.. Nếu bạn là.."Excel" thì bạn sẽ hiểu số 07 là cái gì? Là 1907 hay 2007?... Đó là do cách viết tắc của chúng ta, đáng lý ra phải gõ 2007 thì chúng ta lại gõ 07... Vậy nếu máy có hiểu sai thì là lỗi của chúng ta chứ ko phải do máy... bạn đồng ý về điều này chứ...
Với hàm DATE này, nếu bạn muốn ra 2007 thì có 2 cách: hoặc là ghi rõ 2007 hoặc là ghi 107 cũng dc
Mến

 

[anhtuan1066]

Bạn có thể thử với file này nha! Cho phép bạn nhập tùy ý từ 4 số đến 8 số...
Mến
ANH TUẤN

 

[ltmcustoms]

Code nhập "giùm" dấu cách ngày/tháng/năm !

Dear Mr Tuấn !
Nhận đc hồi âm của bác, em rất vui - đặc biệt là vào dịp "pà con cả nước đang đi chơi ...", nhân đây em tỏ lòng cám ơn và kích chúc bác cùng những người thân và các thành viên của diễn đàn mạnh khỏe, may măn và thành đạt!
Còn về em đang test File "NhapDate" thấy vẫn còn mấy vấn đề nhỏ nhưng rất khó (Liên quan đến ngày tháng mà !), cụ thể như sau ạ:
- Nhập những ngày từ 20->31 trong tháng không được!? Chẳng lẽ đối với các ngày này (nhều wa!) lại nhập thủ công thì cực wa giời!
- Chương trình cho phép người sử dụng nhập tùy ý từ 4 số đến 8 số rất tiện vì đỡ công quy ước, hướng dẫn với trường hợp nhiều người đổi ca sử dụng! nhưng lỗi: nếu nhập (dạng 8 số) lại 1 ngày mà bên trên đã nhập (dạng 6 số) thì báo lỗi "#######- Notvalid date" - nhập 6 số thì OK, đặc biệt là sau khi xuất hiện MsgBox rồi thì nhập một số ngày hựp lệ khác lại ####### hoặc bị quy đổi ngày khác làm em fát hoảng?!
- Liệu sau khi nhập xong 1 ô, cho ra kết quả dạng ngày d/m/yyyy hoặc dd/mm/YYYY không ạ !? Trước em có diễn đạt ko rõ! hi hi (Cquan em Win "buộc phải" là m/d/yyyy (theo Tây) còn trong Excell em muốn là theo Ta !
- Trong 1 Sheet em cần can thiệp vào 2 cột ko liền kề, Code của bác em chỉ biết thay là Range("A1:C100") và MsgBox là rất hay nhưng khi nhập dữ liệu cột B thì cực wa !
* Các vấn đề trên theo em có thể là do hiệu ứng thông minh của Excel !? Liệu có các nào khi nhập liệu ở các vùng này thì Disable (tắt) hiệu ứng này đi đc ko các bác nhỉ!?
Hiện tại em mới chỉ phát hiện được như vậy! hy vọng đưa vào thực tế chỉ thế thôi nhỉ, không thì tốn sức các bác quá!. Khả năng của em hiện tại thì còn mầm non wa! nhất là vấn đề "dữ liệu thời gian" - lỗi nhỏ nhưng nhiều khi "bó bột". Dữ liệu này ai cũng gặp phải nhưng chỗ em thì 1 thay nhau ngồi gõ hết ngày này sang năm khác! Mong bác Tuấn và diễn đàn quan tâm giúp em!
Em chỉ biết, đang ngồi chờ kết quả!
Chào các bác !
#########
LTMCustoms

 

[anhtuan1066]

Chào bạn!
Hôm nay ko biết đi chơi ở đâu... Hic... tình cờ lên mạng lại lượm dc 1 bí kíp nữa vả Quick Dates Entry... Bạn tải file về và đưa vào Add-Ins nhé! Tôi mới thử sơ sơ, thấy hay lắm đấy... Nó cho phép bạn chọn vùng hoạt đông ngay ngoài bảng tính mà ko cần vào VBE để sửa gì cả...
Bạn cứ thử đi, có gì hay thì chia sẽ với mọi người nhé! (tôi ko có tg đễ thứ.. hi.. hi..)
Ah... Ko biết thì mở hướng dẩn ra xem... cũng dễ hiểu lắm
ANH TUẤN

 

[anhtuan1066]

Dựa vào thuật toán INT, tôi nhờ thầy Phước xây dựng code như sau:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim r As Integer
Dim d, m, y
If Target.Column = 1 And Target.Row < 100 Then
r = Target.Row
y = Int(Target / 10000)
m = Int((Target - y * 10000) / 100)
If (Target - y * 10000 - m * 100) < 30 Then
d = Target - y * 10000 - m * 100 + 2000
Else
d = Target - y * 10000 - m * 100
End If
Target = DateValue(y & "/" & m & "/" & d)
End If
End Sub

Code này khỏi sợ lỗi, vì nếu nhập sai thì nó sẽ ko làm gì cả...
Mến
ANH TUẤN

 

[thanhthuy.doan]

tính tiền lương tháng 13

công ty e nếu làm một tháng đủ 15 ngày 11.5 tiếng thì sẽ được tính thưởng 1 tháng( e sẽ viết trong excel là 15/11.5) vậy thì dùng công thứ như thế nào để có thher đếm dó là được 1 tháng, nếu 15/11.3 hoặc 14/11.5 là không được
e viết là =if(F1<=15/11.5;1;0) sao nó không đúng. nhờ các anh chị giúp e với,

 

[bebo021999]

công ty e nếu làm một tháng đủ 15 ngày 11.5 tiếng thì sẽ được tính thưởng 1 tháng( e sẽ viết trong excel là 15/11.5) vậy thì dùng công thứ như thế nào để có thher đếm dó là được 1 tháng, nếu 15/11.3 hoặc 14/11.5 là không được
e viết là =if(F1<=15/11.5;1;0) sao nó không đúng. nhờ các anh chị giúp e với,

Có vẻ như bạn đang muốn quy ra tổng số giờ làm việc trong 1 tháng?
Nếu F1 là tổng giờ thực tế,

=IF(F1>=172.5,1,0)

172.5 = 15*11.5=tổng số giờ theo quy định 1 tháng

 

[thanhthuy.doan]

nhưng trong F1 e vẫn đánh là 15/11.5 ( hoặc 14/11.5, 15/11) không qui ra 172.5.e lập công thức xong thì khi F1 e nhập 14/11.5 ( chi có 161h)nó vẫn tính được 1, nếu đúng nó phải trả về 0 chứ, mong giải giúp.

 

[bebo021999]

nhưng trong F1 e vẫn đánh là 15/11.5 ( hoặc 14/11.5, 15/11) không qui ra 172.5.e lập công thức xong thì khi F1 e nhập 14/11.5 ( chi có 161h)nó vẫn tính được 1, nếu đúng nó phải trả về 0 chứ, mong giải giúp.

Chưa hiểu, bạn gửi file lên đi