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" <gkaspen@discussions.microsoft.com> wrote in message
news:1BAA01DB-4EE3-4790-9E2F-6F698507C463@microsoft.com...
> 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"
<gkaspen@discussions.microsoft.com> 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.........

Data>Text to Columns>Fixed Width>Next>Column Data Format>Date 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"
<gkaspen@discussions.microsoft.com> 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"
> <gkaspen@discussions.microsoft.com> 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"
<gkaspen@discussions.microsoft.com> 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 <ronrosenfeld@nospam.org>
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