电子表格身份证自动生成出生日期

电子表格身份证自动生成出生日期

篇一：在EXCEL中用身份证号码自动生成出生年月

EXCEL中用身份证号码自动生成出生年月年龄及性别

在学校的人事管理中经常会遇到需要统计教职工的年龄的问题，但案头的原始资料只有身份证号码，其实这足够了。在EXCEL中，引用其内置函数利用身份证号码达到此目的比较简单。

1、身份证号码简介（18位）：

1～6位为地区代码；7～10位为出生年份；11～12位为出生月份；13～14位为出生日期；15～17位为顺序号，并能够判断性别，奇数为男，偶数为男；第18位为校验码。

2、确定“出生日期”：

18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄”应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。

①正确输入了身份证号码。(假设在D2单元格中)

②将光标定位在“出生日期”单元格(E2)中，然后在单元格 1

中输入函数公式

“=MID(D2,7,4)&-&MID(D2,11,2)&-&MID(D2,13,2)”即可计算出“出生日期”。 关于这个函数公式的具体说明：MID函数用于从数据中间提取字符，它的格式是:MID（text,starl_num,num_chars）。

Text是指要提取字符的文本或单元格地址（上列公式中的D2单元格）。

starl_num是指要提取的第一个字符的位置（上列公式中依次为7、11、13）。

num_chars指定要由MID所提取的字符个数(上述公式中，提取年份为4，月份和日期为2）。 多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起，“/”或“-” 分隔符则是在提取出的”年”“月”“日”数据之间添加的一个标记，这样的数据以后就可以作为日期类型进行年龄计算。操作效果如下图：

3、确定“年龄”：

“出生日期”确定后，年龄则可以利用一个简单的函数公式计算出来了：将光标定位在“年龄”单元格中，然后在单元格中输入函数公式“=INT((TODAY()-E2)/365)”即可计算出“年龄”。

关于这个函数公式的具体说明：

2

①TODAY函数用于计算当前系统日期。只要计算机的系统日期准确，就能立即计算出当前的日期，它无需参数。操作格式是TODAY（）。

②用TODAY（）-E2，也就是用当前日期减去出生日期，就可以计算出这个人的出生天数。 ③再除以 “365”减得到这个人的年龄。

④计算以后可能有多位小数，可以用【减少小数位数】按钮，将年龄的数值变成“整数”，也可在公式=

(TODAY()-E2)/365中再嵌套一个 “INT”函数取整数，即

“ =INT((TODAY()-E2)/365)”，这样就会自动将后面的小数去掉，只保留整数部分。操作效果如下图：

4、确定“性别”：

每个人的性别可以利用“身份证号码”进行判断，18位身份证号码中，第15～17位为顺序号，奇数为男，偶数为女。

将光标定位在“性别”单元格中，然后在单元格中输入函数公式

“=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),女,男)”即可计算出“性别”。

关于这个函数公式的具体说明：

①函数公式中，MID(D2,15,3)的含义是将身份证中的第15～17位提取出来。

3

②VALUE(MID(D2,15,3))的含义是将提取出来的文本数字转换成能够计算的数值。

③VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2)的含义是判断奇偶。(“INT”在上面说过是取整函数，如果是偶数，则前后相等；如果是奇数，则前后不相等。)

④=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),女,男)的含义是若是“偶数”就填写“女”，若是“奇数”就填写“男”。操作效果如下图：

篇二：Excel表格中根据身份证号码自动填出生日期

