"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.

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.

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

Why "of course" and "ALWAYS" - simply signifies slack or simpleton
programming to me!

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

As you will have determined, my opinion is that all indiscriminate date
casting whether explicit or implicit should consider locale. If as
programmer you find a reason to force a non locale cast, then you should
code it distinctly such as DateSerial() offers.

Sub tst()

Thanks for the tute but I have already BTDT - I was only looking for a way
to ensure locale processing always occurred. It is no big drama to me that
Excel is not up to it. As it happens I already have a proprietary IsDate()
function to catch other quirks in Excel like IsDate("5,011,554").

Cheers, Frank.

the only quirk is YOU seem determined to demonstrate
that if you cast a LOCALE date string to a cells's value
you run the risk of excel interpretation.

To avoid this behaviour:

cast a date as a DOUBLE or DATE to value or value2
=no interpretation


cast a locale STRING to formulaLOCAL
it will be interpreted against locale !

Sub DateCasting()
Dim d As Date
d = DateSerial(2003, 2, 1)

ActiveCell.NumberFormat = "general"
ActiveCell.FormulaLocal = Format(d, "d/m/yy")


MsgBox "entered as DMY string" & vbNewLine & _
"system uses " & IIf(Application.International(xlMDY), "MDY", "DMY") & _
vbNewLine & "result:" & (ActiveCell.Value = d)

End Sub





keepITcool

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


"Frank_Hamersley" wrote:


Thanks for the tute but I have already BTDT - I was only looking for a
way to ensure locale processing always occurred. It is no big drama
to me that Excel is not up to it. As it happens I already have a
proprietary IsDate() function to catch other quirks in Excel like
IsDate("5,011,554").

Cheers, Frank.

"keepITcool" wrote
the only quirk is YOU seem determined to demonstrate
that if you cast a LOCALE date string to a cells's value
you run the risk of excel interpretation.

Reread the post and then explain why IsDate("5,011,554") returns TRUE - that
is the question (apols to the Bard).

Frank.

the isdate function's description from vba help..
Returns a Boolean value indicating whether an expression
can be converted to a date.

the crux is in the word CAN..

I do admit that vba or excel's interpretation is somewhat loose.
plus there are some differences between VBA and Excel functions.

I ran a few loops (swapping system separators)
and determined that vba's string interpretation is based on ALL
of the following user's locale settings...

listseparator,decimalseparator,dateseparator,hours eparator
(optionally including the am pm indicators..)

funny as you'd expect it to be based on either
locale's or usenglish Date separator.

you may be interested in :
http://support.microsoft.com/default...;EN-US;Q214233

also see harlan grove's article "an interesting read" in
mp.excel.worksheetfunctions:
. com




keepITcool

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


"Frank_Hamersley" wrote:

"keepITcool" wrote
the only quirk is YOU seem determined to demonstrate
that if you cast a LOCALE date string to a cells's value you run the
risk of excel interpretation.

Reread the post and then explain why IsDate("5,011,554") returns TRUE
- that is the question (apols to the Bard).

Frank.

"keepITcool" wrote
the isdate function's description from vba help..
Returns a Boolean value indicating whether an expression
can be converted to a date.
the crux is in the word CAN..

....excepting in this case it can't be displayed (visually) as a date -
although it does not cause an exception so I guess that meets the CAN
criteria!

plus there are some differences between VBA and Excel functions.

Understood - however it was the VBA giving me the heartburn.

I ran a few loops (swapping system separators)
and determined that vba's string interpretation is based on ALL
of the following user's locale settings...
listseparator,decimalseparator,dateseparator,hours eparator
(optionally including the am pm indicators..)

Yuk!

funny as you'd expect it to be based on either
locale's or usenglish Date separator.

I agree. It looks like an each way bet being placed - I presume somehow in
response to something like (as I understand them) the European formats for
dates like yyyy.mm.dd and decimal numbers like 999.999.999,99 ie. the same
char being used in different contexts.

The Q214233 article mentions in respect of dates
a.. If a number contains a slash mark (/) or hyphen (-), it may be
converted to a date format.
It isn't nearly as comprehensive as your research shows it needs to be in
respect of commas etc!

also see harlan grove's article "an interesting read"

It was very interesting! As it happens I have a B.Sc majoring in Zoology
and the risk this trait poses for genetic analysis is not lost on me! I'll
bet the smart alec's that though they would help us all out by casting
"likely" dates etc didn't see this one coming! Do you know if there is any
way to disable this "feature" without having to go to the lengths of
preformatting the cell?

Cheers
Frank.