OmniDocHow to filter age groups by ID number in Excel
When there is only ID number and no age column in the table, how to calculate the age using the date of birth, and then filter by over 60 years old or a certain age range. Covers 18-digit and 15-digit numbers, reference date settings and result verification.
Key points of this article
- The 7th to 14th digits of the ID number are the year, month and day of birth. First use the formula to split it into dates, and then calculate the age.
- A reference date must be fixed to calculate one year old, otherwise the results will change every day and the statistical caliber cannot be reproduced.
- The ID number must be set to text format, otherwise the 18-digit number will be displayed in scientific notation and the last digit will be changed to 0.
- Filter by "greater than or equal to 60" for those over 60 years old, filter by "between" for age ranges, and all boundary values are included.
- ID numbers are sensitive personal information and should be deleted or desensitized after counting. Do not forward the entire table at will.
Many units only have ID numbers in their lists, registration forms or social security accounts, but no age column. To count "people over 60 years old" or "employees under 35 years old", you must first calculate the age from the number and then filter. This type of requirement is common in personnel, community registration and training registration. The longer the form, the easier it is to make errors by manually calculating each one.
This article talks about the complete process of filtering age groups by ID number in Excel: extract the date of birth from the number, use a formula to calculate the age, and then use the filter function to get the target group. It is different from "extracting only gender" or "extracting only date of birth". The focus is on calculating age accurately and solving boundary and format problems during screening. After reading, you can apply these formulas directly in your own spreadsheets.
How to use ID number to filter age group? One sentence conclusion
Direct answer: First use a formula to extract the date of birth from the ID number, then use the DATEDIF function to calculate the age based on a fixed reference date, and finally perform numerical filtering on the age column.
| your situation | Recommended practices | reason |
|---|---|---|
| 18-digit ID number, statistics include people over 60 years old | Extract the date of birth, calculate the age, and filter the number "greater than or equal to 60" | The 7th to 14th digits of the 18-digit number are the date of birth, and the formula is direct |
| There are 15 old numbers mixed in the table | Use IF to determine the number of digits, 15 digits split by the 1900s | The 15-digit number only has two digits of birth year and needs to be processed separately. |
| To filter for a range, such as 18 to 35 years old | Number filter "Between 18 and 35" | Both ends of the range are included, suitable for recruitment, enrollment and other scenarios |
| Need to double check or share results | Add "Does it match" auxiliary column | The judgment results of each line are clear at a glance, making it easier for others to review |
| Statistics must be based on a certain deadline | Set up a separate reference date cell | The age changes with time, and the results can be reproduced only on a fixed date. |
Before you start: Confirm these points first
1. Confirm whether the number is 18 digits or 15 digits. The current ID card is 18 digits, the date of birth is in the 7th to 14th digits, and the format is YYYYMMDD. Old numbers issued before 1999 mostly have 15 digits, date of birth in digits 7 to 12, and only two digits for the year. The two numbers are split in different ways and must be judged separately when mixed together.
2. Determine "on which day to calculate age". Years of age are calculated from the date of birth to a certain day, and statistical reports usually indicate the deadline. If you use TODAY() directly, the results of opening the table may change tomorrow. It is recommended to leave a blank cell in the table, fill in the date you want to count, and refer to it in all formulas.
3. First set the ID number column as text. Excel will treat numbers with more than 15 digits as numerical values, and the excess part will be displayed as 0. The last digit may also be changed, and an error will occur if the date of birth is split later. It's a safe bet to format the entire column as text before pasting, or to enter a single quote before typing.
4. Confirm whether there are blank lines, missing numbers, or records with inconsistent formats. The formula will report an error when it encounters an empty cell or a number with incorrect digits. First filter out records whose length is not 15 or 18 and check them individually to avoid them lowering the statistical results.
5. Protect personal information. ID number is sensitive personal information. When only age is needed for statistics, you can delete the number column in the saved copy, or keep only the age result. Do not forward the form containing the complete number randomly through chat groups or emails.
Steps to filter people over 60 years old by ID number in Excel
- Leave a reference date above the form. For example, enter the deadline you want to count in F1, and write "reference date" in E1 for later checking.
- Extract the date of birth in cell C2. Assuming the ID number is in B2, enter the formula:
=DATE(--MID(B2,7,4),--MID(B2,11,2),--MID(B2,13,2)). This formula applies to 18-digit numbers, MID is used to intercept characters at the specified position, and "--" converts text into numbers. - Compatible with 15-digit numbers. If there may be old numbers in the table, change C2 to:
=IF(LEN(B2)=15,DATE(1900+--MID(B2,7,2),--MID(B2,9,2),--MID(B2,11,2)),DATE(--MID(B2,7,4),--MID(B2,11,2),--MID(B2,13,2))). The 15-digit number is understood in terms of the 1900s by default, an assumption that needs to be accounted for in the results. - Calculate the age. Enter:
=DATEDIF(C2,$F$1,"y")in D2. "y" means calculated in whole years, and the result is the number of years old as of the reference date. Fill down to all rows. - Filter the age column. Select the table header and enable the filter function, click the drop-down arrow of the age column, select the greater than or equal to category option in the numerical filter, and enter 60. When an interval is required, select the Between option and fill in the values at both ends.
- Add judgment column (optional). Enter:
=IF(D2>=60,"是","否")in E2, and then you can directly press "Yes" to filter, and the results are easier to review. - Copy the filter results. In the filtering state, select the visible rows and copy them to the new worksheet, keeping only the required columns and keeping the original table intact.
Check each item after completion
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Is the date of birth correct? | Randomly check lines 3 to 5 and compare it with the middle 8 digits of the ID number. | If the date is misaligned, check the starting position and digits of the MID |
| Check if the reference date is correct | Check if the date in cell F1 matches the cutoff date you want to count | The formula will be automatically recalculated after modification, no need to modify it line by line. |
| Is the age limit reasonable? | Find the exact date of birth as a reference date to check if the age is underestimated or overestimated by one year | Confirm that the third parameter of DATEDIF is "y", rather than counting by days |
| Whether there are error cells | Use the filter to see if #VALUE! or #NUM! appears in the filter! | Usually, the number has incorrect digits or contains spaces, which are cleaned up and refilled |
| 15-digit result number | Individually screen 15 records to see if their age is reasonable | If the birth year of the old number is clearly off, manual verification of the original file is required |
| Whether the number has been changed | Check if the last digit of your ID number is X or a number, and if it has turned to 0 | If it has been changed, it must be re-imported from the original file; it cannot be fixed using formulas |
After verification, remember to keep a version with a reference date so you can answer later when someone asks, "What day is this age calculated?"
❓ Frequently Asked Questions (FAQ)
1. Is it okay to use TODAY() instead of a reference date?
Yes, but the results will change depending on the date it opens. If it's just a temporary look, using TODAY() is fine; If you want to use it for reports, lists, or archiving, it is recommended to fix a date and specify the statistical caliber.
2. Why did the ID number I entered become scientific notation?
Excel displays numbers longer than 15 characters as values. The solution is to set the cell to text before entering or add single quotes before the number. Deformed data cannot be restored using formulas and must be re-imported into the original numbers.
3. Is it "over 60" or "greater than or equal to 60"?
By age, those aged 60 or above should be included, so "above or equal to 60" should be selected. The specific standards are subject to the regulations of your unit or institution.
4. What if the formula doesn't match the age on your ID card?
First, confirm that the reference date is correct, then check if the birth date column is properly split. The full age is calculated based on the full number of years from the date of birth to the reference date. If there is still a discrepancy, official verification or original file records should be used, and not the results of the table alone should be relied upon.
📝 Summary
The key to filtering age groups by ID number is to first extract the birth date from digits 7 to 14, then calculate the exact age using a fixed reference date, and finally filter the target group with numbers. Pay special attention to the format of the numbers and the 15-digit old numbers in the table, and after compiling the data, make sure to protect your personal information properly.
