Extract 6 digits from a string and convert it into date

Got a question about how to extract the date of birthday (DOB) portion from an ID number like 63102400965, where the first 6 digits represent the DOB, so that we could use the date for other calculation.  In this example the DOB is Oct 24, 1963.

To solve this question, the first thing on my mind is the formula approach I described in the post Convert an 8-digit number into Excel-recognizable Date.  Soon enough, I realized that I am dealing with 6 digits only, where we need to fix another issue of the year… where 63 seems logical to be 1963; and 10 seems logical to be 2010.  It means I need a longer formula to entertain the year portion, like if first two digits is less than or equal to 29, then it should be the 21st century…

Wait, why do I need to reinvent the wheel?  Let’s do it a non-formula approach – Text to Columns.

First of all, copy the column with the ID numbers we have.  Select the “copied” range and perform the following steps:

Go to Data –> Text to Columns

  • Step 1 – Select “Fixed width”Excel Tips - Text to Columns
  • Step 2 – Click on the position where you want the break line to beExcel Tips - Text to Columns 1
  • Step 3 is the crucial step to make it work.
  1. Click on the first column in “Data preview”
  2. Select “Date: YMD” under Column data format (date format according to your case)Excel Tips - Text to Columns 2
  3. Click on the second column in “Data preview”
  4. Select “Do not import column (Skip).”  – Assume we do not need it.Excel Tips - Text to Columns 3


Excel Tips - Text to Columns 4

As simple as that. 🙂

  • Pros: Easy, Quick, No formula required.
  • Cons: Result is static.  Good for one off solution only.

Note: By default, when year is input as 2 digit number, Excel treats 01-29 as 20xx; 30-99 as 19xx.  Please read What we need to know about inputting Date in Excel? to learn more about the basic of date input in Excel.


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.