Problem with date formatting

[Healthwatch]

I have created a userform to enter specific data for a project, one being the date. I have confirmed that the date is valid by using the IsDate function however when i transfer this information onto a worksheet the date changes ie date entered as 08/02/2007 in userform but on worksheet it changes to 02/08/2007.

What can i do to rectify this, as it has serious implications as the project calculates drug dosages for children.

hope to hear from someone very soon

[Leith Ross]

Hello Healthwatch,

You need to override the default cell formatting. You can do this in code by changing the Cell's NumberFormat property. Here is an example here the date is has been transfered from TextBox1 on the userform, to cell A2 on worksheet Sheet1.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Healthwatch]

Thanks Leith Ross for the quick response but i'm not sure where to put in the code.
I have therefore supplied a copy of the code used to transfer information from userform to the worksheets.
please let me know how to proceed, many thanks

Please Login or Register  to view this content.

[Leith Ross]

Hello Healthwatch,

I have added the format changes. They are in blue.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Healthwatch]

Thanks again for the quick response to this problem,

i will be trying your advice ASAP

[Healthwatch]

I tried your suggestion unfortunately it would not work, kept on giving me a run-time error '1004'; with the message

Unabe to set NumberFormat Property of the range class

Any suggestions?

[Healthwatch]

stopped getting the run-time error however still getting the dates transposed on the worksheet.

when debugging information correctly stored however when checked on the worksheet its wrong.

This is driving me nuts, please help

[Excelhilator]

I think there´s a little typo

Please Login or Register  to view this content.

[Healthwatch]

the problem ocurs on all worksheets and i'm at a loss as to why.

i enter for example the date 08/02/2004 in the userform and when this is moved to the worksheets it still formats as 02/08/2004.
i've tried setting the properties of the cells on each worksheet but to no avail. i've tried the suggestions made by leith ross again no joy

however if the date is 24/03/2004 then it enters this correctly into the worksheet

Its driving me crazy, why wont it just the the text as i've typed it in????

[Excelhilator]

Can you post the workbook?

[Leith Ross]

Hello Healthwatch,

I may not have understood how you wanted your date format. The program, as is, sets the date order to day/month/year. If you want it as month/day/year, change the number format codes to "mm/dd/yyyy".

Sincerely,
Leith Ross

[Healthwatch]

Thanks for all the help, have now sorted the problem by setting the properties to text. Still not sure why excel continues to invert the day and month when propeties set to date, but at least it works now