Excel again :)

[Adam]

Having another blonde Excel moment.

​I have a column with dates in which is listed like below..

Date

Sep 03 2012

Sep 04 2012

Sep 04 2012

Sep 04 2012

Sep 04 2012

Sep 04 2012

Sep 04 2012

Sep 04 2012

Sep 04 2012

Sep 04 2012

Sep 04 2012

Sep 05 2012

Sep 05 2012

Sep 05 2012

Sep 05 2012

Sep 06 2012

Sep 06 2012

Sep 06 2012

Sep 06 2012

Sep 06 2012

How do I change this so it shows as 03/09/2012 etc.

The obvious formatting method doesn't work, I guess it's to do with how the date was pasted into Excel, but not sure!

 

[unofficialPete]

I'm guessing they are General format and are infact text?

In that case as a bodge I would try and use Replace, replace Sep with 09 and then replace spaces with /, then try and change the format again.

Pres Ctrl+F to open the Find box and tab to replace. someone might come up with a better solution though...

edit: tested this method and it worked in Excel 2010!

 

[Adam]

Yeah sort of. It's the source of the data from one of our systems, when copied into Excel it goes all strange lol.

​I will have a play around and see how I get on.

 

[Adam]

Yeah I can get it to look like this.. 09/03/2012 but that's American, I want it British but using the date formatting does nothing.

 

[The Boosh!]

I'd use text functions to arrange it as a UK date, C&P special values then format it as a date.

i.e to get 09/03/2012 into UK date it would be =mid(a1,4,2)&"/"&left(a1,2)&"/"&right(a1,4)

or you could do it all in one function...

=date(right(a1,4),left(a1,2),mid(a1,4,2))

It's difficult togive you a proper solution without seeing the data though, especially when linking to/from databases, as excel can do some strange things which need you to do a bit of fudging.

 

[Adam]

Sorted with a bit of bodging... using text formulas like, "Left", "Right", "Mid", "Concatenate"

 

[The Boosh!]

Lol, well done!

 

[Adam]

Haha... thanks