date function

P

Pam Coleman

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.
 
C

Chip Pearson

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


message
news:[email protected]...
 
D

Dave Peterson

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.
 
N

neopolitan

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 Format>Cells>Custom.
2. Type in: yyyymmdd
3. Click on OK.
 
P

Pam Coleman

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

Dave Peterson

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



Pam said:
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,
Click to expand...
 
Top