US dates to UK??
 

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.

US dates to UK??
 

Try:

=TEXT(A1,"dd mmmm yyyy")


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=491783

US dates to UK??
 

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?

--
Kind regards,

Niek Otten

"Bill" wrote in message
.. .

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.

US dates to UK??
 

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.




"Niek Otten" wrote in message
...
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?

--
Kind regards,

Niek Otten


Bill.

US dates to UK??
 

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