Shopping Cart

Extracting D.O.B’s from ID’s for milennials

As more and more Millennials creep into our Databases it’s important that we beef up our Formula and learn about extracting D.O.B’s from ID’s.

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 the issue of extracting D.O.B’s from ID’s.

=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 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.

If you found this useful and would like a firmer grasp of the fundamentals, check out our beginner Excel learning course, which will help take your skills to the next level.

Want to learn more?

Receive our Excel tips and news to your inbox!

"*" indicates required fields

Choices*
This field is for validation purposes and should be left unchanged.

Request a Consultation

MS Excel Advanced Course

R 1,550

Course Length: 79 Lessons | 7.5 Hours of Videos

Content Covered

  • Key Essentials
  • Spreadsheet Integrity
  • Cell References
  • Forecasting
  • CSV Import
  • Merging Data
  • Splitting Data
  • Data Validation
  • Worksheet Protection
  • Advanced Formula
  • Text Formula
  • Date Formula
  • Logical Formula
  • Lookup Formula
  • Maths Formula
  • Pivot Tables & Charts
  • Excel Automation (Macros)

MS Excel Intermediate Course

R 1,350

Course Length: 55 Lessons | 5.5 Hours of Videos

Content Covered

  • Key Essentials
  • Series Generation
  • Advanced Copy and Paste
  • Formula
  • Cell Referencing
  • Rounding
  • View Optimization
  • Find and Replace
  • Sort and Filter
  • Graphs
  • Conditional Formatting
  • Ranges vs Tables
  • Pivot Tables
  • VLOOKUP
  • Data Manipulation
  • Keyboard Shortcuts
  • Tips and Tricks

MS Excel Beginner Course

R 1,150

Course Length: 56 Lessons | 4 Hours of Videos

Content Covered

  • Excel Overview
  • Key Essentials
  • Data Input
  • Excel Autofill
  • Formatting Cells
  • Number Formatting
  • Formatting Worksheets
  • Formulas
  • Printing
  • Basic Sort and Filter
  • Visualizing Data
  • Excel’s Golden Rule
  • Pivot Tables Sneak Peek
  • Keyboard Shortcuts
  • Tips and Tricks