software

If I have dates listed in a column as 06-0101 etc, how can I format it to
be: 20060101? I have 7000 rows where I need to reformat the date.
Help.

Pam,

Assuming your dates are in column A, put the following formula in
column B

=DATE(quot;20quot;amp;LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.comquot;Pam Colemanquot; gt; wrote in
message
...
gt; If I have dates listed in a column as 06-0101 etc, how can I
gt; format it to
gt; be: 20060101? I have 7000 rows where I need to reformat the
gt; date.
gt; Help.
Are those yy-mmdd
If yes and yy are all in this century, you could use a helper cell and this
formula:

=--TEXT(--SUBSTITUTE(A1,quot;-quot;,quot;quot;),quot;\2\000\/00\/00quot;)
format as date and drag down.
Pam Coleman wrote:
gt;
gt; If I have dates listed in a column as 06-0101 etc, how can I format it to
gt; be: 20060101? I have 7000 rows where I need to reformat the date.
gt; Help.

--

Dave Peterson


You didn't say if the data in your cells is entered as a date and that
the format you currently have displays them as 06-0101 or if this is
just text that you have entered.

If they are truly entered as dates, you can create a custom format:

1. Click on Formatgt;Cellsgt;Custom.
2. Type in: yyyymmdd
3. Click on OK.--
neopolitan------------------------------------------------------------------------
neopolitan's Profile: www.excelforum.com/member.php...nfoamp;userid=611
View this thread: www.excelforum.com/showthread...hreadid=504117What if the first 2 numbers is the year and the other are our complaint number:
02-12345 how could I make it read 200212345? Some of our information this
year is going to contain a complaint number instead of the full date.
Thanks,

quot;Chip Pearsonquot; wrote:

gt; Pam,
gt;
gt; Assuming your dates are in column A, put the following formula in
gt; column B
gt;
gt; =DATE(quot;20quot;amp;LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))
gt;
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt; quot;Pam Colemanquot; gt; wrote in
gt; message
gt; ...
gt; gt; If I have dates listed in a column as 06-0101 etc, how can I
gt; gt; format it to
gt; gt; be: 20060101? I have 7000 rows where I need to reformat the
gt; gt; date.
gt; gt; Help.
gt;
gt;
gt;

Maybe just:
quot;20quot;amp;substitute(a1,quot;-quot;,quot;quot;)
Pam Coleman wrote:
gt;
gt; What if the first 2 numbers is the year and the other are our complaint number:
gt; 02-12345 how could I make it read 200212345? Some of our information this
gt; year is going to contain a complaint number instead of the full date.
gt; Thanks,
gt;
gt; quot;Chip Pearsonquot; wrote:
gt;
gt; gt; Pam,
gt; gt;
gt; gt; Assuming your dates are in column A, put the following formula in
gt; gt; column B
gt; gt;
gt; gt; =DATE(quot;20quot;amp;LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Cordially,
gt; gt; Chip Pearson
gt; gt; Microsoft MVP - Excel
gt; gt; Pearson Software Consulting, LLC
gt; gt; www.cpearson.com
gt; gt;
gt; gt;
gt; gt; quot;Pam Colemanquot; gt; wrote in
gt; gt; message
gt; gt; ...
gt; gt; gt; If I have dates listed in a column as 06-0101 etc, how can I
gt; gt; gt; format it to
gt; gt; gt; be: 20060101? I have 7000 rows where I need to reformat the
gt; gt; gt; date.
gt; gt; gt; Help.
gt; gt;
gt; gt;
gt; gt;

--

Dave Peterson