Date Format - Please Help!

[Aviles]

Hi all,
I'm currently using a user form with current "UserForm_Activate" settings as follows:

Private Sub UserForm_Activate()
txtDate.Text = Format(Now(), "dd/mm/yyyy")

Up to this point I'm ok, as the date on the user form defaults to dd/mm/yyyy (e.g. 28/02/2011).

I also have the below code for when the user clicks on the "Enter" button on the user form, which transfers the date from the form to the spreadsheet:

Sheets("PrimeCo Home").Select
Range("W1").Select       
 
Selection.Value = txtGMT.Value
Selection.Offset(1, 0).Value = txtDate.Value
Selection.Offset(2, 0).Value = txtClientName.Value

The date does get transfered to the spreadsheet, but in the mm/dd/yyyy format (e.g. 02/28/2011).

I've changed the format under "Format Cells", both under the "Date" and "Custom" categories... with no luck.

I have even added code which should change the format to dd/mm/yyyy:

Range("W2").Select
Selection.NumberFormat = "dd/mm/yyyy"

My computer's settings are also set up with the dd/mm/yyy format.

Can any one give me other suggestions?

Thanks in advance.

 

[Blade Hunter]

Click the column then under data click Text To Columns, click next and choose the date format it currently it there.

Then once you click finish you can reformat the column how you want it and it will work.

 

[Aviles]

Click the column then under data click Text To Columns, click next and choose the date format it currently it there.

Then once you click finish you can reformat the column how you want it and it will work.

I clicked on cell "W2" which is the cell that the date gets incorrectly transferred to, and when I click on "Text to Columns" it give me the error message:

"No data was selected to parse"

Any suggestions?

 

[Blade Hunter]

Click the whole column, not just a cell.

 

[Aviles]

Click the whole column, not just a cell.

Selected the whole column and still getting the same error message.

 

[Blade Hunter]

That's extremely odd. I don't know why it is doing that.

 

[Dryver14]

I've been following on with this for a while now and have searched several threads and I too am struggling with any suggestions

Is it worth removing this line from the macro and formatting W1 direct

eg =NOW() on the cell itself

 

[Aviles]

I ended up using the following formula that I got from another thread:

=IF(DAY(W2)<=12,DATE(YEAR(W2),DAY(W2),MONTH(W2)),W2)

Probably not the cleanest way to do it, but it works fine now.

 

[Rasm]

Try something like this

Range("C3").Value = Format(TextBox.Text, "dd/mm/yyyy")

 

[Aviles]

Thanks Rasm, will give this a try.