extract numbers, convert to date

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
=DATE(MID(A12,5,2),MID(A12,1,2),MID(A12,3,2))

assuming the numbers are always 6 digits
 
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
Click to expand...

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

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
Click to expand...
 
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
Click to expand...

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
 
Back
Top