date function
 

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.

date function
 

Pam,

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

=DATE("20"&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Pam Coleman" wrote in
message
...
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.

date function
 

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,"-",""),"\2\000\/00\/00")
format as date and drag down.



Pam Coleman wrote:

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.

--

Dave Peterson

date function
 

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 FormatCellsCustom.
2. Type in: yyyymmdd
3. Click on OK.


--
neopolitan


------------------------------------------------------------------------
neopolitan's Profile: http://www.excelforum.com/member.php...nfo&userid=611
View this thread: http://www.excelforum.com/showthread...hreadid=504117

date function
 

What 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,

"Chip Pearson" wrote:

Pam,

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

=DATE("20"&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Pam Coleman" wrote in
message
...
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.

date function
 

Maybe just:
"20"&substitute(a1,"-","")



Pam Coleman wrote:

What 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,

"Chip Pearson" wrote:

Pam,

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

=DATE("20"&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Pam Coleman" wrote in
message
...
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.




--

Dave Peterson