Convert Text to Date?

user_1300244 0
Viewed 343466

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.

vba excel
11 Answers

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.

user_13215160

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.

user_27078640

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.

user_24746560

I had a very similar issue earlier. Unfortunately I looked at this thread and didn't find an answer which I was happy with. Hopefully this will help others.

Using VBA.DateSerial(year,month,day) you can overcome Excel's intrinsic bias to US date format. It also means you have full control over the data, which is something I personally prefer:

function convDate(str as string) as Date
  Dim day as integer, month as integer, year as integer
  year  = int(mid(str,1,4))
  month = int(mid(str,6,2))
  day   = int(mid(str,9,2))
  convDate = VBA.DateSerial(year,month,day)
end function
user_63021310

Blast from the past but I think I found an easy answer to this. The following worked for me. I think it's the equivalent of selecting the cell hitting F2 and then hitting enter, which makes Excel recognize the text as a date.

Columns("A").Select
Selection.Value = Selection.Value
user_98329250

Solved the issue for me :

    Range(Cells(1, 1), Cells(100, 1)).Select

    For Each xCell In Selection

    xCell.Value = CDate(xCell.Value)

    Next xCell
user_117398960
Related