Date Formatting Bug

U

UberSchnell

Hi

The problem is regarding populating a cell with a date, from a textbox in a user form. I find that when populating dates (from a user form into a cell in Excel), the date format reverts to the American style e.g. mm/dd/yy. This occurs even when I specify date format as dd/mm/yy in the code AND on the spreadsheet. I have done a number of tests and found the problem to arises when the dd is 01, 02, 03.... up to 12. Therefore if the dd is up to 12, it reverts to the American format as it thinks it's a month. If the dd is 13 and over, it recognises that it can't be American and formats correctly to dd/mm/yy

Being in Australia DD/MM/YY is the preferred date format

Any suggestions why this may be happening and what could be done to fix it would be greatly appreciated.
 
N

Nigel

Try this link to reveal how Excel deals with dates, the userform issue is a
known feature(!) -

http://www.cpearson.com/excel/datetime.htm

Cheers
Nigel

UberSchnell said:
Hi,

The problem is regarding populating a cell with a date, from a textbox in
Click to expand...
a user form. I find that when populating dates (from a user form into a cell
in Excel), the date format reverts to the American style e.g. mm/dd/yy. This
occurs even when I specify date format as dd/mm/yy in the code AND on the
spreadsheet. I have done a number of tests and found the problem to arises
when the dd is 01, 02, 03.... up to 12. Therefore if the dd is up to 12, it
reverts to the American format as it thinks it's a month. If the dd is 13
and over, it recognises that it can't be American and formats correctly to
dd/mm/yy.
Being in Australia DD/MM/YY is the preferred date format.

Any suggestions why this may be happening and what could be done to fix it
Click to expand...
would be greatly appreciated.
 
U

UberSchnell

Do you have any references that specifically discuss the UserForm issue?

Thanks

Timshel
 
Top