Question

  • Creator
    Topic
  • #2251341

    Convert Excel Date String to Date type

    Locked

    by janania ·

    I have a date string with “Monday, February 02, 2007” format in a one cell column.

    This is a .csv file exported to a excel file for some reason the column was changed to a string type instead a date type.

    I need help in converting this date string to a date type of any format as long as it’s a date type not a string.

All Answers

  • Author
    Replies
    • #2504129

      Clarifications

      by janania ·

      In reply to Convert Excel Date String to Date type

      Clarifications

    • #2504126

      Well that being a completely non standard date format

      by tony hopkinson ·

      In reply to Convert Excel Date String to Date type

      means very few (if any) programs will auto convert it which is why it’s coming in as a string.

      I’m not an excel aficionado, but the only way I can see forward would be to import it then add a formula to manipulate the string into a recognisable date format and then convert that to a date. ’02-Feb-2007′ would probably be the easiest manipulation.

      Course if you have control of the export, you could change it to output the date in a recognised format, but life is not usually that nice. 😀

      • #2504122

        Thanks.

        by janania ·

        In reply to Well that being a completely non standard date format

        Hi Tony, thanks for your wise reply..but how is my problem now for the manipulation. I’m not a VB guru either… Let’s see what I can come up with if any…there goes my weekend 🙁

        • #2504112

          Do it in bits

          by tony hopkinson ·

          In reply to Thanks.

          “Monday, February 02, 2007” in A1

          b1 = Mid(instr(“,”,A1)+2,20) gives “February 02, 2007″
          then make the following columns strings with format

          c1 = Mid(1,instr(” “,b1) -1,20 gives “February”
          d1 = Mid(instr(“,”,b1)-2,2) gives 2
          e1 = mid(instr(“,”,b1)+2,4) gives 2007
          f1 = d1 & “-” & Left(c1,3) & “-” & e1

          the format F column as date.

          My vba is iffy as well, but something like this should work, no doubt an excel expert could give a better way.

        • #2494807

          Awesome!

          by janania ·

          In reply to Do it in bits

          Hi Tony, I used find() instead of InStr(). and Voila! it worked. Thanks a lot!

        • #2611378

          my email id is sanjaykumar84@hotmail.com

          by sanjaykumar84 ·

          In reply to Do it in bits

          Hi,

          I have a data which is in the format xx.xx.xxxx which i want to convert to date type. But out of 200 records only 50 are changing. rest are not changing. what could be the reason.

Viewing 1 reply thread