Pb with date format when using .Value=(Value,X)

[vadius]

Hi there,

I have two sheets and I am trying to extract the date value from one sheet to another with the below code. The problem I have is that the date I want to extract are written as a text in the cells with the following format & sentence :

"SMI® PR as of 01-07-2010"
"SMI® PR as of 02-07-2010"

etc for each day

So I computed the below code that extract the 10 right letters to get "01-07-2010". The problem I have is that the value I get in the second sheet is not a text any longer : vba/excel converts automatically into a date format dd/mm/yy , but the format is wrong. Instead of having 1st of July, 2nd of July etc.. I have 7th of January, 7th of February etc...

I tried to change the column format with mm-dd-yy but it does not work.

Have you guys any ideas on how I could get the extract values in the right date format dd-mm-yy ? I need then to sort the data in my extract by dates..

Thanks for your help

=====================================================

Sub copydate()

Dim strValue As String
Dim strCellNum As String
Dim x As String
x = 2
For i = 2 To 6000 Step 26
strCellNum = "A" & i
strValue = Worksheets("Extract").Range(strCellNum).Value
Debug.Print strValue
Worksheets("Clean").Range("A" & x).Value = Right(strValue, 10)
x = x + 1
Next

End Sub

 

[GlennUK]

Use a non-ambiguous entry:

Worksheets("Clean").Range("A" & x).Value = format(CDate(Right(strValue, 10)),"dd/mmm/yy")

 

[vadius]

Thanks it does work perfectly.
I tried somethong like .Value = Format (Right (... but I forgott the "CDate"

Anyway thanks a lot

 

[GlennUK]

That's great! Glad to help.