Frank

you draw some quick conclusions.
not willing to learn or study.?? <vbg

ofcourse inserting #1/2/2003# produces Jan2nd.
#xxxxx# denotes VBA date.. which as i mentioned is ALWAYS US format)



but inserting DateValue("01-02-03") will insert Feb 01
because datevalue will translate string to a date against locale
just as format will produces a string from date

or use dateserial..

just and please read help on date conversions...
a small demo..

Sub tst()
'hardcoded date
MsgBox "JAN 2nd: " & Format(#1/2/2003#, "dd-mmm-yyyy")


MsgBox "use datevalue to get a LOCALE date from string" & _
vbNewLine & Format(DateValue("01/02/03"), "dd-mmm-yyyy")

'note the output is unformatted...
MsgBox "or use dateserial with year,month,day integers" & _
vbNewLine & DateSerial(2003, 2, 1)

'lets combine it and format the output
MsgBox "FEB 1st: " & Format(DateValue("01-02-03"), "dd-mmm-yyyy") & _
vbNewLine & FormatDateTime(DateSerial(2003, 2, 1), vbLongDate)

End Sub




once you've a valid date (can be a double or a date data type)
THEN use value2 to assign to excel cell.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Frank_Hamersley" wrote:

"keepITcool" wrote in message
vba uses USamerican internally

Par for the course I guess!

using a range's value2 property to insert dates will solve your
uncertainty..

Didn't seem to change the outcome at all. Note that I am casting from
a String rather than Date.

ActiveCell.Value2 = "1/2/2003" produces 02-Jan-2003
ActiveCell.Value2 = #1/2/2003# produces 37623 which formats as
02-Jan-2003

as it will insert the value as a number rather than an (interpreted)
date

Looks like I will have to keep parsing the string and casting it
myself!

Thanks for the input.
Frank.