format colum for identity 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

 

[merjet]

Assuming a string of numbers in cell A1, use the following formula.
D1: =MID(A1,1,6) & " " & MID(A1,7,4) & " " & MID(A1,11,2) & " " &
MID(A1,13,1)
E1: =INT(INT(TODAY()-DATE(1900+MID(A1,1,2),MID(A1,3,2),MID(A1,5,2)))/
365.25)
You may need to modify the formats.

Hth,
Merjet