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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

This code is working for me

Dim N As Long, r As Range
N = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To N
    Set r = Cells(i, "B")
    r.Value = CDate(r.Value)
Next i

Try it changing the columns to suit your sheet

Answer 7

To the OP... I also got a type mismatch error the first time I tried running your subroutine. In my case it was cause by non-date-like data in the first cell (i.e. a header). When I changed the contents of the header cell to date-style txt for testing, it ran just fine...

Hope this helps as well.

Answer 8

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

Answer 9

I've got rid of type mismatch by following code:

Sub ConvertToDate()

Dim r As Range
Dim setdate As Range

'in my case I have a header and no blank cells in used range,
'starting from 2nd row, 1st column
Set setdate = Range(Cells(2, 1), Cells(2, 1).End(xlDown)) 

    With setdate
        .NumberFormat = "dd.mm.yyyy" 'I have the data in format "dd.mm.yy"
        .Value = .Value
    End With

    For Each r In setdate
        r.Value = CDate(r.Value)
    Next r

End Sub

But in my particular case, I have the data in format "dd.mm.yy"

Answer 10

For DD-MM-YYYY here is a simple workaround to manage string and dates:

insert the date into the string via DD-MMM-YYYY for example 01-11-2017 -> 01-Nov-2017

U can use the FORMAT(date, "dd-mmm-yyyy") to input dates into a string from the spread sheet.

Later, when you output it from a string, it will not confuse the days and months.

Answer 11

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