Excel表格中根据身份证号码自动填出生日期、计算年龄

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,TEXT(MID(e2,7,8),#-00-00),19&TEX 4

T(MID(e2,7,6),#-00-00))

自动录入男女：

=IF(E2=,,IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,女,男))

计算年龄（新旧身份证号都可以）：

=IF(AND(E2=),,IF(MIDB(E2,7,2)=19,107-MIDB(E2,9,2),107-MIDB(E2,7,2)))

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

前些天领导要求统计所有员工的性别、出生日期、年龄等信息，并且要得很急。而我们单位员工人数众多，短时间内统计相关信息并且输入计算机几乎是不太可能的。幸好在以前的一份金山表格中我们曾经统计有所有员工的身份证号码，而身份证中正有我们所需要的性别、出生日期、年龄等信息的。所以，干脆，还是直接在金山表格中从身份证号码提取相关的信息吧。

身份证号放在A2单元格以下的区域。我们需要从身份证号码中提取性别、出生日期、年龄等相关信息。由于现在使用的身份证有15位和18位两种。所以，在提取相关信息时，首先应该判断身份证号码的数字个数，然后再区别不同情况进行相关处理。

一、身份证号的位数判断

在B2单元格输入如下公式“=LEN($A2)”，回车后即可得 5

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

二、判断男女性别

第一步：提取性别识别数字。如果是18位身份证，那么倒数第二位就是性别判断的识别数字，奇数为男，偶数则为女。而如果是15位身份证，那么倒数第一位则是性别识别数字，同样奇数为男，偶数为女。所以首先应该提取这两种身份证的性别识别数字。在C2单元格输入公式

“=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))”，回车后，立刻可以得到相应的识别数字了。如图2所示。

图2 WPS中键入函数

函数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个字符”。

6

第二步：根据识别数字判断男女性别，在D2单元格输入公式

“=IF(ISEVEN($C2)=TRUE,女,男)”，回车后就可以得到的结果了。如图3所示。

图3 WPS中键入函数判断男、女性别

此处函数ISEVEN($C2)的作用是判断C2单元格的数字是否为偶数，如果是，则返回结果TRUE，否则返回结果FALSE。因此，公式

“=IF(ISEVEN($C2)=TRUE,女,男)”的含义就是如果C2单元格数字为偶数，那么在当前单元格显示“女”，否则显示

三、出生日期提取

第一步：提取数字型出生日期。在E2单元格输入公式

“=IF($B2=18,MID($A2,7,8),19&MID($A2,7,6))”并回车，如图4所示。这种形式的出生日期我们并不常用，此处提取出来只是为了我们下一步计算年龄时要用，也是一个辅助列。19&MID($A2,7,6)的意思是把字符“19”和MID($A2,7,6)截取到的6个字符合并在一起成为一个新的字符串。其它的，就不用再解释了吧？

图4 WPS中键入函数判断出生日期

第二步：提取带连接符的出生日期。在F2单元格输入公式

7

“=IF($B2=18,MID($A2,7,4)&-&MID($A2,11,2)&-&MID($A2,13,2),19&MID($A2,7,2)&-&MID($A2,9,2)&-&MID($A2,11,2))” 并回车，如图5所示，可以得到我们常用的如 “1977-08-11”的格式。此处用到的函数在前面已经有过解释，所以不再罗嗦了。

图5 WPS中键入函数判断特殊日期

四、公式计算年龄

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

图6 WPS中用公式计算年龄

最后，选中B2:G2单元格，向下拖动填充句柄至最后一行，那么我们所需要的所有数据就有了，再选中B列、C列、E列，并点击右键，在弹出的右键菜单中点击“隐藏”命令，将这些辅助列隐藏起来，就可以得到一份完整的表格了。

很简单吧？利用WPS的函数功能，只需要设置一下相应的参数，把一个个隐藏在身份证号码里的信息提取出来，完成上面所有操作用时也不到半小时，将所得到的表格美化一下，就可以向领导交差了，呵呵。

篇三：Excel表格中根据身份证号码自动填出生日期

8

