Help With Dates to Text!!!

[emperornortonus]

I've been looking for answer to this one for awhile. I have a bunch of files with cells that contain a date in the date format (i.e. 10/15/08 stored as "39736" in excel). My task is to create a macro that converts these excel dates to static text in the format yyyy-mm-dd. I've been trying to look for a function online and in the VBA help that will do this. No luck! I keep finding format functions or "=text("A1", yyyy-mm-dd) but both keep the information stored as a date data type. I want to convert the actual data type to text while formatting the text into yyyy-mm-dd. If anyone can help me with this it would be GREATLY appreciated. I was thinking maybe formating the date to yyyy-mm-dd and then convert to raw text....but I don't know.

 

[VoG]

Hello and welcome to MrExcel.

Select your range then run the macro:

Sub DatesToText()
Dim c As Range
For Each c In Selection
    If IsDate(c.Value) Then
        c.NumberFormat = "@"
        c.Value = Format(c.Value, "yyyy-mm-dd")
    End If
Next c
End Sub

 

[emperornortonus]

Thank you so much! This code is a life saver!!!

 

[emperornortonus]

Ugh, I'm sorry to ask again...but I tried applying it to my macro

Dim dateField as Range

Set dateField = copyBook.Worksheets("AB_Broker").Range("C39")
If IsDate(dateField.Value) Then
dateField.NumberFormat = "@"
dateField.Value = Format(dateField.Value, "yyyy-mm-dd")
End If

Every time I try to run this I get "Wrong number of arguments or Invalid property assignment"

HELP!

 

[emperornortonus]

Nevemind! Figured it out. Thanks again!