PDA

View Full Version : Please! I need help re-arranging date format



badflyer
08-29-2006, 06:44 AM
I have a spreadsheet in excel 2003 with about 100 rows in column A. I need to rearrange the date format from yyyymmdd eg 20060902 to dd/mm/yyyy.
Can someone help me with a macro to do this in bulk. I am ok to put the re-arranged dates in Column B. I can't figure it out! many thanks

uksrogers
08-29-2006, 07:26 AM
I did something very similar to this the other day but used formulae instead of a macro. I did it over 4 columns
if the date was in cell A1, column B was day which had the formula =right(A1,2)
Column C was month which was =mid(a1,5,2)
Column D was year which was =left(a1,2)

I then combined them all together in Column E with a formula as follows
=E1 & "/" & F1 & "/" & G1.

Once you have that formula it can be copied to each required cell and it should work it all out for you.

HTH

uksrogers

Norie
08-29-2006, 08:18 AM
No need for code.

Select the column with the dates, goto Data>Text to columns... and select YMD on the last step.

That should convert the values to 'real' dates which you can then format as required.

uksrogers
08-29-2006, 08:26 AM
No need for code.

Select the column with the dates, goto Data>Text to columns... and select YMD on the last step.

That should convert the values to 'real' dates which you can then format as required.

Nice.. had no idea you could use text to columns for that...

badflyer
08-29-2006, 10:49 AM
Thanks Gents !!