Forum Discussion
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 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......
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
14 Replies
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.
- 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.
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
- Good call Sergei, I didn't catch that difference, thanks for clarifying
- 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.
