Shoobert
01-07-2008, 02:52 PM
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
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