身份证号自动生成年龄、出生年月、性别公式[我整理]

2023-04-01 本文由:[文学大佳]_发布到适用文档117 【我要投稿】

第一篇：身份证号自动生成年龄、出生年月、性别公式

身份证号自动生成年龄、出生年月、性别公式=MID(E4,8,4)&“/”&MID(E4,12,2)&“/”&MID(E4,14,2)

(2022-07-28 17:54:56)转载▼

身份证号自动生成年龄、出生年月、性别公式: =IF(LEN(C2)=15,“19”&MID(C2,7,2)&“-”&MID(C2,9,2)&“-”&MID(C2,11,2),MID(C2,7,4)&“-”&MID(C2,11,2)&“-”&MID(C2,13,2))

=DATE(MID(I4,IF(LEN(I4)=18,7,7),IF(LEN(I4)=18,4,2)),MID(I4,IF(LEN(I4)=18,11,9),IF(LEN(I4)=18,2,2)),MID(I4,IF(LEN(I4)=18,13,11),IF(LEN(I4)=18,2,2)))

1、生成出生年月日公式一（18位号和15位号都可自动生成 19**-*-*）： =DATE(MID(I4,IF(LEN(I4)=18,7,7),IF(LEN(I4)=18,4,2)),MID(I4,IF(LEN(I4)=18,11,9),IF(LEN(I4)=18,2,2)),MID(I4,IF(LEN(I4)=18,13,11),IF(LEN(I4)=18,2,2)))生成出生年月日公式二（18位或15位号生成19**-**-**）：

=IF(LEN(C2)=15,“19”&MID(C2,7,2)&“-”&MID(C2,9,2)&“-”&MID(C2,11,2),MID(C2,7,4)&“-”&MID(C2,11,2)&“-”&MID(C2,13,2))

说明：自动生成出生年月就把以上公式复制粘贴到需自动生成“出生年月日”的那一栏第一格，然后把公式一中的所有“I4”或公式二中的所有“C2”改成身份证号码单元格的栏代码如A、B、C、D……和行数如1、2、3、4……。点“回车”即可。

2、利用出生年月日生成年龄： =YEAR(TODAY())-YEAR(F4)

说明：自动生成年龄就把以上公式复制粘贴到需自动生成那一栏第一格，然后把公式中的所有“F4”改成出生年月日单元格的栏代码如A、B、C、D……和行数如1、2、3、4……。点“回车”即可。

3、利用身份证号码生成年龄：

=IF(LEN(D4)=18,YEAR(TODAY())-MID(D4,7,4),YEAR(TODAY())-MID(D4,7,2)-1900)

说明：自动生成年龄就把以上公式复制粘贴到需自动生成那一栏第一格，然后把公式中的所有“D4”改成身份证号码单元格的栏代码如A、B、C、D……和行数如1、2、3、4……。点“回车”即可。

4、利用身份证号码生成性别一：

=IF(MOD(IF(LEN(I4)=15,MID(I4,15,1),MID(I4,17,1)),2)=1,“男”,“女”)利用身份证号码生成性别二：

