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”
- Step 2 – Click on the position where you want the break line to be
- Step 3 is the crucial step to make it work.
- Click on the first column in “Data preview”
- Select “Date: YMD” under Column data format (date format according to your case)
- Click on the second column in “Data preview”
- Select “Do not import column (Skip).” – Assume we do not need it.
Finish
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.
wmfexcel 