VBA: Copy a date and paste as text

[Aaron Rubinstein]

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!

 

[Don Guillett]

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

 

[OssieMac]

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")

 

[Aaron Rubinstein]

OssieMac,

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

Aaron

 

[skiran]

Hi , i need help with the same thing except
I have a VBA procedure that loops through these column starting from row 1 and reads the element and corresponding values and inserts in another another spreadsheet as element name and date inserted as text in the format 2009-11-04.


Below is the logic i am using . What am i doing wrong with the format ?

Sheets ("Sheet_2").range("A" & LTrim(str(RowCnt_nd))) =Range ("A1").value
Sheets ("Sheet_2").range("B" & LTrim(str(RowCnt_nd))).Numberformat="@"
Sheets ("Sheet_2").range("B" & LTrim(str(RowCnt_nd))) =format (Range("A" & LTrim(str(RowCnt_nd)))),"yyyy-mm-dd").value

OssieMac, please help !