-1

I am getting data in a CSV file with data column having format e.g. 1987-09-17T00:00:00.000-06:00.

When I apply date formatting on it to say DD/MM/YYYY, it doesn't change to 17/09/1987.

Is there a way to achieve this?

Thanks!

Nitin
  • 65
  • 2
  • 9
  • 1
    See e.g. http://stackoverflow.com/questions/20264303/excel-date-and-text-format or http://stackoverflow.com/questions/23277916/convert-text-date-to-date or various other related questions. – MP24 Aug 29 '14 at 15:21
  • possible duplicate of [Changing the date format to yyyy-mm-dd](http://stackoverflow.com/questions/7106238/changing-the-date-format-to-yyyy-mm-dd) – Isaac G Sivaa Aug 29 '14 at 15:23

1 Answers1

2

Excel will assume it is text because it is not in a format it recognises as being a date. You could use LEFT() and MID() functions to split out the parts you want as arguments in the DATE() function. Eg 

=DATE(left(A1,4),(MID(A1,6,2),MID(A1,9,2))
Dave
  • 1,643
  • 1
  • 9
  • 9