software

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

quot;Stevequot; gt; a écrit dans le message de news:
...
gt; Hi i'm not to hot on Macro's but can anyone tell me if its possible to
gt; write
gt; a macro in excel to do the following?
gt; I have a column in a spreadsheet that lists the date but in this format
gt; 20060404, 20060405, 20060406 etc
gt; the cells are formatted as number but i'd like them to be date and read in
gt; uk format
gt; 04/04/2006, 05/04/2006. 06/04/2006.
gt; Is it possible to change this for each row in 1 go as there at 25,000 rows
gt; and will take me forever manually?
gt;
gt; Many thanks,
gt; Steve
gt;
gt;
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.

quot;Stevequot; wrote:

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

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) amp; quot;/quot; amp; right(a1,2) amp; quot;/quot; amp; left(a1,4)

Where a1 is the numeric you are referring to
--
HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758quot;Stevequot; wrote:

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

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

quot;Stevequot; wrote:

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

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

--

Dave Peterson

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

Steve

quot;Barb Reinhardtquot; wrote:

gt; You can do this with a formula and a helper column.
gt;
gt; Let's say your date is in cell A1
gt;
gt; Use this to convert to a date
gt;
gt; =DATE(VALUE(LEFT(A1,4)),VALUE(MID(A1,5,2)),VALUE(R IGHT(A1,2)))
gt;
gt; You can change the format as you want.
gt;
gt; quot;Stevequot; wrote:
gt;
gt; gt; Hi i'm not to hot on Macro's but can anyone tell me if its possible to write
gt; gt; a macro in excel to do the following?
gt; gt; I have a column in a spreadsheet that lists the date but in this format
gt; gt; 20060404, 20060405, 20060406 etc
gt; gt; the cells are formatted as number but i'd like them to be date and read in
gt; gt; uk format
gt; gt; 04/04/2006, 05/04/2006. 06/04/2006.
gt; gt; Is it possible to change this for each row in 1 go as there at 25,000 rows
gt; gt; and will take me forever manually?
gt; gt;
gt; gt; Many thanks,
gt; gt; Steve
gt; gt;
gt; gt;