Date not converting to regional format
 

I have a colleague in Switzerland who has a macro which creates a spreadsheet
that needs to be sent to me here in the U.S. and to others in various parts
of the world. He is working with his regional formats (date, etc.).

He is trying to place a date value in a cell. When he does, he says it
looks OK when he views the cell, but when he sends the spreadsheet to me,
even though the cell is formatted as Date, it looks like text to me. A value
which he enters as "31.03.2006" should look like "3/31/2006" on my machine,
but instead looks like the original "31.03.2006" and is left justified, as
text would be.

Below is the code he is using. How should it be changed to accomplish this?

Dim vStartDate As Variant
vStartDate = Range("A3").Value
sValue(i) = DateValue(vStartDate)

Cell A3 has a valid, regionally formatted date in it ("31.03.2006").
The destination end up with a text value ("31.03.2006") instead of a date
value.

Date not converting to regional format
 

that code doesn't put a date in A3, so there is no change to be made to that
to put a date in A3. If you colleague formats A3 as General, it should look
like 38807 which is the date serial value for 3/31/2006 and his how dates
are stored. If it still looks like "31.03.2006" then it is being stored as
a string.

--
Regards,
Tom Ogilvy


"Bill Sturdevant" wrote in
message ...
I have a colleague in Switzerland who has a macro which creates a
spreadsheet
that needs to be sent to me here in the U.S. and to others in various
parts
of the world. He is working with his regional formats (date, etc.).

He is trying to place a date value in a cell. When he does, he says it
looks OK when he views the cell, but when he sends the spreadsheet to me,
even though the cell is formatted as Date, it looks like text to me. A
value
which he enters as "31.03.2006" should look like "3/31/2006" on my
machine,
but instead looks like the original "31.03.2006" and is left justified, as
text would be.

Below is the code he is using. How should it be changed to accomplish
this?

Dim vStartDate As Variant
vStartDate = Range("A3").Value
sValue(i) = DateValue(vStartDate)

Cell A3 has a valid, regionally formatted date in it ("31.03.2006").
The destination end up with a text value ("31.03.2006") instead of a date
value.

Date not converting to regional format
 

When the format of cell A3 is changed to General, the value does show as
38807. But when the value is placed into the destination cell, even though
the format of that cell is Date, it still gets place there as a string. How
do we get it place in the destination cell as a date?

"Tom Ogilvy" wrote:

that code doesn't put a date in A3, so there is no change to be made to that
to put a date in A3. If you colleague formats A3 as General, it should look
like 38807 which is the date serial value for 3/31/2006 and his how dates
are stored. If it still looks like "31.03.2006" then it is being stored as
a string.

--
Regards,
Tom Ogilvy


"Bill Sturdevant" wrote in
message ...
I have a colleague in Switzerland who has a macro which creates a
spreadsheet
that needs to be sent to me here in the U.S. and to others in various
parts
of the world. He is working with his regional formats (date, etc.).

He is trying to place a date value in a cell. When he does, he says it
looks OK when he views the cell, but when he sends the spreadsheet to me,
even though the cell is formatted as Date, it looks like text to me. A
value
which he enters as "31.03.2006" should look like "3/31/2006" on my
machine,
but instead looks like the original "31.03.2006" and is left justified, as
text would be.

Below is the code he is using. How should it be changed to accomplish
this?

Dim vStartDate As Variant
vStartDate = Range("A3").Value
sValue(i) = DateValue(vStartDate)

Cell A3 has a valid, regionally formatted date in it ("31.03.2006").
The destination end up with a text value ("31.03.2006") instead of a date
value.

Date not converting to regional format
 

Dim DestCell as Range
Set DestCell = Range("B9")
DestCell.Value2 = clng(Range("a3").Value2)
DestCell.NumberFormat = "mm/dd/yyyy"

--
Regards,
Tom Ogilvy


"Bill Sturdevant" wrote in
message ...
When the format of cell A3 is changed to General, the value does show as
38807. But when the value is placed into the destination cell, even
though
the format of that cell is Date, it still gets place there as a string.
How
do we get it place in the destination cell as a date?

"Tom Ogilvy" wrote:

that code doesn't put a date in A3, so there is no change to be made to
that
to put a date in A3. If you colleague formats A3 as General, it should
look
like 38807 which is the date serial value for 3/31/2006 and his how
dates
are stored. If it still looks like "31.03.2006" then it is being stored
as
a string.

--
Regards,
Tom Ogilvy


"Bill Sturdevant" wrote in
message ...
I have a colleague in Switzerland who has a macro which creates a
spreadsheet
that needs to be sent to me here in the U.S. and to others in various
parts
of the world. He is working with his regional formats (date, etc.).

He is trying to place a date value in a cell. When he does, he says
it
looks OK when he views the cell, but when he sends the spreadsheet to
me,
even though the cell is formatted as Date, it looks like text to me.
A
value
which he enters as "31.03.2006" should look like "3/31/2006" on my
machine,
but instead looks like the original "31.03.2006" and is left
justified, as
text would be.

Below is the code he is using. How should it be changed to accomplish
this?

Dim vStartDate As Variant
vStartDate = Range("A3").Value
sValue(i) = DateValue(vStartDate)

Cell A3 has a valid, regionally formatted date in it ("31.03.2006").
The destination end up with a text value ("31.03.2006") instead of a
date
value.