Forum Discussion
How to convert date format from imported data?
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
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.
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
- Thanks Sergei - the formula worked perfectly - in this case it might be the best solution for me.
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.
I've tried the import and it doesn't seem to work....