Autoconvert U.K. to American date in Excel

[FrauW]

How do I by just selecting an entire column convert dates from british to american
eg. 30/9/2006 to 9/30/2006 without manually typing it in. Can't seem to do it by selecting Format-Cells-Date... because this only converts those where the day was originally a single digit i.e. it changes 4/5/2006 (European 4th of May) to 4/5/2006 (American May 4th).

HELP!

 

[jgurgen]

highlight all the cells and right click -> format cells

highlight dates on the left change your locale you English(United Kingdom) and pick the format you want and select ok

 

[jgurgen]

i misread your post, but same thing only to convert it to US dates its English(United States)

 

[SkipVought]

Hi,

Why do Dates and Times seem to be so much trouble? faq68-5827

Is 30/9/2006 REALLY a date?

Format as GENERAL and see what happens. It the displayed value changes to a NUMBER, then you have a REAL DATE. If it remains as 30/9/2006, then all you have is a STRING representing a date.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[FrauW]

Thanks everyone. I think you misunderstood. The column entries had already been imported from another tool so December 12, 2006 in the European format was 29/12/2006. There is no way to change this via the usual Format->Cells. You do indeed have to ensure the format is 'General' however afterward you must:

Select the date(s)
Data->"Text to Column"->Leave default ("Delimited"), Click "Next"->" Next"->select column data format –ensure the “Date” radio button is selected- choose MDY

The problem still remains that if it's December 9, 2006 i.e. 9-12-2006 it'll change to 12/9/2006 no problem but once we're in the double digits there is no change. Any additional assistance is helpful.

 

[Lilliabeth]

You have not confirmed that you tried the previous suggestion: In the Format cells dialog box, choose Date category and on the right side of the dialog box is a drop-down that will allow you to change the Locale.

That should work (even on two-digit days). If it doesn't, I wonder if you have real dates, or just text that looks like dates.

 

[TonyJollans]

If I understand correctly you have textual data in d/m/y format and you want to convert it to valid US dates in m/d/y format. I think you need to do this explicitly yourself. If you have a text date in A2 put this in B2:

[blue][tt]=DATE(RIGHT(A2,LEN(A2)-FIND("/",A2,FIND("/",A2)+1)),
MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1),
LEFT(A2,FIND("/",A2)-1))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[FrauW]

Locale is defaulted to US so that is correct. Also, Tony, thx 4 the formula but it doesn't work. Dates are written 12-9-2006 for Septebmer 9,2006. I want to change it automatically to 9/12/2006. As I said, my above suggestion (Data->Text to columns etc...)works like a charm for single digit dates but not double digit dates eg. 22-9-2006 (September 22,2006) stays 22-9-2006 instead of changing to 9/22/2006

Additional feedback welcome!

 

[SkipVought]

That's because...

its NOT REALLY A DATE.

Did you try the Format thing?

Changing the format to GENERAL does NOTHING, does it???

It's just a STRING that looks like a date.

Did you read the FAQ in order to understand what's happening?

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[xlhelp]

FrauW,

In step 3 of your Text-To-Column, please choose DMY. That should work.

Member AAA - Abolish Abused Abbreviations

 

[TonyJollans]

You can't use Text to Columns the way you're trying to, but if your dates have "-" separators, change all the "/" in my formula to "-" and see if that helps.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[AnotherHiggins]

First re-read Skip's post and then read his FAQ to which he linked.

Locale is defaulted to US so that is correct.

There's the clue.

You and your computer are set up to use the American standard of mm/dd/yy.

The data you are importing is in the European standard of dd/mm/yy.

When Excel sees 5/4/06, that does makes sense as a date, so Excel stores it as a date.

When Excel sees 25/10/06, that does NOT make sense as a date, so Excel stores this as a STRING (not a number).

That is why your Text-To-Column works sometimes, but not always.

I'm not entirely clear on what your data looks like, but try this:
[COLOR=blue white]=IF(ISNUMBER(A1),VALUE(A1),VALUE(TEXT(MID(A1,4,3)&LEFT(A1,3)&RIGHT(A1,2),"mm/dd/yy")))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlhelp]

My 2c:

When you are doing text-to-columns, you need to tell Excel what you have; not what you are aiming for. That's why, in Step 3, choosing DMY will work.

Member AAA - Abolish Abused Abbreviations