VBA interprets date strings using US english conventions if possible.

Cdate(recombine)

would intepret the string using regional settings.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
On Feb 13, 10:19 am, "Bob Phillips" wrote:
Maybe

With ThisWorkbook.Sheets("Sheet2").Cells(2, 16)
.NumberFormat = "dd/mm/yy"
.Value = recombine
End With

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

ups.com...



I have a problem displaying date.
Situation: worksheet has a column which is displaying date wrongly.
For example it is displaying year/month/date. When it displays date it
is adding either 20 or 30 with the date. ie 06/09/2029. Here 2029
should be 29 as a day.
At any rate, I have written a macro which parses the cell and chop off
the 20 part, and stores the value as 06/09/29.
Problem: when I try to display the value back into sheet, it changes
the format to 09/06/2029, instead of displaying 06/09/29. Can anyone
help ?
My code:(nothing wrong with it, works fine, but cant display the value
back to worksheet)
Sub formatDate()

hold = ThisWorkbook.Sheets("Sheet2").Cells(2, 13).Value
Debug.Print hold
hold_values = Split(hold, "/")
recombine = ""
For i = 0 To 2 ' format correction

If i = 2 Then
chop = Right(hold_values(i), 2)
Debug.Print chop
recombine = recombine + chop
Else
Debug.Print hold_values(i)
recombine = recombine + hold_values(i) + "/"

End If

Next i

ThisWorkbook.Sheets("Sheet2").Cells(2, 16).Value = recombine
Debug.Print recombine ' displays the result as required ie YY/MM/DD

End Sub- Hide quoted text -

- Show quoted text -

I am not sure if it is right, but i have tried this:
With ThisWorkbook.Sheets("Sheet2").Cells(2, 16)
.NumberFormat = "mm/dd/yy"
.Value = recombine
End With
I changed numberFormat to mm/dd/yy from dd/mm/yy
It is working fine, can you explain that ?