date issue

K

khennet

hi,
i use excel2000
i have a problem with date format.
i get a variant with toto="15/03/2003"
when i put it in cells

dim toto as variant
with Cells(x,y)
.value = toto
.numberformat = "DD/MM/YYYY"
end with

it displays "03/15/2003" and the cell format is as"mm/dd/yy"!!!!.
i tried to format the cell before affecting data, same issue!
i tried to put data into a string var before putting it into cells
i tried to format(toto, 'dd/mm/yy")
anyway the same issue
the regional parameters are short= dd/mm/yy
long = dd/mm/yyyy


please help me!!!!!
 
T

Tom Ogilvy

dim toto as long
toto = DateValue("03/15/2004")
with Cells(x,y)
.value = toto
.numberformat = "DD/MM/YYYY"
end with
 
T

Tom Ogilvy

When you declare as a variant or string and populate the cell, it's not a
number.
'
With reference to valid date strings, that doesn't seem to be universally
true - in fact I would say it is more likely to be untrue.
Win 2k, Excel 2K US English

Using a variant:

Sub TestVariant()
Dim v As Variant
v = "03/15/2004"
ActiveCell.NumberFormat = "General"
ActiveCell.Value = v
Debug.Print ActiveCell.Value
Debug.Print ActiveCell.Value2
Debug.Print ActiveCell.Text
Debug.Print ActiveCell.NumberFormat
End Sub

Produces:
3/15/2004
38061
3/15/2004
m/d/yyyy


Using a String:

Sub TestVariant()
Dim v As String
v = "03/15/2004"
ActiveCell.NumberFormat = "General"
ActiveCell.Value = v
Debug.Print ActiveCell.Value
Debug.Print ActiveCell.Value2
Debug.Print ActiveCell.Text
Debug.Print ActiveCell.NumberFormat
End Sub

Produces
3/15/2004
38061
3/15/2004
m/d/yyyy

--
Regards,
Tom Ogilvy


Brad Vontur said:
When you declare as a variant or string and populate the cell, it's not a
Click to expand...
number. It's text. Instead, declare your variable as a date, or use
conversion functions like CDate(toto) prior to placing the value.
An issue you might run into is the date notations that Excel recognizes.
Click to expand...
"15/03/2004" will default to your format dd/mm/yyyy properly because 15
isn't a valid month. But if you use "12/03/2003" to try to grab March 12th,
instead you'll get December 3rd.
If possible, when building your date variable use the mm/dd/yyyy format,
Click to expand...
and then output it from a date variable however you like using the format
function.
 
B

Brad

Let me add another clause to my statement then

When you declare a variable as a variant or string and populate the cell, it's not a number unless it's a reference to a valid date string; which VB will implicitly coerce into a date

Thanks for pointing out my oversight

-Brad
 
Top