Results 1 to 6 of 6

Thread: Data type conversion.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2010
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Data type conversion.

    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

  2. #2
    Board Regular
    Join Date
    Apr 2004
    Posts
    7,895
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data type conversion.

    try posting a sample of your data, and what you want to do with it
    Yesterday I felt on top of the world. Today its falling in on me.

  3. #3
    New Member
    Join Date
    Aug 2010
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data type conversion.

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

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,250
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Data type conversion.

    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    New Member
    Join Date
    Aug 2010
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data type conversion.

    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

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,250
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Data type conversion.

    Quote Originally Posted by Caula View Post
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •