OmniDocHow to extract date of birth from ID number using Excel formula
Use MID to intercept the 7th to 14th digits of the date of birth of the ID number, and then use DATE to convert it into a real date. Explain clearly how to write 18-digit and 15-digit numbers, the reasons for error results and the method of checking each item.
Key points of this article
- The 7th to 14th digits of the 18-digit ID card are the date of birth in YYYYMMDD format, which can be intercepted with MID.
- MID intercepts text, which needs to be combined with DATE to become a real date that can be calculated and sorted.
- The year of the 15-digit old number only has two digits, so 1900 must be added as the base number in the formula.
- The number column must be set to text format first, otherwise the last digits will become 0, affecting subsequent verification.
- When DATE encounters a non-existent month and day, it will be automatically postponed, and the result must be verified by reverse verification or sampling.
There is often a column of ID numbers in personnel tables, property registration tables or registration forms. When counting ages, grouping by birth year or filtering birthdays, manually copying them one by one is slow and error-prone. In fact, the number itself already contains the date of birth. Use Excel's text function to intercept it and convert it again to get the result.
This article is written for office workers and form users who need to process ID numbers in batches. After reading, you will get two formulas that can be directly applied, which are suitable for 18-digit and 15-digit numbers respectively. You will also know how to troubleshoot when the result is an error or the date is incorrect.
Which formula should be used to extract the date, month and day of birth from the ID card? One sentence conclusion
Let me give the conclusion first: use MID to intercept the 8 digits starting from the 7th digit of the 18-digit number, and then use DATE to split the year, month, and day combination to form a date; the year of the 15-digit number only has two digits, which must be completed according to the 1900s.
| your situation | Recommended practices | reason |
|---|---|---|
| The entire column is an 18-digit ID number. | Use MID to intercept digits 7 to 14, and then use DATE to convert | The date of birth is fixed and the formula is simple |
| There are 15 old numbers mixed inside and outside the table | First use LEN to determine the length and apply two sets of formulas respectively. | The year of the 15-digit number only has two digits and needs to be processed separately. |
| Only the year is required for segmented statistics | Only intercept the 7th to 10th digits and use VALUE to convert them into numbers. | It is not necessary to get the complete date, the calculation is more straightforward |
| The number column looks normal, but an error is reported | Check for spaces and confirm that the number column is in text format | Both spaces and number formats will cause the interception position to be misaligned or the precision to be lost. |
| Need to sort or filter by age | Convert it to a real date and then use DATEDIF to calculate the age | Only true dates can participate in date calculations |
Before you start: Confirm these points first
- The number of digits in the number. The date of birth of the 18-digit number is in the 7th to 14th digits, and the 15-digit number is in the 7th to 12th digits. The two positions are different. You can use the LEN function to count the length in the next column, for example =LEN(A2). If the result is 18 or 15, you can know which formula to use.
- Cell format. If the number column is treated as a number by Excel, the portion of the 18-digit number that exceeds 15 significant digits will become 0. Date of birth is still within the top 15 digits, but once those numbers are overwritten, it's difficult to recover. It is recommended to select the entire column first, format it as text, and then enter or paste the number. Data that has been converted into numbers cannot be retrieved after changing the format and needs to be re-imported from the original source.
- Whether the data is clean. Numbers copied from web pages, chat records or PDFs often contain leading and trailing spaces or invisible characters, which will cause the intercepted position to be misaligned. You can use the TRIM function to clean it first, such as =TRIM(A2), and then extract it after confirming it is correct.
- Data security. ID number is personal information. When processing, try to operate in the local form, do not upload it to the online conversion website at will, and avoid sending the result table out at will. If the original is a scanned copy, text recognition can be completed locally to reduce data transfer.
- Use of results. If it is only for people to see, just set it to date display; if you want to calculate, filter or sort age, you must get the real date value, not text.
Steps to extract the 18-digit ID birth date in Excel
- Confirm that the ID number is in column A, starting from row 2, and enter the header "date of birth" in column B.
- Enter the formula =DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)) in cell B2. Among them, MID(A2,7,4) intercepts the year, MID(A2,11,2) intercepts the month, MID(A2,13,2) intercepts the day, and DATE combines them into a date.
- Press Enter to confirm. B2 may appear as a string of numbers, which is Excel’s internal date serial number. It is not an error. Just proceed to the next step.
- Select B2, set the cell format to date, select the year-month-day style, and the numbers will be displayed as readable dates.
- Double-click the fill handle in the lower right corner of B2, and the formula will automatically copy down to the entire column, with each row corresponding to a person's birth date.
- If there are 15 mixed inside and outside digit number, you can use the combined judgment formula instead: =IF(LEN(A2)=18,DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)) ,IF(LEN(A2)=15,DATE(1900+MID(A2,7,2),MID(A2,9,2),MID(A2,11,2)),"Number length is abnormal")). In this way, lines with abnormal lengths will be directly marked to facilitate individual verification.
- When you need to count the age, enter =DATEDIF(B2,TODAY(),"Y") in C2 to get the age. TODAY will change with the opening date, and the results may be different each time the table is opened. If you need to fix it, you can write a certain base date into the formula to replace TODAY.
Check each item after completion
| Inspection items | How to see it | What should you do if there's a problem? |
|---|---|---|
| Whether the date is correct | Randomly select 5 to 10 rows and manually confirm them against the original digits 7 to 14 | If the entire misalignment occurs, check the starting position and number of characters of the MID |
| Whether the cell is still a string of numbers | Check if it shows the date | Set this column to date format |
| Is there a #VALUE! | Check the original number of the line with the error report | After cleaning spaces and non-numeric characters, recalculate |
| Whether there are illegal dates | Use =TEXT(B2,"yyyymmdd")=MID(A2,7,8) for reverse validation; lines that return FALSE need to be checked | DATE automatically extends 1990 13 to January of the following year; such error numbers require manual verification |
| Check if the number length is abnormal | Use LEN to see if there are only 18 and 15 | Rows with abnormal lengths should be checked separately against the original data; do not use the results directly |
❓ Frequently Asked Questions (FAQ)
1. What should the formula do if it shows a string of numbers, not a date?
This is the date serial number in Excel. Select the result cell and set the format to date to display normally, without affecting the calculation result itself.
2. How to quickly insert the ID number into Excel if the ID number is in a PDF?
If the PDF text can be selected, just copy and paste directly, making sure to clean up any extra spaces. Scanned files need OCR text recognition first. The PDF editor in the OmniDoc desktop client supports OCR on scanned PDFs and can export them to Excel. Identification results are prone to individual digit errors, so the ID number must be checked one by one before use.
3. What is the difference between splitting with the column function and using formulas?
Columns can be separated separately from digits 7 to 14 at fixed widths, suitable for one-time processing. But what it still gets is text, and the date must be converted again; After the number table is updated, you will need to redo the operation. Formulas can be automatically calculated based on data, making them more suitable for frequently changing tables.
4. What is the reason for the difference between the calculated age and the actual one-year difference?
There are three common reasons: incorrect conversion of birth date itself, DATEDIF defaults to age by year but business requires age by nominal age, or using TODAY instead of a unified reference date. First, verify the date of birth, then confirm the calculation criteria, and finally unify the reference date.
📝 Summary
To extract the birth date on the ID card, the key is to first distinguish between the 18-digit and 15-digit numbers, then use MID to capture and convert DATE to the real date. Before starting, set the number column as text, then sample and verify the results with reverse validation, so the table can be confidently used for statistics and filtering.
