Converting US date format to UK format

[Swaroon]

Hi,

I have a report which shows the date in US format eg. 10-28-2007

I want to convert it to UK format 28-Oct-07 but it isn't being recognised as a date but a text.

I can use Mid(4,2)&"-"&Left(2)&"-"&Right(4) to get 28-10-2007 but it still doesn't recognise it as a date so can't be formatted as 28-Oct-07.

Any ideas,

thanks
Steve

 

[Andrew Poulsom]

Try selecting the column, choosing Data|Text to Columns and selecting Date MDY at Step 3 of the wizard.

 

[Swaroon]

Thanks, I tried this and nothing happened, it still kept the US format.

 

[Andrew Poulsom]

I just tried it with the text entry 10-28-2007 in A1 and got 39383 or 28/10/2007 in dd/mm/yyyy format? Are your entries text or numbers? You can check with the ISNUMBER function.

 

[Richard Schollar]

I would have thought TextToColumns would work, but a formula you could use would be (basically same as yours, with addition of plus 0 on the end):

Excel Workbook
	A	B
1	10-28-2007	28 October 2007
Sheet1

 

[Swaroon]

Interesting, I can access excel on both a UK and a US server, when I try the UK server, I get false to the ISNUMBER and the text to col didnt work, when I tried the US server, it all worked.
When I copied from the US to UK server, the formatting came across fine.

 

[Swaroon]

Thanks for the formula, it worked !

 

[barry houdini]

=MID(A1&"-"&A1,FIND("-",A1,4)+1,LEN(A1))+0

I like that one, Richard

 

[Richard Schollar]

=MID(A1&"-"&A1,FIND("-",A1,4)+1,LEN(A1))+0

I like that one, Richard

I had a good teacher, Barrie