VBA: Copy a date and paste as text

  • Thread starter Thread starter Aaron Rubinstein
  • Start date Start date
A

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!
 
Sub changedateformat()
With ActiveCell
..Offset(1).Value = .Value
..Offset(1).NumberFormat = "yyyy-mm-dd"
End With
End Sub
 
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")
 
OssieMac,

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

Aaron
 
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 !
 
Back
Top