Forum Discussion

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

Iron Contributor

May 12, 2017

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.

SergeiBaklan

Diamond Contributor

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
Iron Contributor
May 12, 2017
Thanks Sergei - the formula worked perfectly - in this case it might be the best solution for me.
- 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
    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!
Reuben
Iron Contributor
May 12, 2017
I've tried the import and it doesn't seem to work....