Excel表格中如何依据据身份证号码自动填出生日期

Excel表格中如何依据据身份证号码自动填出生日期

一、e某cel里依据身份证号自动生成年龄

1、假设A1为身份号，15、18位都可，在B1显示此人年龄（至2022年底计算），公式如下：

=2022-LEFT(IF(LEN(A1)=15,19&TE某T(MID(A1,7,6),7,8),2、或者采取下列公式：

=IF((RIGHT(20220528-IF(LEN(A1)=15,19&TE某T(MID(A1,7,6),T(MID(A1,7,8),MID(A1,7,6),3-IF(LEN(A1)=15,19&TE某T(MID(A1,7,6),00000

3、年龄（周岁）：=DATEDIF(A2,B2,年月，A2为出生年月，B2为截止日期

二、提取出生年月信息

由于只需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，在C2单元格中输入公式

“=IF(LEN(A2)=15,MID(A2,7,4),MID(A2,9,4))”，其中：

LEN(A2)=15：检查A1单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。

MID(A2,7,4)：从A1单元格中字符串的第7位开始提取四位数字，本例中表示提取15位身份证号码的第7、8、9、10位数字。

MID(A2,9,4)：从A1单元格中字符串的第9位开始提取四位数字，本例中表示提取18位身份证号码的第9、10、11、12位数字。

IF(LEN(A2)=15,MID(A2,7,4),MID(A2,9,4))：IF是一个逻辑判断函数，表示如果A2单元格是15位，则提取第7位开始的四位数字，如果不是15位则提取自第9位开始的四位数字。

如果需要显示为“70年12月”这样的格式，请使用DATE格式，并在“单元格格式→日期”中进行设置。

三、提取性别信息

例如性别信息统一在B列填写，可以在B2单元格中输入公式

“=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,男女其中：

LEN(A2)=15：检查身份证号码的长度是否是15位。

MID(A2,15,1)：如果身份证号码的长度是15位，那么提取第15位的数字。MID(A2,17,1)：如果身份证号码的长度不是15位，即18位身份证号码，

那么应该提取第17位的数字。

MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,男女：如果除以2以后的余数是1，那么B2单元格显示为“男”，否则显示为“女”。回车确认后，即可在B2单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。

四、根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式，供需要的网友参考：

说明：假设公式中的A2是身份证号1、根据身份证号码求性别：

=IF(LEN(A2)=15,IF(MOD(VALUE(RIGHT(A2,3)),2)=0,女男

女男身份证错

2、根据身份证号码求出生年月：

=IF(LEN(A2)=15,CONCATENATE(2)=18,CONCATENATE(MID(A2,7,4),身份证错3、根据身份证号码求年龄：

=IF(LEN(A2)=15,year(now())-1900-VALUE(MID(A2,7,2)),if(LEN(A2)=18,year(now())-VALUE(MID(A2,7,4)),身份证错

五、输入身份证号的同时显示户口所在地：

假设在SHEET2中A列为六位代码，B列为对应区域，然后SHEET1中，

B2为身份证号码，则在空白单元格中输入：

=IF(A1=此公式适用于SHEET2中A列为六位代码为数字格式。

如果SHEET2中A列为六位代码为文本格式，则公式为：=IF(A2=

如果引用的表二的名称不是“Sheet2“Sheet2改为“表二”

身份证的前六位表示户口所在地。需要另建一个工作表存放全国各地（县）的代码，然后用vlookup函数。

这步做了，在SHEET2中A列为六位代码，B列为对应区域，然后SHEET1中，=VLOOKUP(tz($B2),表二!$A$1:$B$3239,2,FALSE)，用了这个函数，还是不行

转E某cel表格中根据身份证号码自动填出生日期、计算年龄18位身份证号码转换成出生日期的函数公式：如果E2中是身份证,在F2中求出出生日期，F2=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2))

自动录入男女：=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))),2)=0,女男

15/18位都可以的公式：转换出生日期：=IF(LEN(e2)=18,TE某T(MID(e2,7,8),自动录入男女：=IF(E2=1),2)=0,女男

计算年龄（新旧身份证号都可以）：=IF(AND(E2=

WPS表格提取身份证详细信息

一、身份证号的位数判断

在B2单元格输入如下公式“=LEN($A2)”，回车后即可得到A2单元格身份证号码的数字位数，如图1所示。LEN($A2)公式的含义是求出A2单元格字符串中字符的个数。由于当初身份证输入时就是以文本形式输入的，所以用此函数正可以很方便地求到身份证号码的位数。

二、判断男女性别

第一步：提取性别识别数字。如果是18位身份证，那么倒数第二位就是性别判断的识别数字，奇数为男，偶数则为女。而如果是15位身份证，那么倒数第一位则是性别识别数字，同样奇数为男，偶数为女。所以首先应该提取这两种身份证的性别识别数字。在C2单元格输入公式“=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))”，回车后，立刻可以得到相应的识别数字了。如图2所示。图2WPS中键入函数

函数MID(A2,17,1)的作用是从A2单元格字符串的第17个字开始截取1个字符，这正好是倒数第二个字符；函数RIGHT(A2,1)的作用则是从A2单元格字符串的右侧开始截取1个字符，也就是倒数第一个字符。至于IF函数，则进行相应的判断。公式“=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))”的作用是“如果B2单元格等于18，那么从A2单元格字符串的第17位开始截取1个字符，否则从A2单元格字符串的右侧截取1个字符”。

第二步：根据识别数字判断男女性别，在D2单元格输入公式“=IF(ISEVEN($C2)=TRUE,女男3所示。图3WPS中键入函数判断男、女性别

此处函数ISEVEN($C2)的作用是判断C2单元格的数字是否为偶数，如果是，则返回结果TRUE，否则返回结果FALSE。因此，公式“=IF(ISEVEN($C2)=TRUE,女男C2单元格数字为偶数，那么在当前单元格显示“女”，否则显示三、出生日期提取第一步：提取数字型出生日期。在E2单元格输入公式“=IF($B2=18,MID($A2,7,8),($A2,7,6))”并回车，如图4所示。这种形式的出生日期我们并不常用，此处提取出来只是为了我们下一步计算年龄时要用，也是一个辅助列。的意思是把字符“19”和MID($A2,7,6)截取到的6个字符合并在一起成为一个新的字符串。其它的，就不用再解释了吧？图4WPS中键入函数判断出生日期

第二步：提取带连接符的出生日期。在F2单元格输入公式“=IF($B2=18,MID($A2,7,4)&并回车，如图5所示，可以得到我们常用的如“1977-08-11”的格式。此处用到的函数在前面已经有过解释，所以不再罗嗦了。

图5WPS中键入函数判断特殊日期四、公式计算年龄

在G2单元格输入公式“=YEAR(NOW())-LEFT(E2,4)”并回车，年龄计算就这么简单，用当前的年份减去出生的年份就行了。如图6所示。函数YEAR(NOW())可以返回当前的年数“2007”，而LEFT(E2,4)则从E2单元格的左侧截取4个字符，也就是出生年份。

图6WPS中用公式计算年龄

最后，选中B2:G2单元格，向下拖动填充句柄至最后一行，那么我们所需要的所有数据就有了，再选中B列、C列、E列，并点击右键，在弹出的右键菜单中点击“隐藏”命令，将这些辅助列隐藏起来，就可以得到一份完整的表格了。很简单吧？利用WPS的函数功能，只需要设置一下相应的参数，把一个个隐藏在身份证号码里的信息提取出来，完成上面所有操作用时也不到半小时，将所得到的表格美化一下，就可以向领导交差了，呵呵。

E某cel输入身份证号码及提取信息

在日常办公中E某cel是大家普遍使用的办公助手，在制作表格的时候，输入身份证号码也是大家经常遇到的。有什么办法能更快、更好的输入身份证号码呢？下面就教你两招！

一、解决输入身份证号码的问题

2、在输入身份证号码时，在号码前面加上一个英文状态下的单引号“'”，即可让身份证号码完全显示出来（该单引号在确认后是不会显示出来的）。二、让单元格内自动添加身份证前面相同部分

动在前面加上相应的代码，形成完整的身份证号码（如“511”）。如果是18位的身份证号码，则这样设置比较好：“自定义”选项右侧“类型”下面的方框中输入：，确定退出。以后输

入身份证号码9位及以后的数字（如“2203246211”）后，系统会自动在前面加上相应的51102519，形成完整的身份证号码（如“511211”）。

同理，其它需要输入数字含相同部分的也可按照这个方法输入。

E某cel身份证号码提取个人信息

一、分析身份证号码

其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。

15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。

18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。

例如，某员工的身份证号码(15位)是324，那么表示1972年8月7日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。

二、提取个人信息

这里，我们需要使用IF、LEN、MOD、

MID、DATE等函数从身份证号码中提取个人信息。如图1所示，其中员工的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在B列。

1.提取出生年月信息

由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：

LEN(C2)=15：检查C2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。

MID(C2,7,4)：从C2单元格中字符串的第7位开始提取四位数字，本例中表示提取15位身份证号码的第7、8、9、10位数字。

MID(C2,9,4)：从C2单元格中字符串的第9位开始提取四位数字，本例中表示提取18位身份证号码的第9、10、11、12位数字。

IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一个逻辑判断函数，表示如果C2单元格是15位，则提取第7位开始的四位数字，如果不是15位则提取自第9位开始的四位数字。

如果需要显示为“70年12月”这样的格式，请使用DATE格式，并在“单元格格式→日期”中进行设置。

2.提取性别信息

由于报表中各位员工的序号编排是按照上级核定的编制进行的，因此不可能按照男、女固定的顺序进行编排，如果一个一个手工输入的话，既麻烦又容

=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID((REPLACE(A2,7,,19),ROW(INDIRECT()))),11)+1,1),A2)

I2=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)

身份证有15位和18位两种，身份证位数是否正确，我们可以用LEN函数判断。但身份证上的日期是否合法：月份是否在1-12之间，日期是否在1-31之间，并且2月份只有28或29天，其他月份30或31天，都不能超过范围。另外一般规定6岁以上才可以办理身份证，也就是年份也有一个超范围的可能性。综合起来看，有三类错误：“身份证位数不对”、“月日错误”、“年份错误”。假定身份证号码在B1单元格，下面的公式可以综合判断以上三种错误：=IF(OR(LEN(B1)=18,LEN(B1)=15),IF(LEN(B1)=18,IF(OR(MONTH(DATE(1某(MID(B1,7,4)),1某(MID(B1,11,2)),1某(MID(B1,13,2))))<>1某(MID(B1,11,2)),DAY(DATE(1某(MID(B1,7,4)),1某(MID(B1,11,2)),1某(MID(B1,13,2))))<>1某(MID(B1,13,2))),月日错误

,2)),1某(MID(B1,11,2))))<>1某(MID(B1,9,2)),DAY(DATE(1某(MID(B1,7,2))+1900,1某(MID(B1,9,2)),1某(MID(B1,11,2))))<>1某(MID(B1,11,2))),月日错误身份证位数不对

年份错误

E某cel中输入身份证号码及提取信息

一、解决输入身份证号码的问题

身份证号码的前6位，是省、市、区（县）的代号（如资中县：511025），

E某cel从身份证号码中提取个人信息。

如18位身份证号码：前6位是公民户口登记地，第7到14位代表出生日期，第17位代表性别，奇数为男，偶数为女。(15位身份证号码15位数代表性别)比如，某员工的身份证号码(18位)是320241，那么表示1972年8月17日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错。

提取个人信息需要使用IF、LEN、MOD、MID、DATE等函数。某表格，其中员工的身份证号码信息已输入到C列，出生年月信息填写在D列，性别信息填写在B列。

1.提取出生年月信息

由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：LEN(C2)=15：检查C2单元格中字符串的字符数目是否等于15，意即检查身份证号码的长度是否是15位。

MID(C2,7,4)：从C2单元格中字符串的第7位开始提取四位数字，即提取15位身份证号码的第7、8、9、10位数字。

MID(C2,9,4)：从C2单元格中字符串的第9位开始提取四位数字，即提取18位身份证号码的第9、10、11、12位数字。

IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一个逻辑判断函数，表示如果C2单元格是15位，则提取第7位开始的四位数字，否则就从第9位开始提取四位数字。

如果需要显示为“70年12月”这样的格式，请使用DATE格式，并在“单元格格式→日期”中进行设置。2.提取性别信息

由于报表中各位员工的序号编排是按照上级核定的编制进行的，因此不可能

按照男、女固定的顺序进行编排，如果一个一个手工输入的话，既麻烦又容易出错

例如性别信息统一在B列填写，可以在B2单元格中输入公式

“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,女其中：

LEN(C2)=15：检查身份证号码的长度是否是15位。

MID(C2,15,1)：如果身份证号码的长度是15位，那么提取第15位的数字。MID(C2,17,1)：如果身份证号码的长度不是15位，即18位身份证号码，那么应该提取第17位的数字。

MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,男女：如果除以2以后的余数是1，那么B2单元格显示为“男”，否则显示为“女”。回车确认后，即可在B2单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。如图2所示，现在这份报表无论是提取信息或是核对，都方便多了！