VBA Setting .Value to a date does not respect local system setting

[Frank_Hamersley]

I have found that using a macro to set a cell value to a date does not
generate the expected result. Consider ...

Sub mcrSetDate()
ActiveCell.Value = "1/2/2003"
ActiveCell.NumberFormat = "dd mmm yyyy"
End Sub

This results in "02 Jan 2003". However if I enter "1/2/2003" into a cell
directly and format it I get "01 Feb 2003". Clearly VBA is not observing
the system date/time format which is set to Australian. (ie. dd/mm/yy) but
it is clearly parsing the .Value to a date serial.

Changing the statement to the following corrects the problem...

ActiveCell.Value = DateValue("1/2/2003")

However this means because my macro is dealing with data of unknown type
(from a text file) I have to inspect every value to decide if it is a date
so I can cast it before assigning it to the .Value.

Does anyone know of a cleaner way to get around this problem. The sheet
involved is always new and has never had any formatting aplied by the time
the data is being placed on it.

Cheers, Frank.

 

[keepITcool]

vba uses USamerican internally
in the VBE see what happens if you type:
d1=#1-2-2003# will convert to #2/1/2003#

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

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


Sub DoDate()
Dim d1 As Date, d2 As Date
'Jan 1st
d1 = #1/2/2003#
'Feb 2nd
d2 = DateValue("1-2-2003")
With ActiveCell
.Cells(1).Value2 = d1
.Cells(2).Value2 = d2
.Resize(2).NumberFormat = "dd mmm yyyy"
End With



keepITcool

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

 

[William]

Hi Frank

Try using "DateSerial" [ DateSerial(Year, Month, Day) ]


Sub mcrSetDate()
ActiveCell.Value = DateSerial(2003, 2, 1)
ActiveCell.NumberFormat = "dd mmm yyyy"
End Sub

which results in 01 Feb 2003

--
XL2002
Regards

William

(e-mail address removed)

| I have found that using a macro to set a cell value to a date does not
| generate the expected result. Consider ...
|
| Sub mcrSetDate()
| ActiveCell.Value = "1/2/2003"
| ActiveCell.NumberFormat = "dd mmm yyyy"
| End Sub
|
| This results in "02 Jan 2003". However if I enter "1/2/2003" into a cell
| directly and format it I get "01 Feb 2003". Clearly VBA is not observing
| the system date/time format which is set to Australian. (ie. dd/mm/yy) but
| it is clearly parsing the .Value to a date serial.
|
| Changing the statement to the following corrects the problem...
|
| ActiveCell.Value = DateValue("1/2/2003")
|
| However this means because my macro is dealing with data of unknown type
| (from a text file) I have to inspect every value to decide if it is a date
| so I can cast it before assigning it to the .Value.
|
| Does anyone know of a cleaner way to get around this problem. The sheet
| involved is always new and has never had any formatting aplied by the time
| the data is being placed on it.
|
| Cheers, Frank.
|
|

 

[Frank_Hamersley]

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_Hamersley]

"William" wrote

Try using "DateSerial" [ DateSerial(Year, Month, Day) ]

Thanks - but I have no problem if I cast the value myself - only if I leave
it to Excel.

Cheers, Frank.

 

[keepITcool]

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 >

 

[keepITcool]

see my other post.. you DO have a problem with casting the date!

keepITcool

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

"William" wrote

Try using "DateSerial" [ DateSerial(Year, Month, Day) ]

Thanks - but I have no problem if I cast the value myself - only if I
leave it to Excel.

Cheers, Frank.

 

[Frank_Hamersley]

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.

 

[keepITcool]

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 >

 

[William]

Frank

I do not understand. Your original post implied you encountered problems in
returning the "correct" date with the code
-----ActiveCell.Value = "1/2/2003"-----
Why is it that the code below (or KeepItCool's solution) does not resolve
your problem?
-----ActiveCell.Value = DateSerial(2003, 2, 1)-----

XL2002
Regards

William

(e-mail address removed)

| see my other post.. you DO have a problem with casting the date!
|
| keepITcool
|
| < email : keepitcool chello nl (with @ and .) >
| < homepage: http://members.chello.nl/keepitcool >
|
|
|
| > "William" wrote
| >>
| >> Try using "DateSerial" [ DateSerial(Year, Month, Day) ]
| >>
| > Thanks - but I have no problem if I cast the value myself - only if I
| > leave it to Excel.
| >
| > Cheers, Frank.
| >
| >
| >
|

 

[Frank_Hamersley]

I do not understand. Your original post implied you encountered problems in
returning the "correct" date with the code
-----ActiveCell.Value = "1/2/2003"-----
Why is it that the code below (or KeepItCool's solution) does not resolve
your problem?
-----ActiveCell.Value = DateSerial(2003, 2, 1)-----

A timely question! In my original post I explained that the "string" at
hand was not known to be a correctly formatted date (locale or otherwise).

This all came to my attention because I had made some VBA code changes to
reduce the number of CPU cycles consumed in preparing the results sheets to
the user (neatly layed out and formatted).

In the old code I inspected every string, determined the most suitable
"type" and established the cell formatting before assigning the string to
the .Value property. In the case of a date, because the .NumberFormat had
been set in advance to what is effectively my locale setting, the casting
was performed correctly and all was well.

However profiling showed this approach was very slow so I mod'ed the code to
defer the application of cell formatting until after all the strings had
been assigned to their respective cells, and then performed the required
formatting on the .EntireColumn. This saved heaps of CPU time but exposed
"the issue" when it ignored the locale.

Anyway as I mentioned before, I am resigned to doing it the hard way because
Excel sure ain't going to changes its spots - I just had a dig at the
original Excel coders because IMO this sort of trait is in the same genus as
the myriad problems cropping up in IE now (and to varying extent in other MS
products in the past).

Cheers, Frank.

 

[Frank_Hamersley]

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]

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,hourseparator
(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.aspx?scid=kb;EN-US;Q214233

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



keepITcool

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

 

[Frank_Hamersley]

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,hourseparator
(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.