How to convert dates entered as text to values

[Michael]

I have a column of dates in an excel table entered as
text in the following format YYMMDD - example: '040527'
is May 27, 2004 and '040430' is April 30, 2004. They are
entered as text (I think these were captured from a
mainframe via screencapture)

I'd like to find a convenient way to convert these into
serial numbers so that I can work with them.

DATEVALUE doesn't like this format...

 

[Jason Morin]

One way:

=(RIGHT(REPLACE(A1,5,,"-"),5)&"-"&LEFT(A1,2))*1

Format as date.

HTH
Jason
Atlanta, GA

 

[Dana DeLouis]

If your text does not have the trailing 'mark (ie just the text 040527),
then one way is to use <Data><Text to Column>. On step 3, select "Date",
and select the "YMD" format.

HTH
Dana DeLouis

 

[Harlan Grove]

One way:

=(RIGHT(REPLACE(A1,5,,"-"),5)&"-"&LEFT(A1,2))*1

...

Can be done with just two function calls without fubarring in locales in which
the default date format is dd-mm-yy.

=--("20"&REPLACE(A1,3,2,"-"&MID(A1,3,2)&"-"))

 

[Gord Dibben]

Michael

Data>Text to Columns>Next>Next "Column data Format">Date>YMD>Finish.

To see serials, format as general or number.

Gord Dibben Excel MVP

 

[Michael]

You da man - worked like a charm! Thanks much.

-----Original Message-----
One way:

=(RIGHT(REPLACE(A1,5,,"-"),5)&"-"&LEFT(A1,2))*1

Format as date.

HTH
Jason
Atlanta, GA

.

 

[Dana DeLouis]

A slight variation to Harlan's excellent idea might be the following,
assuming years are 2000+

=--("20" & TEXT(A1,"00-00-00"))

Dana DeLouis

 

[Harlan Grove]

A slight variation to Harlan's excellent idea might be the following,
assuming years are 2000+

=--("20" & TEXT(A1,"00-00-00"))

....

For that matter,

=--TEXT(A1,"\2\000-00-00")

 

[Dana DeLouis]

Even more excellent! Thanks. :>)

Looks like it may even work like this, but your idea is probably safer.
=--TEXT(A1,"2000-00-00")

Dana DeLouis
(Win XP & Office XP)