在Excel
中输入身份证号码、出生日期和年龄的技巧
在使用Excel表格进行人员信息等相关操作时,常常需要用到身份证号码、出生日期和年龄等信息。如何将这些信息方便地进行输入呢?下面是我在相关操作中用到的技巧:
假设需要在A1单元格输入身份证号码。
1、输入身份证号码的方法:选中A1单元格,设置单元格
为“文本”,然后再使用数字键盘输入身份证号码;不设置单元格格式也可以,但必须先输入英文格式的“'”(引号内的符号),再输入身份证号码的数字。
2、提取出生日期的方法:在需要输入出生日期的单元格(比如B1)中,设置单元格格式为“日期”,并选择好需要的日期格式,再输入“=IF(OR(LEN(A1)=18,LEN(A1)=15),TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0,"身份证错误")”(引号内的公式)后回车就可以了;
也可以使用
“=TEXT(IF(LEN(A1)=15,"19",)&MID(A1,7,IF(LEN(A1)=18,8,l)),"####-##-##")”(引号内的公式)进行提取,但此时B1单元格中的日期格式是固定的“####-##-##”格式。
也可以
“=MID(A1,7,4)&-MID(A1,11,2)&-MID(A1,13,2)”(不含引号,其中蓝色突出标识的为你所引用数据,即身份证号码所在的单元格)回车即可提取到1980-02-14格式的出生年月日了
也可以
如果需要1980年2月14日格式的,可以用下面的公式“=MID(F2,7,4)&"年"&MID(F2,11,2)&"月"&MID(F2,13,2)&"日"”(不含引号)(“&”有合并的意思)
3、提取年龄的方法:提取年龄的方法有两种,一种是根据当前年份确定年龄的方法,一种是根据特定日期确定年龄(比如学校招收一年级新生时,要根据8月31日来确定年龄是否符合要求)的方法。第一种方法的公式是“=YEAR(TODAY())-MID(A1,7,4) ”;第二种方法的公式是
“=DATEDIF(TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00"),"2010-8-31","Y") ”。
4、提取性别的方法:
=IF(LEN(A1)=15,IF(VALUE(RIGHT(A1,3))/2=INT(VALUE(RIGHT(A1,3))/2),"女","男"),IF(VALUE(MID(A1,15,3))/2=INT(VALUE(MID(A1,15,3))/2),"女","男"))