extract numbers, convert to date
 

I imported a comma delimined .txt file into Excel. One column is formated
(for example) 070794MEFKMAG. This is a person's birthday (mmddyy) and then
parts of their name. I want to extract the birthdate numbers, convert them to
a date and subtract that date from todays date. I've been able to extract the
numbers [=MID(A12,ROW($1:$9),6)] giving me 070794, but haven't been able to
convert them to any date that makes sense.

Any suggestions

I used the following formula =TODAY() -
CONCATENATE(MID(F11,1,2),"/",MID(F11,3,2),"/",MID(F11,5,2))

F11 = 070794

I hope that helps

"gkaspen" wrote:

I imported a comma delimined .txt file into Excel. One column is formated
(for example) 070794MEFKMAG. This is a person's birthday (mmddyy) and then
parts of their name. I want to extract the birthdate numbers, convert them to
a date and subtract that date from todays date. I've been able to extract the
numbers [=MID(A12,ROW($1:$9),6)] giving me 070794, but haven't been able to
convert them to any date that makes sense.

Any suggestions

=DATE(MID(A12,5,2),MID(A12,1,2),MID(A12,3,2))

assuming the numbers are always 6 digits


--

Regards,

Peo Sjoblom


"gkaspen" wrote in message
...
I imported a comma delimined .txt file into Excel. One column is formated
(for example) 070794MEFKMAG. This is a person's birthday (mmddyy) and then
parts of their name. I want to extract the birthdate numbers, convert them
to
a date and subtract that date from todays date. I've been able to extract
the
numbers [=MID(A12,ROW($1:$9),6)] giving me 070794, but haven't been able
to
convert them to any date that makes sense.

Any suggestions

On Tue, 1 Mar 2005 11:45:06 -0800, "gkaspen"
wrote:

I imported a comma delimined .txt file into Excel. One column is formated
(for example) 070794MEFKMAG. This is a person's birthday (mmddyy) and then
parts of their name. I want to extract the birthdate numbers, convert them to
a date and subtract that date from todays date. I've been able to extract the
numbers [=MID(A12,ROW($1:$9),6)] giving me 070794, but haven't been able to
convert them to any date that makes sense.

Any suggestions


To convert the above string into a date, assuming your regional settings are
US:

=--TEXT(LEFT(A1,6),"00\/00\/00")

So if you want, for example, age in years:

=DATEDIF(--TEXT(LEFT(A1,6),"00\/00\/00"),TODAY(),"y")




--ron

If data is consistent.........

DataText to ColumnsFixed WidthNextColumn Data FormatDate DMY or MDY for
column 1.

Select Column 2 and Skip.

Finish.

Now you have dates. Do your subtraction or use the Datedif Function which
Chip Pearson has instructions for.

http://www.cpearson.com/excel/datedif.htm


Gord Dibben Excel MVP


On Tue, 1 Mar 2005 11:45:06 -0800, "gkaspen"
wrote:

I imported a comma delimined .txt file into Excel. One column is formated
(for example) 070794MEFKMAG. This is a person's birthday (mmddyy) and then
parts of their name. I want to extract the birthdate numbers, convert them to
a date and subtract that date from todays date. I've been able to extract the
numbers [=MID(A12,ROW($1:$9),6)] giving me 070794, but haven't been able to
convert them to any date that makes sense.

Any suggestions

Ron,

Thanks, that did exactly what I needed done. But I'm curious what the
forward AND backward slashes in the date format does?

Greg

"Ron Rosenfeld" wrote:

On Tue, 1 Mar 2005 11:45:06 -0800, "gkaspen"
wrote:

I imported a comma delimined .txt file into Excel. One column is formated
(for example) 070794MEFKMAG. This is a person's birthday (mmddyy) and then
parts of their name. I want to extract the birthdate numbers, convert them to
a date and subtract that date from todays date. I've been able to extract the
numbers [=MID(A12,ROW($1:$9),6)] giving me 070794, but haven't been able to
convert them to any date that makes sense.

Any suggestions


To convert the above string into a date, assuming your regional settings are
US:

=--TEXT(LEFT(A1,6),"00\/00\/00")

So if you want, for example, age in years:

=DATEDIF(--TEXT(LEFT(A1,6),"00\/00\/00"),TODAY(),"y")




--ron

On Tue, 1 Mar 2005 13:19:03 -0800, "gkaspen"
wrote:

Ron,

Thanks, that did exactly what I needed done. But I'm curious what the
forward AND backward slashes in the date format does?

Greg

From HELP for number formatting:

Displaying both text and numbers To display both text and numbers in a cell,
enclose the text characters in double quotation marks (" ") or precede a single
character with a backslash (\).

It is simpler (and requires fewer characters) to precede the desired character
(/) with a backslash.

The backslash, of course, is the separator for the portions of the date.

The equivalent, without the backslashes, would be:

=--TEXT(LEFT(A1,6),"00""/""00""/""00")


--ron

On Tue, 01 Mar 2005 19:52:16 -0500, Ron Rosenfeld
wrote:

The backslash, of course, is the separator for the portions of the date.

I had that mixed up, of course. But you get the point.


--ron