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.

Hi @user1300244, did you find any answer worth accepting? It is always good to give feedback / rate answers. — sancho.s ReinstateMonicaCellio, Jun 19 '17 at 06:25
Type mismatch error usually means invalid date like `"1900-13-12"`, `"1900-02-29"`, `"10000-01-01"`, etc. Dates before `"1899-12-30"` result in the generic "Run-time error '1004': Application-defined or object-defined error" — Slai, Oct 10 '17 at 14:12

score 35 · Answer 1 · answered Dec 04 '13 at 12:48

35

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.

answered Dec 04 '13 at 12:48

Sam

7,245
3
25
37

1

btw there is also some `TimeValue(...)` and `Const ds = "31.12.19 16:00"` would work for `DateValue(ds) + TimeValue(ds)` at least in a German time format environment – Andreas Covidiot Sep 23 '19 at 14:02
2

Great, thanks. Useful even 7 years later :) – Geoff Griswald Jan 29 '21 at 16:47

score 34 · Answer 2 · answered Jun 03 '15 at 02:11

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.

The DateValue function works better and uses less resource than TextToColumns — Geoff Griswald, Jan 29 '21 at 16:47

score 15 · Answer 3 · edited May 23 '17 at 11:46

15

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.

edited May 23 '17 at 11:46

Community

1
1

answered Dec 04 '13 at 14:05

sancho.s ReinstateMonicaCellio

14,708
20
93
185

score 6 · Answer 4 · answered Dec 04 '13 at 12:31

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.

score 5 · Answer 5 · edited Feb 28 '21 at 14:50

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

score 3 · Answer 6 · answered Oct 28 '15 at 15:47

3

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

answered Oct 28 '15 at 15:47

Jose Luis Martin Cara

33
5

This is basically the same as @Sam's answer above, except that it uses a For loop instead of a For Each loop. The benefits of a For Each loop are twofold: First, it automatically sets the variable to be equal to the Range in question. Second, it eliminates the need to determine how many cells exist in the range. – Grade 'Eh' Bacon Oct 28 '15 at 15:49
The "xlUp" method of finding the end of a range is awkward and clunky to me. If there were any cells with values after the end of your range it would fail. I much prefer the "For each x in range" logic in Sam's answer. Excel already knows how many cells there are in the range, we don't need to store it as a variable. – Geoff Griswald Feb 01 '21 at 12:07

score 1 · Answer 7 · answered Feb 20 '15 at 18:10

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.

score 1 · Answer 8 · answered Aug 16 '18 at 13:02

1

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

answered Aug 16 '18 at 13:02

Christian Stanyer

65
1
6

If you have UK-formatted dates (or probably any other format than US) then doing this will often convert UK dates which are supplied as text to US dates in date format. Using the DateValue method in Sam's answer works a lot better than this. – Geoff Griswald Jan 29 '21 at 16:49

score 0 · Answer 9 · answered Jun 01 '17 at 08:36

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"

score 0 · Answer 10 · answered Sep 05 '17 at 00:37

0

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.

answered Sep 05 '17 at 00:37

Gilco

1,326
12
13

@GeoffGriswald which answer is the accepted answer? – Davesexcel Jan 31 '21 at 12:34
The top-voted answer sorry. Didn't notice that it hasn't been accepted. – Geoff Griswald Feb 01 '21 at 12:04

score 0 · Answer 11 · answered Jul 04 '19 at 13:24

0

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

answered Jul 04 '19 at 13:24

bastien

1

Convert Text to Date?

11 Answers11

Linked

Related