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.




Don Guillett

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

Aaron Rubinstein


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



Jan 16, 2014
Reaction score
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 !

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question