Cell A1 contains a date formatted as follows: 11/4/2009

I want to write a VBA procedure that will read the date in A1 and
insert it as text in B1 in the format 2009-11-04.

I can accomplish this easily in excel by inputting the following
function into B1: =TEXT(A1,"yyyy-mm-dd"). I would then copy the result
and Paste Special Values to get the intended result of text that
reads 2009-11-04.

I tried to replicate this in VBA using the following statement:
Range("B1").Value = worksheetfunction.Text(Range("A1"), "yyyy-mm-dd")

However, the value returned in B1 is still formatted as a date and
appears as 11/4/2009.

Can anyone help with this? This is a simple example but I want to use
this code as part of a longer procedure.

Thanks!

Sub changedateformat()
With ActiveCell
..Offset(1).Value = .Value
..Offset(1).NumberFormat = "yyyy-mm-dd"
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Aaron Rubinstein" wrote in message
...
Cell A1 contains a date formatted as follows: 11/4/2009

I want to write a VBA procedure that will read the date in A1 and
insert it as text in B1 in the format 2009-11-04.

I can accomplish this easily in excel by inputting the following
function into B1: =TEXT(A1,"yyyy-mm-dd"). I would then copy the result
and Paste Special Values to get the intended result of text that
reads 2009-11-04.

I tried to replicate this in VBA using the following statement:
Range("B1").Value = worksheetfunction.Text(Range("A1"), "yyyy-mm-dd")

However, the value returned in B1 is still formatted as a date and
appears as 11/4/2009.

Can anyone help with this? This is a simple example but I want to use
this code as part of a longer procedure.

Thanks!

Hi Aaron,

Don's answer reformats the date using the number format. Perhaps that is all
that you require. However, if you really want the result as text in lieu of a
date then you need to format the cell first as text because Excel interprets
dates entered as y/m/d as real dates when entered in a cell formatted to the
default General fromat. Also the Format function is the VBA equivalent of
worksheet function TEXT.

Range("B1").NumberFormat = "@"
Range("B1") = Format(Range("A1"), "yyyy-mm-dd")

--
Regards,

OssieMac


"Aaron Rubinstein" wrote:

Cell A1 contains a date formatted as follows: 11/4/2009

I want to write a VBA procedure that will read the date in A1 and
insert it as text in B1 in the format 2009-11-04.

I can accomplish this easily in excel by inputting the following
function into B1: =TEXT(A1,"yyyy-mm-dd"). I would then copy the result
and Paste Special Values to get the intended result of text that
reads 2009-11-04.

I tried to replicate this in VBA using the following statement:
Range("B1").Value = worksheetfunction.Text(Range("A1"), "yyyy-mm-dd")

However, the value returned in B1 is still formatted as a date and
appears as 11/4/2009.

Can anyone help with this? This is a simple example but I want to use
this code as part of a longer procedure.

Thanks!
.

OssieMac,

That's exactly what I was looking for. Thanks for explaining... makes
sense now!

Aaron