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?

--
Kind regards,

Niek Otten

"Bill" <[email protected]> wrote in message
news:e%23WAJ$%23%[email protected]...
>
> 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.
>

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




"Niek Otten" <[email protected]> wrote in message
news:u$ddMZ$%[email protected]...
> 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.
>>
>
>

[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