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

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
--
AP

"Steve" a écrit dans le message de news:
...
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

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(R IGHT(A1,2)))

You can change the format as you want.

"Steve" wrote:

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

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

Steve

"Barb Reinhardt" wrote:

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(R IGHT(A1,2)))

You can change the format as you want.

"Steve" wrote:

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

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
--
http://HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758


"Steve" wrote:

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

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

"Steve" wrote:

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

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.

Steve wrote:

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

--

Dave Peterson