extract numbers, convert to date

[Guest]

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

 

[Guest]

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

 

[Peo Sjoblom]

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

assuming the numbers are always 6 digits

 

[Ron Rosenfeld]

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

 

[Gord Dibben]

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

 

[Guest]

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

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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