Excel Reformatting dates 1

[StewartJ]

I am using Excel 97 SR-2 on Windows NT4 SP6.

System regional settings are English (UK) with short date format of dd/MM/yy and long date format of dd MMMM yyyy.

My VB code is :

ActiveCell.NumberFormat = "dd/mm/yyyy"
ActiveCell.Value = "01/02/2003"

This displays as 02/01/2003

but

ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Value = "01/02/2003"

This displays as 01/02/2003

ie, in either case, Excel insists on inverting my date. I get the same effect changing the cell format through menu commands.

!!!!!!!!!

Could somebody please explain?

 

[xlbo]

This is why, when inserting dates, you should use the serial number of the date

ActiveCell.NumberFormat = "dd/mm/yyyy"
ActiveCell.Value = 37653

The problem is caused within VBA rather than excel itself. The default date format in VBA is mm/dd/yy. so when you create the string "01/02/2003" VBA thinks that it is in mm/dd/yy format and so tries to convert to dd/mm/yy to enter into the worksheet Rgds
~Geoff~

 

[StewartJ]

Many thanks. Isn't Microsoft wonderful?!

 

[xlbo]

It is for Americans amigo ;-) Rgds
~Geoff~

 

[RobBroekhuis]

If you don't care for figuring out date serial numbers, you can use a different unambiguous presentation, such as:
activecell="2 Nov 1999" or activecell="Nov 2, 1999"
Both will be properly interpreted.
Rob