Today, there are mainly two functions and a big formula that can do things. The general contents are as follows:

• Is the datedif function delicious? What can I do?
• What does the text function do?
• How to verify the authenticity of ID card number through Excel.

Ok, let's get to the point.

1. Datedif function

In Excel, DATEDIF is a legendary hidden function. You can't find it in Help and Insert. It is used to return the number of intervals between "year", "month" and "day" between two dates.

Let's look at the parameters first.

 =DATEDIF ("Start Date", "End Date", "Type of Return Value")

There is only one requirement for the start date and end date, that is, the end date must be greater than the start date. Also, the start date must be after 1900.

You can select the following return types:

 Y: Year M: Month D: Day

Of course, there are other ways to play.

 MD: returns the number of days between the start date and end date in the same month, ignoring the month and year. YD: returns the number of days between the start date and end date in the same year, ignoring the year. YM: returns the number of months between the start date and end date, ignoring the year in the date.

For example, calculate my birthday on September 19, 2020.

 =datedif("1997-09-26","2020-09-19","Y")

How much is returned directly?

The answer is 18 (22). After all, Jiang Chen is 18 (22) years old this year.

2. Text function

The text function is one of the most useful functions in Excel.

It's also my favorite.

The main function is to convert numeric values into text in the specified format.

It's very comfortable to use.

Usage:

 =TEXT ("Value", "Format you want to convert")

For example, I want to change 20200808 into August 8, 2020.

 =Text ("20200808", "00/00/0000")

Very simple. What formats can be converted?

Convertible format	numerical value	Converted results	explain
G/General format	ten	ten	No change
zero	ten point two five	ten point three	If there are less than three decimal places in front of the decimal point, fill them with 0 and reserve one decimal place.
####	ten	ten	All useless zeros disappear
00.##	one point two three four zero	one point two three	If the decimal point is less than 2 digits, use 0 to complete it; Surplus after, only 2 decimal places reserved
0000-00-00	twelve million three hundred and forty-five thousand six hundred and seventy-eight	1234-56-78	Used to represent the date
00/00/0000	eighty-seven million six hundred and fifty-four thousand three hundred and twenty-one	21/43/8765	Or is it used to indicate the date
aaaa	2020/09/19	Saturday	Display the full name of the Chinese day of the week
aaa	2020/09/19	six	Display the abbreviation of the day of the week in Chinese

What else can it be used for?

It can also be used to judge conditions.

for instance:

 =Text ("85", "[>=90] Excellent; [>=60] Qualified; Failed")

What is the output?

 qualified

What does that mean? If the entered value is greater than or equal to 90, excellent will be displayed; If it is greater than or equal to 60 and less than 90, it indicates qualified; if it is less than 60, it indicates unqualified.

What can it be used for? Judge the grade?

Is that it? Can you change 123 into one, two, three with the text function?

sure.

 =Text ("123", "[DBNum1] [$- 804] G/General format")

Output results:

 one hundred and twenty-three

What do I want in capital letters?

 =Text ("123", "[DBNum2] [$- 804] G/general format")

Output results:

 one hundred and twenty-three

Powerful TEXT function, don't you plan to practice it more?

Next, we will play the most important part,

How to verify the authenticity of ID card number through Excel.

As we all know, the ID card number has only 18 digits. What does each person do?

Come and study together.

Excel verifies ID number

The first six digits of the ID card number are the area code. Take Beijing as an example: 110000. The first and second digits represent provinces, three or four cities, and five or six counties.

The 7th to 14th digits indicate the date of birth. This is why in Excel, the age calculated from the ID card starts from the 7th digit and extracts 8 digits.

Next, 15 to 17 digits are sequence codes. It is used to indicate people born in the same place and number them. Among them, 17 are odd numbers for boys and even numbers for girls.

The 18th digit is a check code, which is used to check whether the ID card is legal. If the 18th digit is 10, X is used instead. It is said that it is calculated according to ISO7064:1983.MOD 11-2 check code.

How to determine whether the ID card number is correct under normal circumstances?

 1. Multiply the 17 digits of the front ID card number by different coefficients. The coefficients from the first digit to the seventeenth digit are respectively 7-9-10-5-8-4-2-1-6-3-7-9-10-5-8-4-2. 2. Add the 17 digit number and the result of coefficient multiplication. 3. Add and divide by 11 to see what the remainder is? 4. The remainder can only contain 11 numbers: 0-1-2-3-3-4-5-6-7-8-9-10. The number of the last ID card is 1-0-X-9-8-7-6-5-4-3-2. (That is, the remainder 0 corresponds to 1, the remainder 1 corresponds to 0, and the remainder 2 corresponds to X...) 5. It is known from the above that if the remainder is 3, 9 will appear on the 18th digit of the ID card. If the corresponding number is 2, the last digit of the ID card is the Roman numeral X. For example, the ID card number of a man is [53010219200508011X]. Let's see if this ID card meets the calculation rules. First, we can find that the sum of the products of the first 17 bits [(5 * 7)+(3 * 9)+(0 * 10)+(1 * 5)+(0 * 8)+(2 * 4)+(1 * 2)+(9 * 1)+(2 * 6)+(0 * 3)+(0 * 7)+(5 * 9)+(0 * 10)+(8 * 5)+(0 * 8)+(1 * 4)+(1 * 2)] is 189, and then divide 189 by 11 to get the result that 189 ÷ 11=17 is the remaining 2187 ÷ 11=17, and the remaining 2 cannot be The remainder is 2. Finally, we can know that the inspection code corresponding to the remainder 2 is X. Therefore, it can be determined that this is a correct ID card number.

So, how to use Excel to detect?

Suppose A2 is the ID card number and the current 18 digit second-generation ID card, then we will check it in B2.

 #Fill in B2: =IF (LEN (A2)<>18, "non second-generation ID card", IF (MID ("10X98765432"), (MOD (SUMPRODUCT (MID (A2, ROW (INDIRECT ("1:17")), 1) * {7; 9; 10; 5; 8; 4; 2; 1; 6; 3; 7; 9; 10; 5; 8; 4; 2}), 11)+1), 1)=RIGHT (A2), "right", "wrong")

This formula is very long, isn't it?

Do you need to parse them one by one?

The if function, mod function and mid function are all mentioned in Some Funny Formulas in Excel (2). The unfamiliar ones are the SUMPRODUCT function, INDIRECT function and RIGHT function.

See you tomorrow, then.