US dates to UK??

[Bill]

I have an Excel 2003 spreadsheet that has a column full of dates. I have no
control over how the spreadsheet has been configured.

The dates are in an American format but I am in the UK and my laptop is
configured for uk dates.

I have noticed that cells that contain dates that would be illegal in uk
format i.e. '10/29/04 10:36' are formatted 'General'

Dates that are legal in the uk format i.e. 12/8/05 11:23 are formatted
'Custom dd/mm/yyyy hh:mm'.

I have failed to get the dates to read in the uk format i.e. using the above
examples:-

10/29/04 needs to be 20 April 2004 and
12/8/05 needs to be 8 December 2005.

In the first example I cannot extract a serial because it is seen as text.
In the second any conversion is coming out as 12 August 2005.

I am not worried about the time, I just need the date.

I know that I am probably missing something silly here but ban anyone give
me a pointer on how to get these dates to read as I need please.

Regards.
Bill.

 

[Vito]

Try:

=TEXT(A1,"dd mmmm yyyy"

 

[Niek Otten]

Hi Bill,

It looks like the dates were already no "real" Excel dates; otherwise they
would have been converted (not really, shown actually) as UK dates.
Did you import the dates? If so, probably as a .csv file. Better do that as
a .txt file; that gives you the opportunity to tell Excel what type of dates
they are

Your example: 10/29/04 needs to be 20 April 2004 was probably not right?

 

[Bill]

Sorry Niek, the 20 April 2004 was a complete mess up, it should read 29
October 2004.

You are right about the csv file element. I have a downloaded vbs script to
convert a log file that is in a very poor format to a csv file that can be
opened and in Excel and far better formatted. Hence I cannot change how it
is doing things.

Will try renaming the file as txt as you suggest and see what happens.

Regards.
Bill.

 

[vezerid]

Bill,
if your date/times are imported as text, then the following formula
will do (assuming dates are past Y2K).
=DATE(VALUE(20&MID(A1,FIND("/",A1,FIND("/",A1)+1)+1,FIND("
",A1)-FIND("/",A1,FIND("/",A1)+1)-1)),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1))

HTH
Kostis Vezerides