Excel Tip - Validating an SA ID Number

Its pretty well known that the first 6 digits of your South African ID Number is your Date of Birth. Un-beknown to many though, the SA ID Number has a few other Personal Details hidden within its 13 digits! This is the breakdown {YYMMDD}{G}{SSS}{C}{A}{Z} YYMMDD: Date of birth G: Gender. 0-4 Female; 5-9 Male. SSS: Sequence No. for DOB/G combination. C: Citizenship. 0 SA; 1 Other. A: Usually 8, or 9 (can be other values) Z: Control digit. Using this information and some neat Excel Formulas we can maximise the use of the ID Number to Verify and/or extract this key Personal Info, as well as validate the ID Number itself. Click here to Download this handy Spreadsheet that will enable you to do al

Excel Tip - Catering for Millennials in D.O.B. from an ID Number

As more and more Millennials creep into our Databases, it’s important that we beef up our Formula that extracts a Date of Birth from an ID Number to accommodate them. With just a 2-digit year, the Excel Date function assumes the 1900s. As a result, the ID Number of someone born after the year 2000 converts to a D.O.B. one hundred years earlier! The following Formula addresses this issue. =DATE(CONCAT(IF(VALUE(LEFT(A2,2))<=VALUE(TEXT(TODAY(),"YY")),"20","19"),LEFT(A2,2)),MID(A2,3,2),MID(A2,5,2)) Simply copy and paste the above formula into cell B2 of an Excel Spreadsheet, pop any ID Number into cell A2 and it will extract the D.O.B. The only time it will not work correctly now is for someone

Training Offices:


106 Johan Avenue, Sandton


1 Tamarind Close, Umhlanga


400 Old Howick Road, Hilton

Corporate Onsite Training Available Countrywide

  • Summit Solutions Excel Training
  • Summit Solutions Excel Training
  • Summit Solutions Excel Training

Tel: 086 167 3923


Head Office: Hilton Quarry Office Park, 400 Old Howick Road, Hilton.

2020 | Copyright | Summit Solutions