Help with Macro

[Guest]

Hi i'm not to hot on Macro's but can anyone tell me if its possible to write
a macro in excel to do the following?
I have a column in a spreadsheet that lists the date but in this format
20060404, 20060405, 20060406 etc
the cells are formatted as number but i'd like them to be date and read in
uk format
04/04/2006, 05/04/2006. 06/04/2006.
Is it possible to change this for each row in 1 go as there at 25,000 rows
and will take me forever manually?

Many thanks,
Steve

 

[Ardus Petus]

Say your data is in column A.
In column B, enter:
=DATE(LEFT(A1,4),MID(A1,4,2),RIGHT(A1,2))
fill down formula.
Then you can copy/paste special values from colB to col A.

HTH

 

[Guest]

You can do this with a formula and a helper column.

Let's say your date is in cell A1

Use this to convert to a date

=DATE(VALUE(LEFT(A1,4)),VALUE(MID(A1,5,2)),VALUE(RIGHT(A1,2)))

You can change the format as you want.

 

[Guest]

You do not need a macro for this. You can use the following formula in an
adjacent cell and copy it.

=mid(a1,5,2) & "/" & right(a1,2) & "/" & left(a1,4)

Where a1 is the numeric you are referring to

 

[Guest]

You don't need to do a macro, you can solve it with worksheet functions. If
the strange dates are in column A, you can have the following formula in
column B and then format that column as date.
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

/Marcus

 

[Dave Peterson]

If you select that column of numbers, you can use
Data|text to columns
Choose Fixed width (and remove any lines that excel guessed)
choose ymd (I think your numbers are ymd???) as the field type.

If you need a macro, you could record one when you do it manually.

 

[Guest]

many thanks thats a great help i'll give it a go.

Steve