i found the problem, the string "30/12/2005 " have a space which is
causing it to return
year 1905 instead of 2005.

solved the problem

On May 30, 11:55 am, kiwis wrote:
Thank you everyone for their reply,

NickHK - i have tried to set the format to what i want but to no
sucess, so i have to write code to split up the
date & then regroup into what i want mm/dd/yyyy. The orginal date was
copied from a raw CSV file which is having dd/mm/yyyy format.

Tom - thanks, the code work but the year is giving me 1903 instead of
2003. when i type the formula into the cell, i get the correct year
2003 but when i run the macro AA, the year becomes 1903, why is that
so? any answer to why the year is wrong when using the marco but
correct when i type into the cell?

Thank you

On May 29, 8:46 pm, Tom Ogilvy
wrote:



Sub AA()
Dim ws As Worksheet
Dim ilastrow As Long
Dim i As Long
Set ws = ActiveSheet
ilastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row

For i = 2 To ilastrow
With ws.Cells(i, "B")
.Offset(0, 1).Formula = "=IF(ISTEXT(" & _
.Address & "),DATE(RIGHT(" & _
.Address & ",4),MID(" & .Address & ",SEARCH(""/""," & _
.Address & ")+1,SEARCH(""/"", MID(" & .Address & _
",SEARCH(""/""," & .Address & ")+1,10))-1),LEFT(" & _
.Address & ",SEARCH(""/""," & .Address & _
")-1))," & .Address & ")"
End With
Next i

End Sub

produced the same formula you show in your example. (it places the formula
in column C in the corresponding row. )

To do that, you need to use Address instead of Value. Plus the suggestions
of the other posters.

--
Regards,
Tom Ogilvy'

"kiwis" wrote:
Hi Ben

i have tried your suggestion

..Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value,
SEARCH("" / "",.value)+1,SEARCH("" / "", MID(.value, SEARCH("" /
"",.value)+1, 10))-1),LEFT(.value, SEARCH("" / "",.value)-1)),.value)"

it is still giving me error,
application defined error or object defined error.

Anyone can help?

Thanks

rgds
kiwis

On May 29, 3:23 pm, Ben McBen
wrote:
I think your issue is quotes - if you want to use quotes within a quoted
string, then you have to use two quotes, eg:

"my string ""with quoted subsstring"""

"kiwis" wrote:
Hi

Need some help with my code,

My code will change the date (dd/mm/yyyy) to this format mm/dd/yyyy
format.

part of my code

iLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row

For i = 2 To iLastRow
With ws.Cells(i, "B")

.Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2),
MID(.value, SEARCH(" / ",.value)+1,SEARCH(" / ", MID(.value,
SEARCH(" / ",.value)+1, 10))-1),LEFT(.value, SEARCH(" /
",.value)-1)),.value)"

End With
Next i

i can do it in excel the formula
= IF(ISTEXT(B4), DATE(RIGHT(B4, 2),MID(B4,
SEARCH("/",B4)+1,SEARCH("/", MID(B4, SEARCH("/",B4)+1,
10))-1),LEFT(B4, SEARCH("/",B4)-1)),B4)

but when i put it in a macro, i get error "Type mismatch"

how do i set it such that it will loop through all the cells from in
the for loop?

Thank you
I have send a few days trying to fix this date problem

rgds
kiwis- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -