date issue
 

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!!!!!

date issue
 

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


--
Regards,
Tom Ogilvy



"khennet" wrote in message
...
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!!!!!

date issue
 

Hi
try chaning the line
.value = toto
to
.value = dateserial(2003,3,15)

--
Regards
Frank Kabel
Frankfurt, Germany

"khennet" schrieb im Newsbeitrag
...
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!!!!!

date issue
 

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" wrote in message
...
When you declare as a variant or string and populate the cell, it's not a
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.
"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,
and then output it from a date variable however you like using the format
function.

HTH.

-Brad

date issue
 

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