View Full Version : date help

07-30-2012, 06:47 AM
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
07-30-2012, 07:13 AM
Texbox value is a string. Use CDate() to convert it to a date value. Use the Range's NumberFormat. e.g

01-03-2013, 12:36 PM
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
01-03-2013, 01:16 PM
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.

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, "/")
StrToDate = DateSerial(2000 + d(2), d(1), d(0))
End Function

Bob Phillips
01-03-2013, 03:44 PM

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