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 |
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 |
All times are GMT +1. The time now is 10:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com