Excel表格中根据身份证号码自动填出生日期、计算年龄

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,TEXT(MID(e2,7,8),#-00-00),19&

自动录入男女：TEXT(MID(e2,7,6),#-00-00))

=IF(E2=,,IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,女,男))

计算年龄（新旧身份证号都可以）：=IF(AND(E2=),,IF(MIDB(E2,7,2)=19,107-

MIDB(E2,9,2),107-MIDB(E2,7,2)))

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

前些天领导要求统计所有员工的性别、出生日期、年龄等信息，并且要得很急。而我们单位员工人 数众多，短时间内统计相关信息并且输入计算机几乎是不太可能的。幸好在以前的一份金山表格中我们曾经统计有所有员工的身份证号码，而身份证中正有我们所需 要的性别、出生日期、年龄等信息的。所以，干脆，还是直接在金山表格中从身份证号码提取相关的信息吧。

9

身份证号放在A2单元格以下的区域。我们需要从身份证号码中提取性别、出生日期、年龄等 相关信息。由于现在使用的身份证有15位和18位两种。所以，在提取相关信息时，首先应该判断身份证号码的数字个数，然后再区别不同情况进行相关处理。

一、身份证号的位数判断

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

以很方便地求到身份证号码的位数。

二、判断男女性别

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

图2 WPS中键入函数

函数MID(A2,17,1)的作用是从A2单元格字符串的第17个字开始截取1个字 符，这正好是倒数第二个字符；函数 10

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所示。

图3 WPS中键入函数判断男、女性别

此处函数ISEVEN($C2)的作用是判断C2单元格的数字是否为偶数，如果是，则返 回结果TRUE，否则返回结果FALSE。因此，公式“=IF(ISEVEN($C2)=TRUE,女,男)”的含义就是如果C2单元格数字为偶 数，那么在当前单元格显示“女”，否则显示

三、出生日期提取

第一步：提取数字型出生日期。在E2单元格输入公式

“=IF($B2=18,MID($A2,7,8),19(转载于:

小 龙文档 网:电子表格身份证自动生成出生日期)&MID($A2,7,6))”并回车，如图4所示。这种形式的出生日期我们并不常 用，此处提取出来只是为了我们下一步计算年龄时要用，也是一个辅助列。19&MID($A2,7,6) 11

的意思是把字符“19”和 MID($A2,7,6)截取到的6个字符合并在一起成为一个新的字符串。其它的，就不用再解释了吧？

图4 WPS中键入函数判断出生日期

第二步：提取带连接符的出生日期。在F2单元格输入公式

“=IF($B2=18,MID($A2,7,4)&-&MID($A2,11,2)&-&MID($A2,13,2),19&MID($A2,7,2)&-&MID($A2,9,2)&-&MID($A2,11,2))” 并回车，如图5所示，可以得到我们常用的如 “1977-08-11”的格式。此处用到的函数在前面已经有过解释，所以不再罗嗦了。

图5 WPS中键入函数判断特殊日期

四、公式计算年龄

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

最后，选中B2:G2单元格，向下拖动填充句柄至最后一行，那么我们所需要的所有数据就 有了，再选中B列、C列、E列，并点击右键，在弹出的右键菜单中点击“隐藏”命 12

令，将这些辅助列隐藏起来，就可以得到一份完整的表格了。

很简单吧？利用WPS的函数功能，只需要设置一下相应的参数，把一个个隐藏在身份证号码 里的信息提取出来，完成上面所有操作用时也不到半小时，将所得到的表格美化一下，就可以向领导交差了，呵呵。

身份证号码导出出生日期的备用公式：

=IF(LEN(A1)=18,TEXT(MID(A1,6,8),#-00-00),19&

TEXT(MID(A1,6,6),#-00-00))

=IF(LEN(A1)=18,TEXT(MID(A1,7,8),#-00-00),19&

TEXT(MID(A1,7,6),#-00-00))

=IF(LEN(A1)=18,TEXT(MID(A1,9,8),#-00-00),19&

TEXT(MID(A1,9,6),#-00-00))

=DATE(MIDB(A1,7,4),MIDB(A1,11,2),MIDB(A1,13,2))

五、去掉工资系统中导出身份证号乱码

**去掉工资系统中导出身份证号乱码=LEFT(G7,18)

13