Convert date and time in single column

[splats]

Hello

I have a column in excel 2003 that displays date and time such as 22.08.2008 00:25:00 (dd.mm.yyyy hh:mm:ss) I need it to convert to this format though (dd mmm yyyy hh:mm:ss) e.g 22 Aug 2008 00:25:00

I would like to create a macro to do this so that we can recreate this automatically or even have a code that does it for me.

I have already tried splitting up the date and time into separate columns and then rejoin them together again.

Column D=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))
Column E=TIME(MID(A1,12,2), MID(A1,15,2), RIGHT(A1,2))
Column F=D1 + E1

This works although the file size is huge afterwards. (9 MB from source file of 1 MB)

any ideas would be greatly appreciated.

Thank you

TinaT

 

[NWBeaver]

As I posted in the MS Office Forum, one line of code should do it.
The following example formats column D on sheet 1.

Sub Date_Format()
Sheet1.Columns("D").NumberFormat = "dd mmm yyy hh:mm:ss"
End Sub

 

[SkipVought]

Hi,

" I need it to convert to this format though "

Date/Time are just NUMBERS, like right now in Fort Worth, Texas it is 39736.76298.

Format that ANY WAY YOU LIKE via Format > Cells > Number and choose a stoc date time format OR custom format as you wish. the number does not change. just the format.

YourCell.NumberFormat = "dd mmm yyyy hh:mm:ss"

Forget all that string manipulation! It's unnecessary!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}