Forum Discussion

Reuben's avatar
Reuben
Iron Contributor
May 12, 2017
Solved

How to convert date format from imported data?

I have a csv file exported from another program with a column of dates. Excel recognizes the data as dates but it converts them to the wrong format.   The problem is the date March 27, 2017 shows u...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    May 12, 2017

    Hi Reuben,

     

    The formula could be as

    =DATE(DAY(A1)+2000,MONTH(A1),MOD(YEAR(A1),2000))

    assuming all your dates are in 21st century.

     

    If in A1 is 2027-03-17 (as date, not text) the formula returns 2017-03-27.

     

    However, i'd start from second option

Reuben's avatar
Reuben
Iron Contributor
May 12, 2017
Thanks Sergei - the formula worked perfectly - in this case it might be the best solution for me.
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 12, 2017

My guess was you have two-digits year format for the date, like 17-03-27.

 

As on your screenshot you already have wrong dates in source csv file like 2027-03-17. In such case import wizard could do nothing, there is no conversion from two digit to 4 digits year format.

 

The question is how dates as 2027-03-17 appear in your source file. If no way to change that, when yes, easiest way to do conversions within Excel.

  • Reuben's avatar
    Reuben
    Iron Contributor
    May 12, 2017

    Unfortunately there is no way to change it. It is an export from specific software which does not allow you to specify exporting options.

     

    Thanks for your help though!

    • Frank Urban's avatar
      Frank Urban
      Copper Contributor
      May 11, 2018

      I am late to this dance but I have a similar problem to that which has been discussed here. While mine seems to be easier, I am still banging my head against the wall.

       

      I have attached a file.  In Column G (second column), there is original text.  I removed unnecessary data and this is presented in the first column.  It should be a simple process to convert the text to a date using =+datevalue(B1) which I tried and the result is in the the third column. As you can see, it returned a #VALUE!

       

      Any suggestions?

       

      Kind regards,

      Frank

      • Frank Urban's avatar
        Frank Urban
        Copper Contributor
        May 12, 2018

        That feels much better- I stopped hitting my head against the wall.  I discovered the problem: the system settings were not in synch with the date format in Excel.  Once I changed the system format, everything was okay!

         

        Have a good day.

         

        Frank

    • Frank Urban's avatar
      Frank Urban
      Copper Contributor
      May 11, 2018

      I am late to this dance but I have a similar problem to that which has been discussed here. While mine seems to be easier, I am still banging my head against the wall.

       

      I have attached a file.  In Column G (second column), there is original text.  I removed unnecessary data and this is presented in the first column.  It should be a simple process to convert the text to a date using =+datevalue(B1) which I tried and the result is in the the third column. As you can see, it returned a #VALUE!

       

      Any suggestions?

       

      Kind regards,

      Frank

Resources