date help

[ashgull80]

hi
i have a text box in a userform that i input a date.
02/01/2012 = 02/jan/2012
when it inputs this data into a cell on a sheet it either changes the date to 01/feb/2012 or it just copys it as 02/01/2012 whereas i have set the cell to change it to 02/jan/2012 format.
why is it doing this?

thanks ash

[Kenneth Hobs]

Texbox value is a string. Use CDate() to convert it to a date value. Use the Range's NumberFormat. e.g
[VBA]Range("A1").NumberFormat="dd/mmm/yyyy"[/VBA]

[ashgull80]

hi
i cant get this to work it still changes the date around
so if i put in the form 01/02/12 it copies it to the worksheet as 02 January 2012

[Kenneth Hobs]

First off, textbox values for dates is very problematic. There are several methods that are more reliable though some may seem complicated to you.

At best, CDate() is going to convert a known string date based on your computer's Regional settings.

Howsoever, if you are married to the textbox method, explore the two methods shown here.

[vba]Private Sub CommandButton1_Click()
With Worksheets("Sheet1").Range("A1")
.NumberFormat = "dd/mmm/yyyy"
.Value = CDate(TextBox1.Value)
End With

With Worksheets("Sheet1").Range("B1")
.NumberFormat = "dd/mmm/yyyy"
.Value = StrToDate(TextBox1.Value)
End With
End Sub

Private Function StrToDate(dtString As String) As Date
Dim d() As String
d() = Split(dtString, "/")
'YMD
StrToDate = DateSerial(2000 + d(2), d(1), d(0))
End Function[/vba]

[Bob Phillips]

Ash,

As Kenneth says, you want to cast the text value using CDate ... but haven't we told you that before?