VBA: Copy a date and paste as text
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! |
Copy a date and paste as text
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! |
VBA: Copy a date and paste as text
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! . |
VBA: Copy a date and paste as text
OssieMac,
That's exactly what I was looking for. Thanks for explaining... makes sense now! Aaron |
All times are GMT +1. The time now is 10:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com