How to generate age from ID number

How to calculate age based on ID number?

Take excel as an example, the ID number of the birth date of the extraction formula is: in the age cell enter = YEAR (TODAY ()) – MID (A2,7,4). A2 this represents the location of the ID number in your form. Formula input correctly after the carriage return [ENTER] key to confirm.

Specific steps:

1, open or create excel data table file.

2, add the age column for calculating age.

3, in the age column cell enter the formula, = DATEDIF (D2, TODAY (), “y”), by birth year and month to calculate the age, D2 that the birth year and month column data.

4, press Enter, and pull the cell, complete the data fill, you can complete the bulk age value calculation.

How to calculate age based on ID number?

Method 1:

1, on the computer to open the sample Excel table containing the ID number, to calculate the age of the cell enter = YEAR (TODAY ()) – MID (A2,7,4) where A2 is the ID number of the cell. For example, if the identity card number where the cell is B3 then enter = YEAR (TODAY ()) – MID (B3,7,4).

2, enter a good formula, press the keyboard Enter key can calculate the age of this one

Method two:

1, open the example EXCEL file, the table has four columns: ID number, name, date of birth, age. Date of birth and age need to be extracted and calculated by EXCLE, the 2 columns of the cell format for the “regular” format

2, the extraction of the date of birth need to use the function there are two functions, one is the DATE function, one is the MID function. DATE function of the structure of DATE (year, month, day). month, day), its role is to extract the numbers into date format for display. MID function structure for the MID (text,

start_num,

num_chars), its role is to extract the specified number of bits of characters from a string, which text is the need to extract the characters where the string, start _num is the starting position of the character to be extracted in the string, and num_chars is the number of bits of the character to be extracted.

3, in cell C3 enter the formula “= DATE (MID (A3, 7, 4), MID (A3, 11, 2), MID (A3, 13, 2))” and pull down,

which MID (A3, 7, 4) for the extraction of the year, MID (A3, 11, 2) for the extraction of the month, MID (A3, 11, 2) for the extraction of the month. 2) is used to extract the month and MID(A3,13,2) is used to extract the day.

4, do not need to be accurate to the date of the age calculation can be directly with the need to calculate the year minus the ID card number of the year to arrive. If you want to calculate the age of all people in 2016, in cell C3 enter the formula “=2016-

MID (A3,7,4)” can be.

5, need to be accurate to the date of the age of the calculation need to use the hidden DATEDIF function. The structure of the function DATEDIF (start_date, end_date, unit), which start_date is the starting date (in this case is the date of birth), end_date is the end date (in this case is the need to calculate the age of the specific year date), unit is the type of information to be returned. Such as to calculate the exact age of all people in June 1, 2016, in cell C3 enter the formula “= DATEDIF (C3,

“2016/6/1”, “y”)” can be.

excel table how to set the formula:

1, first open the excel worksheet, open after you can see in the “Start” menu bar there is a “Apply Format” button, click the button.

2, after clicking the drop-down selection, in the drop-down option to select the format to be used, which would like to use which click on which

3, after clicking the dialog box will pop up as shown in Figure, this time, select the use of good use of the form formatting of the region, click the button marked with a red box to select the region.

4, after selecting the region will appear as shown in the figure, if the regional selection is complete, then click just under the red box at the button.

5, after clicking on the picture will appear, this time you can see just select the region from where to where, after confirming that there is no error, click on the bottom of the “OK” button.

6, click the OK button will appear as shown in the figure, this time you can see that the format has just changed, has become a selection of the application of the table format.