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!