Formatting Dates/Times

[smallsmallman]

I have about 20,000 cells of info with date/times. The date is in the format:

01 Jan 2009 13:00:00:000

I need the date to be in this format in order for other cells to recognise it:

01/01/2009 13:00

Making this format by dragging down the little black box won't be any use because there are a load of dates missing. Any suggestions?

 

[barry houdini]

If you have data starting at A2 try this formula in B2

=SUBSTITUTE(A2,":",".",3)+0

That should convert to a valid date/time serial number - format in required date/time format as fill formula down column

 

[smallsmallman]

barry you legend of a man, i got it to work and sorted the date out fine but now all i seem to be getting is 39814 when i try to change this date:

01 Jan 2009 00:00:00:000

i'm probably just being a idiot but whats goin on there??