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

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.

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

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

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

Answer 5

Probably you can do the following:

1) Change the date setting on your windows taskbar to US timing. Here's a guide on how to do so: http://www.sysprobs.com/change-date-format-windows-7-ddmmyyyy

2) Set US timing on excel. Go to format cells>Date>Locale>English (US)

This worked for me, hope it helps.

Answer 6

CONVERT US DATES TO UK DATES IN EXCEL/CSV

(you don't really want to be changing your locale settings)

This is a 2 stage process:

1. To stop Excel trying to autoconvert some dates into UK format and then messing up the conversion, In Excel highlight the column and go to Data > Text to Columns.

   1. Select Delimited, Next
   2. Clear all the Delimiter tick boxes, Next
   3. Select data type of ‘Text’, Next

Don’t try and just set this via the formatting menu (right-click format cells), it will not work. Don't select Date on the 'Text to columns' either as this will convert US dates using UK settings, use 'Text'

[Also see dunxd's answer for importing csvs as well. This will also work if imported as txt.]

2. In a spare close-by column copy the following formula. Replace references to F2 with your starting cell. Format the column in whatever date format you choose.

=DATEVALUE(CONCATENATE((IF(MID(F2,4,1)="/",MID(F2,3,1),IF(MID(F2,2,1)="/",MID(F2,3,2),IF(MID(F2,6,1)="/",MID(F2,4,2),MID(F2,4,1))))),"/",IFERROR((IF(SEARCH("/",LEFT(F2,2)),LEFT(F2,1),LEFT(F2,2))),LEFT(F2,2)),"/",IF(ISERR(FIND("/",RIGHT(F2,3),1)),RIGHT(F2,4),RIGHT(F2,2))))

Note: this should work with dates in the following formats:

• 1/1/14
• 01/1/14
• 1/01/14
• 01/01/14
• 1/1/2014
• 01/1/2014
• 1/01/2014
• 01/01/2014

If your date is not separated by “/”s replace this delimiter in the formula above.

Hope it helps.

---

UPDATE 17-2-2014

As requested a bit of a breakdown:

Datevalue and Concatenate just create the final string in UK date format, so to the interesting bit:

a. Get the US Day (mid value)

((IF(MID(F2,4,1)="/",MID(F2,3,1),IF(MID(F2,2,1)="/",MID(F2,3,2),IF(MID(F2,6,1)="/",MID(F2,4,2),MID(F2,4,1)))))

The most tricky part. We are unsure of the date format. We know that there will be two "/" somewhere but we need to find where. The most tricky part is in a data such as 1/1/10 the second "/" could be as early as the 4th chr, where in a date like 10/10/10 the first "/" is the 3rd, where the second is the 6th chr. We need a way to tell the first and second "/" apart. Its lucky that a '1/1/' format is the only way a "/" could be the 4th chr so we can use this:

• If "/" is the exact 4th chr (meaning d and m will be single chr), return the 3rd chr as the US day value.
• If not (meaning that there must be at least one 2chr number in the US day or month), if the 2nd chr is a "/", then return the 3rd and 4th chr as the US day value.
• If not, if the 6th chr is a "/" (meaning the date must be 2 doubles
  for day and month ie. '10/10/'), return the 4 & 5th chr as the US day value, otherwise return just the 4th chr as the US day value.

b. Get the US Month (left value)

IFERROR((IF(SEARCH("/",LEFT(F2,2)),LEFT(F2,1),LEFT(F2,2))),LEFT(F2,2))

Fairly simple,

• If "/" exists in the leftmost 2 chr of the string, Return just the 1st chr
• if not, return the first 2 chr
• if error, its the same as not finding "/", so return the first 2 chr as well

c. Get the Year (right value)

IF(ISERR(FIND("/",RIGHT(F2,3),1)),RIGHT(F2,4),RIGHT(F2,2))

Simple,

• if "/" exists in the last 3 chr of the string, return last 2 chr, if not return last 4 chr, (4 chr year or 2 chr year).

Hope this helps.

Answer 7

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 8

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 9

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 10

The problem is that you have your date setting in excel set as (and some date) Date formats that begin with and asterik() respond to changes in regional date and time settings specified for the operating system. You need to go to your control panel and adjust the date and time format there as well to ensure no flipping of dates. Sometimes 01/11/2013 for example will flip to 11/01/2013 (where 11 is november) however 30/11/2013 will not flip, this is because ms does not recognize the 30th as a valid month. no formula required

Answer 11

Related information, and the answer to all problems:

Save all date and time information in one format only:
http://en.wikipedia.org/wiki/ISO_8601

Attach a display format field to the saved data, if needed.

Answer 12

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