Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
format colum for identity number and next colum age
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
format colum for identity number and next colum age
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding two colum cells only if there is data in 3rd colum | Excel Worksheet Functions | |||
Keeping a sum colum correct after inserting a colum of data in fro | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Check data on colum A and find match on colum b | Excel Discussion (Misc queries) | |||
lookup in colum a and compare values in colum b | Excel Worksheet Functions |