Data type conversion.

Caula

New Member
Joined
Aug 6, 2010
Messages
46
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
 

Caula

New Member
Joined
Aug 6, 2010
Messages
46
Very simple: I have this text "02/02/2015" in any range and want to convert it to Date type.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,568
Office Version
2010
Platform
Windows
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
Joined
Aug 6, 2010
Messages
46
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
Joined
Apr 18, 2011
Messages
35,568
Office Version
2010
Platform
Windows
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
 

Forum statistics

Threads
1,085,490
Messages
5,383,965
Members
401,868
Latest member
herbalgirlskincare

Some videos you may like

This Week's Hot Topics

Top