How to stop Excel Treating US dates as UK dates?

Question

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

Answer 1

I have had this problem for quite some time. It seems to be a commonly misunderstood problem. Specifically, that the computer will not necessarily recognize a date in the correct format. If you were to record a date in the DD/MM/YYYY format, excel would interpret it in a MM/DD/YYYY format. So 05/06/2012 would be meant as June 5, 2012, however Excel would interpret it as May 6, 2012. Similarly, if you were to enter 13/12/2012, where you meant Dec 13, 2012, Excel would not recognize the date at all, as there is no month 13.

In order to correct this problem, I have devised a formula that will flip the first two date numbers around. This can be used to convert US to UK, or vice versa.

=IF(ISERR(DATEVALUE(F10))=TRUE,DATE(RIGHT(TEXT(F10,"DD/MM/YYYY"),4),LEFT(TEXT(F10,"DD/MM/YYYY"),2),LEFT(RIGHT(TEXT(F10,"DD/MM/YYYY"),7),2)),IF(ISERR(DATEVALUE(TEXT(F10,"mm/DD/YYYY")))=TRUE,DATE(RIGHT(TEXT(TEXT(F10,"mm/DD/YYYY"),"DD/MM/YYYY"),4),LEFT(TEXT(TEXT(F10,"mm/DD/YYYY"),"DD/MM/YYYY"),2),LEFT(RIGHT(TEXT(TEXT(F10,"mm/DD/YYYY"),"DD/MM/YYYY"),7),2)),0))

This has worked for me exactly how I wanted it to, and I believe is a solution that does not yet exist out in the field. Please let me know if this works as well for you as it has for me.

PS. I was using Excel 2007 for this formula.

Answer 2

Right click on the column, select format cells, choose first tab which is "Number", select category date, and on the right hand side, set "Locale" to "English (U.S.)".

Answer 3

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

Answer 4

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

Answer 5

I have had the same problem, but in reverse: from Australian to US format.

Im my case I run a macro that opens the CSV file. One column of the file contains dates. After adding an extra column with some computed data, the macro saves it as a .XLS file. In the XLS file, a date that is valid in both formats will change, i.e. 1/11/12 will become 11/1/12. Note that my macro does not touch the date column in any way, and the CSV always has the correct format. This only seems to happen on the customer's PC which runs Office 2010. I have not seen it in Office 2000 or 2003.

The only way I've been able to get around it is to change the macro to import the CSV, instead of opening it, and specifying Text format for the date column.

I did consider another possibility, which is to have the macro shell something like a grep command to put a "space" in front of the dates. That stops Excel from interpreting it as a date (quotes around the dates do not help). I decided import was the cleaner way though. As there is no native Win utility to do the editing, I would have to install grep on the customer's PC.

Answer 6

I recently experienced a similar problem with Excel 2010. Excel interpreted dates not interpretable as US date format correctly i.e. any date with a day > 12. For all dates with day < 13, it displayed them in US mm/dd/yy format - all in the same column and all in the same format. My first course of action was to check that my settings in both Excel and Windows were English (Australia) or at the very least English (UK). Neither was correct so I changed both and restarted Excel as advised. This did not fix the problem. I reformatted the date column as text, inserted a couple of rows between the correct dates and the US dates, backfilled by dragging the copy handle then typed the correct dates into the new cells, dragged the contents of the other cells in the record into their ne location and deleted the dodgy rows. What I discovered when I reformatted the date column to date format was that, while my dates were now correctly oriented dd/mm/yyyy, Excel still recognised two different formats - one left justified, the other right justified and none of the format painting or other options would resolve this.

Bizarrely, when I tabbed from one of the left justified cells to the adjacent cell, the cell reformatted to the right justified version and retained the correct date format. I can't explain this given that Excel displayed the "Date" descriptor for both in the "Number section of the ribbon, but it fixed the problem.

Answer 7

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.