SUBSCRIBE FOR FREE EXCEL TIPS

Catering for Millennial's when extracting D.O.B from an ID

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(NUMBERVALUE(LEFT(A2,2))<=NUMBERVALUE(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

Training Offices:

Johannesburg

106 Johan Avenue, Sandton

Durban

1 Tamarind Close, Umhlanga

Pietermaritzburg

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

Email: info@summitsolutions.co.za 

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

2020 | Copyright | Summit Solutions