Jinshan WPS Form Proofing Date of birth and identity card is a simple way to match

We use the Jinshan WPS form input ID information, sometimes also need to enter the date of birth, in order to check the ID card information and birthday information is consistent, we need to check, but the workload is still more trouble. Today, small make up to share with you Jinshan WPS form proof ID number and birth date is consistent with the simple method!

A is an identity card number (before entering, please set the column cell format uniformly to "text"), B as a formula from the ID number of the date of birth, take B2 as an example, the formula is:

=text (IF (Len (A2) =15, "a",) &mid (A2,7,if (Len (A2) =18,8,6)), "####-##-##"

Using the text function, the formula can intelligently judge 18-bit and 15-digit numbers, and extract the birth date from them.

We found that due to an input error, the date of the extracted birth date was "1988-13-1" (B3) and "2005-02-29" (B5) that were not possible in practice. So, how do we find them quickly?

We copy the date of birth in column B (e.g. B2:B5), then place the cursor in cell C2, click the small arrow to the right of the Paste button, select value from, and copy all dates to column C, at which point the birth date of column C is all selected, and A smart cue symbol appears on the left side of cell C2, with the mouse click on the prompt to select Convert to Number (C).

In this way, the data in column C is converted from a text format to a real "date" format. However, careful you should have found that only the correct date can be successfully converted, its alignment automatically turned to "right", and the wrong date is not converted, is still text formatting, alignment is still "left", The column column of C to adapt to increase, on the "right, wrong to the left"! All you have to do is proofread the wrong ID numbers and correct them.