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 for someone older than 100!

 

For those of you who are interested in how this works, the underlined part of the Formula compares the 2-digit year of the ID Number with the 2-digit year of the current year. If it’s less (or equal), it assumes the ID Number belongs to a Millennial and creates a 4-digit year by prefixing the year with 20, otherwise it prefixes with 19.

 

As you can see in the example, an ID Number starting with 19 or less will assume a Millennial. By using the Today() function, this value will dynamically change as we progress through the years.

 

 

 

Share on Facebook
Share on Twitter
Please reload

Featured Posts

Excel Tip – The Format of an Inserted Column

January 28, 2020

1/1
Please reload

Recent Posts