A variation on the text to date issue

This is a variation on the text date to number date thing.

Its not that I cant convert the text date to number its what happens when I do that I cant control.

To illustrate I extract data from a database that provides date and time stamps.

The date come out as text with the following format.

dd.mm.yyyy (the European/UK/US Military convention)

After a bit of messing about (for reasons I'm not entirely clear on) I can convert this to a number format.

XX/XX/XXXX which the version of Excell I am using will interpret using only US standard convention.

To get this far.
(I have to copy and paste the text dates into a new range, then "find and replace the . with a / to get it to covert to a number.

I've tried all the other date to text stuff I know but this is the only one that works. Sometimes I get away with replacing . with . but not always hence why I use /)


So having done this my 12.11.2009 becomes 12/11/2009 which get interpreted as 11th December 2009.
Not as 12th November 2009 which is the date recorded.

Dates in European convention that cant exist in US standard e.g. 13.12.2009 get interpreted as text I think and the "=Value" thing turns up in the box.

Try as I might I cant get Excel to change the convention. I have tried pre-formating the cells I create the numerical date in for example, but Excel overwrites this with US standard US and interprets accordingly.
If I then change the date format to European it of course changes 12/11/2009 to 11/12/2009.

I'm stumped

Any Ideas anyone.

The problem isnt on my Excel but a colleagues my Excel is fine.

I would attach a file with the problem but I'm not sure it would have it on another computer.

So having done this my 12.11.2009 becomes 12/11/2009 which get interpreted as 11th December 2009.
Not as 12th November 2009 which is the date recorded.

I think the language which Windows is using is English (United States)... Try changing it to English (United Kingdom) to make it work...

Start>Control Panel>Regional and Language Settings

Hope this helps

Regards
Mohit

If you need a formula solution, you can try =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

Thanks GUYS I'll try changing the English used on the file first. Then try text to columns and finally the formula route.