Data type conversion.

Caula

New Member
I have a column of text strings that looks like short dates but when I check the cell type it comes ou as text.
I'm after the VBA code to convert such text strings to the equivalent date and date data type.
What is the best way to achieve that? Any suggestions?

Thanks,

Claula
 

Rick Rothstein

MrExcel MVP
Do you have to use VB to do this? How about a simple manual method? Select the column with your text dates in them, then call up the Text To Columns dialog box (Data tab, Data Tools group, Text To Columns button) and as soon as the dialog box appears, click the Finish button... you should now be able to use Cell Formatting to make the resulting date serial numbers look like whatever date format you want.
 

Caula

New Member
Thanks for your hel Rick!

The reason I want to do that with VBA is because I need to loop through a counlumn with tens od thousands of these texts. The data will be updates every month and I'll be repeating this operation periodically.

All I need is the function or best way to go about that and I'll be able to write the Sub. I saw somewhere a function named "CDate" which I've never used before. Is this the way to go?

Thanks again Rick
 

Rick Rothstein

MrExcel MVP
Thanks for your hel Rick!

The reason I want to do that with VBA is because I need to loop through a counlumn with tens od thousands of these texts. The data will be updates every month and I'll be repeating this operation periodically.

All I need is the function or best way to go about that and I'll be able to write the Sub.
Here is the code line (there is only one) that does the same thing as the manual method I posted about. Let's assume the column with your text dates in them is Column G, then the code line to convert them to real dates is this...
Code:
Columns("Q").TextToColumns Other:=False
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top