CODEWITHSUNDEEP
exceldatedatetimeformatting
user1286695
user1286695

Reputation: 27

Excel: date formatting

I downloaded a csv file with dates in one column. The dates are formatted dd/mm/yyyy, but when i open the file Excel auto formats the dates it recognises as an actual date and leaves dates it doesn't recognise as a text.

So for example, 01-01-2010 would be 1 January 2010, but excel reads it as January 1 2010 and formats it as such. Something like 30-07-2010 is not recognised and is formatted as general text.

So now I have groups of rows which are formatted to dates as dd/mm/yyyy and groups of rows that are simply xx-xx-xxxx.

Is there any way that can I easily convert stuff like 30-07-2010 to something excel recognises as a date input?

The file I have is downloaded and there is no way to select a file that formats dates as mm-dd-yyyy

Thanks a lot!

Upvotes: 1

Views: 1183

Answers (1)

Joe Mercurio
Joe Mercurio

Reputation: 83

Don't open the CSV file with Excel. Create a new Excel spreadsheet first and then go to the Data Tab --> Get External Data --> From Text.

Find the file in question and open it. In Step 3 the import wizard will ask you for the Column Data Format. Select the 'Date' radio button and in the drop-down to the right, select "MDY". Click Finish to import.

Upvotes: 1

Related Questions