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

Michelle Gross's avatar
Michelle Gross
Copper Contributor
Jun 25, 2018

Excel will not format my date that's being pulled from my program (this is a csv file).  I have never had an issue with this until a couple days ago.  Date will read 512017 which needs to be formatted to 05012018 (mmddyyyy or mm/dd/yyyy) I've come close to getting the proper format however, Excel changes my data to completely different numbers.  How do I fix this issue?

 

Thanks, Michelle

CW_1985's avatar
CW_1985
Copper Contributor
Apr 28, 2019

Michelle Gross 

Hi Michelle,

I'm interested to know if you managed to solve your problem (I sure hope so by now) and I am curious as to how you would resolve your date format with the potential of only 1 digit for both day and month.  In your example, you state that 152017 is interpreted as 01-May.  By extension, I guess 1052017 would be read as 10-May.

 

But how would you interpret 1112017?  Is that 1-Nov or 11-Jan?

 

There's some irony too, given that you have a 4-digit year, but only a 1-digit day and month.

I am having challenges importing data in a similar format, but it appears I always have a 2-digit month, eg, 1052017 is always 1-May and not 10-May.

 

Resources