Convert Text to Date?

I have a column of dates (column A) stored as text in the format yyyy-mm-dd which I'm trying to convert to dates, ultimately so that I can do look ups against them.

I've read a few topics on here and tried some of the suggestions, but I can't get anything to work. One was using:

Columns("A").Select
Selection.NumberFormat = "date"

This changed the format of the cells to date but didn't actually change the format of the value which was still stored as text.

My understanding is that I need to use CDate() to change the format of the value from text to date. I've tried something like this:

Dim c As Range
For Each c In ActiveSheet.UsedRange.columns("A").Cells
    c.Value = CDate(c.Value)
Next c

Which gives me a type mismatch error. I wondered if this was because I was trying to save date values back into a non-date formatted cell so I tried combining it with the .NumberFormat = "date" above, which didn't work either.

Any suggestions would be appreciated.

You can use DateValue to convert your string to a date in this instance

Dim c As Range
For Each c In ActiveSheet.UsedRange.columns("A").Cells
    c.Value = DateValue(c.Value)
Next c

It can convert yyyy-mm-dd format string directly into a native Excel date value.

You can quickly convert a column of text that resembles dates into actual dates with the VBA equivalent of the worksheet's Data ► Text-to-Columns command.

With ActiveSheet.UsedRange.Columns("A").Cells
    .TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)
    .NumberFormat = "yyyy-mm-dd"   'change to any date-based number format you prefer the cells to display
End With

Bulk operations are generally much quicker than looping through cells and the VBA's Range.TextToColumns method is very quick. It also allows you the freedom to set a MDY vs. DMY or YMD conversion mask that plagues many text imports where the date format does not match the system's regional settings. See TextFileColumnDataTypes property for a complete list of the available date formats available.

Caveat: Be careful when importing text that some of the dates have not already been converted. A text-based date with ambiguous month and day integers may already been converted wrongly; e.g. 07/11/2015 may have been interpreted as 07-Nov-2015 or 11-Jul-2015 depending upon system regional settings. In cases like this, abandon the import and bring the text back in with Data ► Get External Data ► From Text and specify the correct date conversion mask in the Text Import wizard. In VBA, use the Workbooks.OpenText method and specify the xlColumnDataType.

Besides other options, I confirm that using

c.Value = CDate(c.Value)

works (just tested with the description of your case, with Excel 2010). As for the reasons for you getting a type mismatch error, you may check (e.g.) this.

It might be a locale issue.

Perhaps:

Sub dateCNV()
    Dim N As Long, r As Range, s As String
    N = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To N
        Set r = Cells(i, "A")
        s = r.Text
        r.Clear
        r.Value = DateSerial(Left(s, 4), Mid(s, 6, 2), Right(s, 2))
    Next i
End Sub

This assumes that column A contains text values like 2013-12-25 with no header cell.