CODEWITHSUNDEEP
excel
114
114

Reputation: 926

Incorrect date formats in Excel when importing or opening files

I am noticing that many files that I use have incorrect date formats. Specifically, some rows will contain correctly formatted dates (DateTime: MDY HH:MM:SS AM/PM), while others register incorrectly. I know that others register incorrectly because although they look the same column cannot be sorted from newest to oldest, and shows that there are dates from the future in late 2014. The typical column looks like this:

5/19/2014 10:39:24 AM
5/19/2014 10:39:24 AM
  07/02/2014 12:55:00 PM
  07/02/2014 12:55:00 PM
3/14/2014 8:25:49 PM

When I import the files to Excel I make sure to select MM/DD/YYYY format, and have tried again to format the problem cells with a custom format after having imported the file. In both cases the dates still remain incorrectly formatted. Would anyone happen to know what's causing this?

Note that these dates look the same in .csv as well: One appears as 

3/13/2014 12:48:06 PM

and is formatted in a 'custom' format MDY as specified in the import, while

3/31/2014 10:00:00 AM

comes up as 'general' format.

UPDATE/ANSWER: This issue was resolved by changing regional settings in Windows. Excel gets confused with mm/dd/yyyy formats if it is opening the file in a system where dd/mm/yyyy is standard. When the 'month' is greater than 12 it gets confused and fails to format properly.

Upvotes: 2

Views: 19992

Answers (2)

Venkata Naresh Babu
Venkata Naresh Babu

Reputation: 367

Using Sqldeveloper importing the data from excel file it will show you map the source and target table columns, in this screen while mapping the date column select the format is empty one, then it automatically takes the format mentioned in the excel columns to table

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60344

Apparently the problem is that some of the dates include a nbsp character, which prevents the text import wizard from converting.

I would suggest the following.

The simplest might be to process the CSV file with a text editor and replace the nbsp's with nothing.

Other option might be

"Import" the .csv file On the Text Import Wizard:

  • Step 1 -- Delimited
  • Step 2 -- select both the comma and, using the Other option, the alt-0160 character
  • Step 3 -- select DMY for any column that contains dates
  • Clean up the extra cells if the process offsets some, but not all, of the data by one column.

Another option would be to convert the text number equivalents to real numbers. You should then be able to format them. This can be done by

  • Enter a 1 in some blank cell
  • Copy that cell
  • Select the column of dates
  • Paste/Special Multiply

You should then be able to format the cells as you wish.

Upvotes: 3

Related Questions