Need macro to reformat cell, but leave value alone

[bufo0892]

I'm trying to reformat a cell with value 20100329163030, which is a date. I select the cell & record the following as a macro: F2, Home, right 4, /, right 2, /, move right 2, space, right 2, :, right 2, :, Enter.


So I end up with a field looking like: 2010/03/29 16:30:30. That's exactly what I want, but I want the macro to reformat any date. However, the macro records the value along with the format change.


So if I have a column with 10 unique dates, I would like the macro to reformat each cell without changing the date. Anyone know how to do this?

 

[Hui]

Have you thought about adding another column with a formula like

=+DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))

assumes your number 20100329163030 is in A1

 

[oldchippy]

Hi,


Just adding to Hui's answer, you would then need to Custom format the cell to

yyyy/mm/dd hh:mm:ss

 

[bufo0892]

Hui, Old Chippy,

Thanks very much for this. I'm not familiar at all with manipulating dates in Excel beyond just changing the format thru the menu. Have never used any date formulas, so I had not considered the approach that Hui offered. I was thinking I could create a mask of some kind in an adjacent column, but couldn't figure out how.

This solution will work just fine, so thanks again.