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

User · Answer

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.

User · Answer

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 · Answer

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

User · Answer

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  month  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 · Answer

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 · Answer

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 · Answer

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

User · Answer

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

User · Answer

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 · Answer

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 · Answer

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

[excel] Convert Text to Date?

Examples related to excel

Examples related to vba