ID number constellation excel_excel ID constellation formula

How to get the date of birth, age and gender from the ID number by using functions in EXCEL tables?

1. Take excel20 10 as an example. As shown in the figure, there are the following ID numbers, in which the ID format is text format, otherwise the following numbers will become 0;

2. Get the date of birth first, and enter = date (MID (A2, 7,4), MID (A2, 1 1 2), MID (A2,13,2)) in cell B2? Just click Enter, and the role of MID is to extract a certain number of characters from the specified string. A2 is the specified string, where 7 refers to extracting from the seventh digit, and 4 refers to extracting the last four digits from the seventh digit, and then converting them into DATE format with the date function;

3. You can also use the TEXT function to extract the date of birth. Just enter = text (mid (a3,7,8), "0-00-00") in the B3 cell.

4. To extract the age, enter = year (today ())-mid (a2,7,4) in the C2 cell and press enter, and then subtract the year in the ID card from the current time to get the current age;

5. When extracting gender, look at the 17 digits on the ID number, with odd numbers representing men and even numbers representing women. Enter = if (mod (mid (A2, 17, 1), 2), "male" and "female" in cell D2, and press enter to determine the gender.