How to convert date format from imported data?

[Reuben]

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 up as 2027-03-17.

Is there a way to convert those dates to 27-Mar-2017 or something correct?

I've tried using "text to columns" but it didn't work. Maybe I'm doing something wrong......

[#2]

Highlight the cells that need formatting changed, go to the top banner under the "numbers" section, and click on the icon in the bottom right corner. Clicking on this bottom right corner will pop out another box with more options. Click on "date" on the left side of the pop-out and select the date format that fits your needs.

[#3]

Hi Reuben,

If your regional settings are in ISO format (2017-03-17) and in your csv file dates looks like 27-03-17 when yes, such data will be recognized as one in year 2027. Since Excel considers your csv date as one with two-digits year format, i.e. as YY-MM-DD.

Above is my guess since not clear how dates are formatted within csv (text) file. If so, there are two ways to fix

1) use Excel formulas to change years and dates

2) or simply not to open csv file but import data from it into new excel workbook (use Get Data from Text), on the third step of the importing wizard for column data format select Date and change default YMD on DMY.

[#4]

Good call Sergei, I didn't catch that difference, thanks for clarifying

[#5]

Thanks Sergei! I'll definitely try that. I couldn't find a formula for your option #1 but I'll try option #2 and see how it works.

[#6 · Accepted solution]

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

[#7]

I've tried the import and it doesn't seem to work....

[#8]

Thanks Sergei - the formula worked perfectly - in this case it might be the best solution for me.

[#9]

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.

[#10]

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!

[#11]

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

[#12]

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

[#13]

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

[#14]

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

[#15]

@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.