=IF(A2<>“",IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“),)

利用身份证号码生成性别三：

=IF(LEN(G6)=15,IF(MOD(VALUE(RIGHT(G6,3)),2)=0,”女“,”男“),IF(LEN(G6)=18,IF(MOD(VALUE(MID(G6,15,3)),2)=0,”女“,”男“),”-“))

说明：自动生成性别就把以上公式复制粘贴到需自动生成那一栏第一格，然后把公式中的所有“I4”或所有“A2”或所有“G6”改成身份证号码单元格的栏代码如A、B、C、D……和行数如1、2、3、4……。点“回车”即可。

5、自动生成编码：

=CONCATENATE(I4,L4,M4)

说明：需要在哪一栏哪一行自动生成编码，就把公式复制粘贴到那一栏的第一格，然后把公式中的“I4,L4,M4”按顺序改成相应单元格的栏代码和行数。点“回车”即可。6、15位身份证号码生成18位身份证号：

=IF(LEN(A2)=15,REPLACE(A2,7，19)&MID(”10X98765432“,MOD(SUM(MID(REPLACE(A2,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11) 1,1),A2)

=REPLACE(A1,7，19)&MID(”10X98765432“,1 MOD(SUM(MID(REPLACE(A1,7，19),ROW($1:$17),1)*2^(18-ROW($1:$17))),11),1)

说明：自动生成18位身份证号就把以上公式复制粘贴到需自动生成那一栏第一格，然后把公式1中的所有“A2”或公式2中的所有“A1”改成15位身份证号码单元格的栏代码如A、B、C、D……和行数如1、2、3、4……。注意：输入公式后不要按回车，而是按Ctrl Shift Enter。

如需整栏自动生成：点到需要生成的那栏的一格，光标指到右下角变成黑色十字架的时候下拉就自动生成了。

7、证号自动生成身份证号：

=MID(I8,1,18)说明：把I8改成证号栏单元格相应栏代码和行数。

分享：分享到新浪Qing

喜欢

阅读(3007)┊ 评论(2)┊ 收藏(0)┊转载(6)┊ 喜欢▼ ┊打印┊举报已投稿到：排行榜圈子

前一篇：民生工程---免费筛查、手术白内障

后一篇：中国*金堂（又新）有机蔬菜采摘暨钓鱼节在又新隆重开幕

评论重要提示：警惕虚假中奖信息|[商讯]我有明星气势签名 [发评论]

 新浪网友2022-10-12 21:52:01 [举报]

非常受用,我想死你了比如身份证号码或者出生日期在A1单元格，可以在B1输入以下公式：（如果是其他单元格，相应改一下就行）

问题1： =DATEDIF(TEXT(MID(A1,7,6 (LEN(A1)=18)*2),(LEN(A1)=15)*19&”0-00-00“),TODAY(),”y“)

问题2： =CHOOSE(MOD(MID(A1,15 (LEN(A1)=18)*2,1),2) 1,”女“,”男“)问题3： =DATEDIF(TEXT(A1,”0-00-00“),TODAY(),”y“)

=IF(LEN(A1)=18,MID(A1,7,4)&”“&MID(A1,11,2)&”“&MID(A1,13,2)&”IF(LEN(A1)=15,MID(A1,7,2)&“年”&MID(A1,9,2)&“月”&MID(A1,11,2)&“日”,“无效身份证号码”))

第二篇：利用身份证号自动提取出生日期和年龄性别

在excel表格中，根据身份证号码就可以自动提取出生年月、性别、年龄。

第一，身份证号码的组成。

当今的身份证号码由18位数字组成：前6位为地址码，第7至14位为出生日期码，第15至17位为顺序码，第18位为校验码。

15~17位为顺序号，其中第17位如果是单数为男性分配码，双数为女性分配码。即通过第17位数字，可以判断性别，奇数为男，偶数为女。

下面以实例的方式讲解如何根据身份证号码自动提取出生年月、性别、年龄。首先，在excel中输入以下表格，实例如下面所示。

姓名

身份证号码

出生日期

性别

年龄

张三

***786

1972年05月03日

女

第二，根据身份证号码自动提取出生年月

在目标单元格输入公式：

=MID(B2,7,4)&“年”&MID(B2,11,2)&“月”&MID(B2,13,2)&“日”，回车确定，单元格内会出现与身份证号码相关的出生日期。其它单元格的填充，不必一一输入公式，只需选中该单元格复制，在其它单元格内粘贴即可。

提示：MID函数是提供的一个“从字符串中提取部分字符”的函数命令。

第三，根据身份证号码自动提取性别的方法

在目标单元格输入公式：

=IF(MOD(RIGHT(LEFT(B2,17)),2),“男”,“女”)

提示：IF函数：根据逻辑表达式测试的结果，返回相应的值。IF函数允许嵌套。

RIGHT函数：根据所指定的字符数返回文本串中最后一个或多个字符。

第四，根据身份证号码自动提取年龄的方法

在目标单元格输入公式：=DATEDIF(TEXT(MID(B2,7,6 2*(LEN(B2)=18)),“#-00-00”),NOW(),“y”)，回车确定。

第三篇：从身份证号中提取性别和年龄

Q：A1单元格中是15位的身份证号码，要在B1中显示性别(这里忽略15位和18位身份证号码的判别）

B1=if(mod(right(A1,1),2)>0,“male”,“female”)请问这个公式有无问题，我试过没发现问题。但在某个网站看到所用的是如下公式：

B1=if(mid(A1,15,1)/2=trunc(mid(A1,15,1)/2）,“female”,“male”)

A：leaf

道理都是一样的，不过你的公式比那个公式优质

提取性别（无论是15位还是18位）

=IF(LEN(A1)=15,IF(MOD(MID(A1,15,1),2)=1,“男”,“女”),IF(MOD(MID(A1,17,1),2)=1,“男”,“女”

如果身份证号的输入已是15或18位，用公式=IF(MOD(LEFT(RIGHT(A1,(LEN(A1)=18) 1)),2),“男”,“女”

如何从身份证号码中提取出生日期、年龄和性别

有许多时候，我们需要批量从身份证号码中提取村民的出生日期、年龄，有时也要标明其

性别。

其实这些信息在每人特定的身份证号码中都有体现，如众所周知的，身份证第7-14位标明的是此人的出生年月日，18位的倒数第2位或15位的倒数第1位是性别识别码，因此根据这一原理，通过使用excel的函数关系就可以分别提取出来。

1、先来看看如何提取出生年月日

例如某人身份证号码是***886，我们可以通过以下公式进行提取：在excel表格中需要放置出生年月日数据的单元格内输入“=MID(F2,7,4)&“-”&MID(F2,11,2)&“-”&MID(F2,13,2)”（不含引号，其中蓝色突出标识的为你所引用数据，即身份证号码所在的单元格，下同）回车即可提取到1980-02-14格式的出生年月日了，如果需要1980年2月14日格式的，可以用下面的公式“=MID(F2,7,4)&“年”&MID(F2,11,2)&“月”&MID(F2,13,2)&“日””（不含引号）

2、提取性别信息

可以在相应的单元格内输入“=IF(MOD(RIGHT(LEFT(F2,17)),2),“男”,“女”)”，以上面的身份证

号码为例，得到结果应该是“女”。

3、提取年龄

虽然我们无法直接从身份证号码中看出具体年龄，但可以通过稍加转换即可实现。

可以在相应的单元格内输入“=2022-MID(F2,7,4)”，以上面的身份证号码为例，得到结果应该

是“27”。注：其中2022为统计当年年份

第四篇：关于身份证性别年龄查询公式(03版)

关于身份证号码的检测

1、身份证中有X的需大写。

2、检测身份证正误的公式

以身份证号在E列5行为例

=IF(OR(LEN(E5)=15,IF(LEN(E5)=18,MID(“10X98765432”,MOD(SUM(MID(E5,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)*2^(18-{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17})),11) 1,1)=RIGHT(E5))),“身份证正确”,“身份证错误”)

复制到最后一列，复制完成后必须用ctrl shift 回车（也可直接回车），这是数组函数，必这样，然后一直往下拉就行了

3、查重复公式

以身份证号在E列5行为例，结果显示：1证明没有重复。显示：2证明身份证号有错误，检索一下就能发现。

=SUMPRODUCT(N(CLEAN($E$6:E$6000)=CLEAN(E6)))

4、这些仅用来检查身份证的，至于姓名正误和个人其他信息正误，一定要人工认真检查。、15位或者18位身份证性别公式

=IF(MOD((IF(LEN(E6)=18,MID(E6,17,1),MID(E6,15,1))),2)=0,“女”,“男”)

6、15位或者18位身份证年龄公式

=YEAR(TODAY())-(IF(LEN(E6)=18,MID(E6,7,4),“19”&MID(E6,7,2)))

7、15位或者18位身份证出生年月公式

=IF(LEN(H3)=18,MID(H3,7,4)&“-”&MID(H3,11,2),“19”&MID(H3,7,2)&“-”&MID(H3,9,2))

第五篇：在Excel中根据身份证号码自动生成出生生日期和性别

在Excel中根据身份证号码自动生成出生生日期和性别

【身份证号判断性别的方法】 18位的看倒数第二位，奇男偶女； 15位的看倒数第一位，奇男偶女。假如：身份证所在的列为C2

首先要判断该身份证的长度，在excel中使用len函数来获取len(C2)

如果是18位的就取第17位，即倒数第二位，Excel中使用mid函数还获取指定位置开始，指定长度的字符串，如果是15位的就去最后一位也就是第15位 =If(len(C2)=15,mid(C2,15,1),mid(C2,17,1))

获取到这个数后，再判断该数是奇数还是偶数就可以得出性别了，excel中使用mod函数实现 =IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,“男”,“女”)获取年龄的函数如下：

=DATEDIF(TEXT((LEN(C2)=15)*19&MID(C2,7,6 (LEN(C2)=18)*2),“00-00-00”),TODAY(),“y”)

在Excel中根据身份证号码自动生成出生生日期和性别根据身份证号码自动生成出生生日期方法一：

15位身份证号：*** 输出出生日期1979/06/05 =CONCATENATE(“19”,MID(E2,7,2),“/”,MID(E2,9,2),“/”,MID(E2,11,2))公式解释： a.MID(E2,7,2)为在身份证号码中获取表示年份的数字的字符串

b.MID(E2,9,2)为在身份证号码中获取表示月份的数字的字符串 c.MID(E2,11,2)为在身份证号码中获取表示日期的数字的字符串

d.CONCATENATE(“19”,MID(E2,7,2),“/”,MID(E2,9,2),“/”,MID(E2,11,2))目的就是将多个字符串合并在一起显示。

18位身份证号：***521 输出出生日期1979/06/05 =CONCATENATE(MID(E2,7,4),“/”,MID(E2,11,2),“/”,MID(E2,13,2))方法二：

15位身份证号：*** 出生日期790605 =IF(LEN(E3)=15,MID(E3,7,6),MID(E3,9,6))18位身份证号：***521 出生日期790605 =IF(LEN(E3)=15,MID(E3,7,6),MID(E3,9,6))公式解释：

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

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

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

=IF(LEN(E2)=15,MID(E2,7,6),MID(E2,9,6))：IF是一个逻辑判断函数，表示如果额E2单元格是15位，则提取第7位开始的6位数字，如果不是15位则提取自第9位开始的6位数字。根据身份证号码自动生成性别：方法一：

在C列输入身份证号，在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单元格显示为“男”，否则显示为“女”。

15位身份证，看最后一位，奇男偶女；18位的，看第17位数，也是奇男偶女。方法二：

如果你是想在Excel表格中，从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,“男”,“女”),IF(MOD(MID(C2,17,1),2)=1,“男”,“女”))公式内的“C2”代表的是输入身份证号码的单元格。

《身份证号自动生成年龄、出生年月、性别公式[我整理].doc》

下载本文的Word文档后，方便收藏和打印

推荐度：