String to Date problem with VBA / Excel

[Shoobert]

Hi Gurus,

I have a user form in excel which includes a date field. The format
required for users is to input is dd/mm/yyyy. I am storing the input from the
userform in an array.

When VBA writes the date from the form into the worksheet, I use the following code:

Cells(lrownum, 3).Value = txtDateUserForm.Text

However, excel reports the date (when it recognises it) as mm/dd/yyyy, where mm is actually the day entered in the form and dd is actually the month entered in the form. Therefore 4/01/2008 comes back (incorrectly) as the 1st of April, 2008 whereas if i enter it directly in excel it would interpret that (correctly) as the 4th of January, 2008.

If it is not a recognisable date then it will output the dd/mm/yyyy date
but in a text format. I have checked the computer's regional settings
which are set to dd/mm/yyyy format.

It is weird to me because one line of code has the following in it:

DateEntered = Date 'generate today's date for the form
txtDateUserForm.Text = DateEntered

...and that again comes back in the format i want, eg 4/01/2008, but i send it back to excel and it gets its own date backwards!? :banghead:

Does anybody know why it defaults to the mm/dd/yyyy format in vba? Does the "Value" property have a default date format? Is it possible to output the date correctly?

Any help would be much appreciated.

Steve

[Bob Phillips]

Try



Cells(lrownum, 3).Value = CDate(txtDateUserForm.Text)

[Shoobert]

oh thanks guru... you're a legend!:clap:

[Bob Phillips]

Been there, got the t-shirt (many of them) :-).

[jack nt]

Hi Gurus,

I have a user form in excel which includes a date field. The format
required for users is to input is dd/mm/yyyy. I am storing the input from the
userform in an array.

When VBA writes the date from the form into the worksheet, I use the following code:

Cells(lrownum, 3).Value = txtDateUserForm.Text

However, excel reports the date (when it recognises it) as mm/dd/yyyy, where mm is actually the day entered in the form and dd is actually the month entered in the form. Therefore 4/01/2008 comes back (incorrectly) as the 1st of April, 2008 whereas if i enter it directly in excel it would interpret that (correctly) as the 4th of January, 2008.

If it is not a recognisable date then it will output the dd/mm/yyyy date
but in a text format. I have checked the computer's regional settings
which are set to dd/mm/yyyy format.

It is weird to me because one line of code has the following in it:

DateEntered = Date 'generate today's date for the form
txtDateUserForm.Text = DateEntered

...and that again comes back in the format i want, eg 4/01/2008, but i send it back to excel and it gets its own date backwards!? :banghead:

Does anybody know why it defaults to the mm/dd/yyyy format in vba? Does the "Value" property have a default date format? Is it possible to output the date correctly?

Any help would be much appreciated.

Steve

Hi Steve!
It becauses that the sourse data is TEXT (you got them from yourUserForm.yourTextBox.Text).

I also meet the same problem.
I often get data from file.xls done by the others. In case control panel in user's computer of "dd/mm/yyyy", it is OK. But if "mm/dd/yyyy" the problem occurs. Because sometime instead of "DATE" data they use TEXT data like '04/12/2010
I solved as follows:

Function correctDay(Target as Range) As Date
If Target.Formula = Target.Value Then
correctDay = Day(Target) & "-" & Month(Target) & "-" & Year(Target)
Else
correctDay = Target
End if
End Function

Jack NT