Convert Text to Date?
excel, vba
Solution
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.
Problem
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.