Convert Text to Date?

Question

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.

Answer 1

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.