VBA Date formatting problem, please help

[Data Head]

Hi Folks,

I'm trying to format a cell to show a date as 04 Jan 2009

..so I setup a subroutine as...
-----------------------
Columns("A:A").Select
Selection.NumberFormat = "dd mmm yyyy"
Cells(1, 1).Value = "04/01/2009" ' yes, my PC is set to English settings!
------------------------
...BUT, when it runs, the cell entry appears as 01 Apr 2009

If I type 04/01/2009 directly into the cell on the worksheet, then press return, it displays the date as 04 Jan 2009...which is what I wanted!!!

Please help, it's driving me crazy!

DH

 

[Mike Blackman]

Hi and welcome to the board,

Try entering it into the VBE in MDY Format or alternatively try;

Cells(1, 1) = Format("04/01/2009", "dd mmm yy")

 

[c_m]

Try:
Selection.NumberFormat = "mm/dd/yyyy"

 

[Andrew Poulsom]

VBA always uses US style dates when coercing a string, but its DateValue function is locale aware:

Cells(1, 1).Value = DateValue("04/01/2009")

 

[Data Head]

Much thanks folks!

Andrew's Cells(1, 1).Value = DateValue("04/01/2009") is the more universal method ( I don't know what the user's PC setup is), so I'll use that.

Thanks again!!!
DH