Format colum for number and next colum age

[pswanie]

Im from south africa. we use a ID number system. it looks and consist of the
following

800506 5001 05 2
The user will enter the 13 numbers without space or any thing. What i need is:

====================================================
colum d to automaticly put a space after the first 6 numbers and a space
after the next four numbers and then after the next to numbers ie:

8005065001052 = 800506 5001 05 2

then
colum e i need to deduct the first six numbers from todays date becouse the
first six numbers represent the date of birth that is 1980-05-06

so colum e will then show the persons age ie: 28
=====================================================

do appreciate

thanx

 

[Rick Rothstein \(MVP - VB\)]

For your first question, select the entire column, right click it and choose
Format Cells from the popup menu that appears, choose the Number tab on the
dialog box that appears, select Custom in the Category list and copy/paste
this in the Type field...

000000 0000 00 0

The answer for your second question is a little more problematic because you
are only using 2-digit years. Assuming no one will ever be more than 100
years old, try this formula...

=DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))

where I have assumed your ID number is A1 (adjust the formula accordingly).

Rick

 

[Rick Rothstein \(MVP - VB\)]

Sorry, on your second question, I only gave you part of the formula. This is
what I should have posted...

=DATEDIF(DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2)),TODAY(),"y")

Rick

For your first question, select the entire column, right click it and
choose Format Cells from the popup menu that appears, choose the Number
tab on the dialog box that appears, select Custom in the Category list and
copy/paste this in the Type field...

000000 0000 00 0

The answer for your second question is a little more problematic because
you are only using 2-digit years. Assuming no one will ever be more than
100 years old, try this formula...

=DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))

where I have assumed your ID number is A1 (adjust the formula
accordingly).

Rick

 

[pswanie]

All right!!!

the second part works perfect.. thanx alot.

on the first Q & A though i got a problem.... that is how i normaly would
do it. i am using vista and xp2000.
for some odd reason when i try using that it end up as follows

0 000 000 000 000

if however i format the column/cell as

###-###-###-#

it ends up as 000000-0000-000-0

have no idea why it acts up like this....

phillip

For your first question, select the entire column, right click it and
choose Format Cells from the popup menu that appears, choose the Number
tab on the dialog box that appears, select Custom in the Category list and
copy/paste this in the Type field...

000000 0000 00 0

The answer for your second question is a little more problematic because
you are only using 2-digit years. Assuming no one will ever be more than
100 years old, try this formula...

=DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))

where I have assumed your ID number is A1 (adjust the formula
accordingly).

Rick

 

[pswanie]

Jip i choped and changed it a little bit when i saw the answer on my
spreadsheet. got one collum to show ur firs answer and made another colum
to show just the age of the person

thanx

pswanie

Sorry, on your second question, I only gave you part of the formula. This
is what I should have posted...

=DATEDIF(DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2)),TODAY(),"y")

Rick

 

[Rick Rothstein \(MVP - VB\)]

I'm glad the second one worked for you, but I don't know what to tell you
about the problem you are describing on the first one. I'm using Vista and
XL2003 and the Custom Cell Format works as it should. I can't even begin to
figure out how Excel is changing this...

000000 0000 00 0

to this...

0 000 000 000 000

Nor how it could change this...

###-###-###-#

to this...

000000-0000-000-0

Hopefully, someone more knowledgeable in these things will read this thread
and be able to provide an answer (although I can't imagine what that could
possibly be).

Rick

All right!!!

the second part works perfect.. thanx alot.

on the first Q & A though i got a problem.... that is how i normaly would
do it. i am using vista and xp2000.
for some odd reason when i try using that it end up as follows

0 000 000 000 000

if however i format the column/cell as

###-###-###-#

it ends up as 000000-0000-000-0

have no idea why it acts up like this....

phillip