How to stop Excel Treating US dates as UK dates?

I'm in the UK, I've got a problem where I've got a list of dates supplied in US format. Excel seems to treat the ones that are valid in both formats as UK dates, (e.g. 03/01/2012 becomes 3rd of January rather that 1st March), and treat the ones that aren't valid UK dates (e.g. 03/13/2012) as basic text. I assume this choice is something to do with my regional settings.

What I want is the system to recognise that this column of text is supplied in US date format, and convert it into the underlying date representation for calculations.

How do I do this?

EDIT: The dates are supplied in a CSV file of the form:

3/ 1/2012, 09:01     , 18:58     ,9.4,0.6

where 3/1 is 1st of March

Solution 1:

For Excel 2010, rather than opening your CSV file, create a new workbook, then on the DATA tab, select Get External Data → From Text. This gets to the interface where you can specify how to interpret your text data, including how to handle dates.

Solution 2:

Change the file's extension from ".csv" to ".txt", then open with Excel. Excel will give you a text import wizard. Select 'delimited' on the first page, check 'comma' on the second, and on the third you'll be able to select the type for each column of data. One of the types is date and has a drop down with a variety of formats (m/d/y, d/m/y, etc, etc).

Solution 3:

The real problem is that the dates are supplied in a formatted form, the first thing every beginner programmer learns is that never format the dates, they are just numbers that are interpreted to correct format.

So the real solution is : 1.Having the dates in numeric format, having failed that 2.Turn the dates into numeric format yourself and then having them displayed in a separate column in what ever date format.

Number 2 does not involve any programming, just keeping the original column format as general text, using a formula ( better would be a calculated a table column ) that turns it into the numeric form and another column that display that value as a date format to your liking.

Solution 4:

I've come across the same problem but in a slightly different way. The dates being imported from a CSV file are UK format but where the dates are valid in both US & UK excel treats them as US format. My workaround is to parse the dates as text (using NumberFormat = "@") into the sheet then re-formating the cells afterwards as the correct date format (using NumberFormat = "dd/mm/yyyy").

Seems (to me anyway) the simplest way of doing it.