Data type conversion.

[Caula]

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

 

[SteveO59L]

try posting a sample of your data, and what you want to do with it

 

[Caula]

Very simple: I have this text "02/02/2015" in any range and want to convert it to Date type.

 

[Rick Rothstein]

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]

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]

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...

Columns("Q").TextToColumns Other:=False