reformat date and time in one cell in excel

[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

 

[AnotherHiggins]

Can't you just reformat the column?

Format > Cells > Number > Custom > dd mmm yyyy hh:mm:ss

If not, then the problem is that you currently have TEXT, not a DATE!

To convert it, you can use
[tab][COLOR=blue white]=Value(Substitute(F1, ".", "/"))[/color]
and then reformat the cell.

For code, you can turn on your macro recorder and do that, then observe the code that is generated.

If you need help with a macro/VBA-specific solution, please repost in forum707.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Gavona]

Or, if you do have a date but for some reason need to convert it to Text then have a look at the Text function.

If you do need to use Value or Text then you may want to convert the results from formulae to values and then delete the original data.

Select the range containing the formulae
Edit,Copy
Edit,PasteSpecial,values

Gavin

 

[NWBeaver]

One line of code will do it.
The following example formats column D on sheet1.

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

 

[AnotherHiggins]

NWBeaver: That assumes that we're starting with a number.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.