#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bill
 
Posts: n/a
Default 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.


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Vito
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bill
 
Posts: n/a
Default 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.





  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
PivotTables - Group Dates, excluding dates Todd1 Excel Discussion (Misc queries) 4 December 10th 05 06:06 PM
Calculating number of days between two dates that fall between two other dates [email protected] Excel Discussion (Misc queries) 5 October 26th 05 06:18 PM
Automatic shading of cells based on dates??? Pedros Excel Worksheet Functions 3 October 20th 05 12:35 AM
2 digit year in dates return 19xx not 20xx moranbo Excel Discussion (Misc queries) 1 September 7th 05 01:44 AM
Formating Dates for production schedule dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 09:43 PM


All times are GMT +1. The time now is 03:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"