Excel again :)

[Adam]

Having another blonde Excel moment.

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

[TABLE="width: 200"]
[TR]
[TD]Date[/TD]
[/TR]
[TR]
[TD] Sep 03 2012[/TD]
[/TR]
[TR]
[TD] Sep 04 2012[/TD]
[/TR]
[TR]
[TD] Sep 04 2012[/TD]
[/TR]
[TR]
[TD] Sep 04 2012[/TD]
[/TR]
[TR]
[TD] Sep 04 2012[/TD]
[/TR]
[TR]
[TD] Sep 04 2012[/TD]
[/TR]
[TR]
[TD] Sep 04 2012[/TD]
[/TR]
[TR]
[TD] Sep 04 2012[/TD]
[/TR]
[TR]
[TD] Sep 04 2012[/TD]
[/TR]
[TR]
[TD] Sep 04 2012[/TD]
[/TR]
[TR]
[TD] Sep 04 2012[/TD]
[/TR]
[TR]
[TD] Sep 05 2012[/TD]
[/TR]
[TR]
[TD] Sep 05 2012[/TD]
[/TR]
[TR]
[TD] Sep 05 2012[/TD]
[/TR]
[TR]
[TD] Sep 05 2012[/TD]
[/TR]
[TR]
[TD] Sep 06 2012[/TD]
[/TR]
[TR]
[TD] Sep 06 2012[/TD]
[/TR]
[TR]
[TD] Sep 06 2012[/TD]
[/TR]
[TR]
[TD] Sep 06 2012[/TD]
[/TR]
[TR]
[TD] Sep 06 2012[/TD]
[/TR]
[/TABLE]

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