EXCEL中根据身份证号计算出生日期、年龄、性别、月份间隔
用身份证号计算性别、出生日期、周岁和年龄
用身份证号计算性别、出生日期、周岁和年龄
上篇《必懂！Excel中长字符串的秘密》提到在Excel中录入长字符串的技巧，实际工作上，身份证这种长字符串运用最多，今天就来聊这个。
身份证上的信息有很多种，今天提取一些常用的特征进行说明。
1.性别：第17位为奇数是男，偶数是女。
=IF(MOD(MID(B2,17,1),2),"男","女")
2.出生日期：7~10位为出生年份，11~12位为出生月份，13~14位为出生日。
=TEXT(MID(B2,7,8),"0-00-00")
3.周岁：
=DATEDIF(D2,TODAY(),"y")
4.年龄：
=DATEDIF(D2,TODAY(),"y")&"岁
"&DATEDIF(D2,TODAY(),"ym")&"个月"
第3、4的TODAY函数也可以改为NOW函数，效果一样。
字符提取，左边用LEFT函数，中间用MID函数，右边用RIGHT 函数。
还不熟练推荐看看视频。
作者：卢子，清华畅销书作者，《Excel效率手册早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）。
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"&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表格提取身份证详细信息前些天领导要求统计所有员工的性别、出生日期、年龄等信息，并且要得很急。
而我们单位员工人数众多，短时间内统计相关信息并且输入计算机几乎是不太可能的。
幸好在以前的一份金山表格中我们曾经统计有所有员工的身份证号码，而身份证中正有我们所需要的性别、出生日期、年龄等信息的。
所以，干脆，还是直接在金山表格中从身份证号码提取相关的信息吧。
身份证号放在A2单元格以下的区域。
我们需要从身份证号码中提取性别、出生日期、年龄等相关信息。
由于现在使用的身份证有15位和18位两种。
所以，在提取相关信息时，首先应该判断身份证号码的数字个数，然后再区别不同情况进行相关处理。
身份证输入年龄性别出生年月
1 、在excel中根据身份证号码自动填充出生年月的公式：=IF(LEN(E5)=15,MID(E5,7,2)&" 年"&MID(E5,9,2)&"月"&MID(E5,11,2)&"日",MID(E5,7,4)&"年"&MID(E5,11,2)&"月"&MID(E5,13,2)&"日")E5 是身份证号所在单元格，在出生年月单元格输入上面的公式，就提取出日期，显示为“xxxx年xx月xx日”。
如果不需要提取日期，就把“月”后面的部分公式去掉就可以了。
或者要显示为“xxxx-xx-xx”，只需要将相应的“年”“月”“日”替换为“-”就可以了。
2 、在excel中根据身份证号码自动填充性别的公式：=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)), 2)=1," 男","女")A2 是身份证号所在单元格，在性别单元格输入上面的公式，自动填充性别。
3 、在excel中根据身份证号码自动填充年龄的公式：①=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN( A1)=18)*2),"00-00-00"),TODAY(),"y" )②=YEAR(TODAY())-VALUE(MID(E4,7,4))A1 是身份证号所在单元格，在年龄单元格中输入上面的公式，计算出截止今天的年龄。
小技巧：要想整列数据都使用公式，只需要按住设置了公式的单元格右下角的“+”往下拉就可以了。
在EXCEL中根据身份证号计算出生年月日、年龄
在EXCEL中根据身份证号计算年龄、出生年月、性别在EXCEL中如何根据身份证号计算年龄、出生年月、性别呢？下面来介绍一下，希望对您有帮助1.根据身份证号计算年龄=IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(L EN(A2)=18)*2),"#-00-00"),TODAY(),"y"),)注意：A2单元格为身份证号，且为文本格式。
2.根据身份证号计算出生年月日第一种，计算出来的格式为××年××月××日=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"年"&MID(A2,9,2)&"月"&MID(A2,11,2)&"日",MID(A2,7,4)&"年"&MID(A2,11,2)&"月"&MID(A2,13,2)&"日")第二种，计算出来的格式为××年××月=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"年"&MID(A2,9,2)&"月",MID(A2,7,4)&"年"&MID(A2,11,2)&"月")3.根据身份证号计算性别=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,"男","女")计算出生年月日的公式=TEXT(TEXT(MID(D2,7,LEN(D2)/2-1),"#-00-00"),"e-m-d") D2为身份证。
教你用Excel表把身份证号中提取出“出生日期”“性别”“籍贯”
教你⽤Excel表把⾝份证号中提取出“出⽣⽇期”“性别”“籍贯”⾝份证号码中包含每个⼈的“出⽣⽇期”，但⼤多数⼈还不知道这⼀串数字中还包含了很多隐藏的信息,今天,我们就⽤EXCEL从中“探囊取物”，⼀起看个究竟！今天的主题：⽤excel函数和公式，从⾝份证号中提取出“出⽣⽇期”“性别”“籍贯”，并计算“当前年龄”。
1、从⾝份证号中提取“出⽣⽇期”=--Text(mid($B3,7,8),'0-00-00')使⽤函数：text、mid要点提⽰：（1）利⽤mid函数，从18位⾝份证号码中，取出从第7位开始的8个数字（2）利⽤text函数，将取出的8个数字，⽣成⽇期的数值形式（3）最后，通连续的减号“--”，将数值转换成⽇期的⽂本形式（4）依次往下复制、填充补充：excel中⽇期规范形式：“2018-5-7”、“2018/5/7”，不能写成“20180507”、'2018.5.7'、'2018、5、7'2、从⾝份证号中提取“性别”⽅法⼀：=IF(MID($B3,17,1)/2=TRUNC(MID($B3,17,1)/2),'⼥','男')⽅法⼆：=IF(MOD(MID(M2,17,1),2),'男','⼥')使⽤函数：if 、mid、trunc、mod要点提⽰：（1）利⽤if函数，判断⾮“男”，即“⼥”（2）if函数的“判断条件”，⽤mid提取出的第17位数除以2，看是否等于trunc函数判断第17位数除以2的整数部分（3）或者直接⽤mod函数，对第17位数除以2，求“余数”，>0为“男”，=0为“⼥”（4）依次往下复制、填充补充知识：⾝份证号倒数第2位数，奇数为“男”，偶数为“⼥”3、计算⾝份证号当事⼈当前“年龄”⽅法⼀：插⼊P列、Q列辅助列“P3”单元格输⼊：=year(today())“Q3”单元格输⼊：=mid(O3,7,4)“R3”单元格输⼊：=P3-Q3⽅法⼆：=YEAR(TODAY())-YEAR(--TEXT(MID($B3,7,8),'0-00-00'))使⽤函数：year、today、text、mid要点提⽰：1）today()指的是电脑的当前⽇期，再利⽤year函数，返回当前的年份值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"&TEXT(MID(e2,7,6),"#-0 0-00"))如果要想把“-”换成“年月日”，则公式可改为=IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#年00月00日"),"19"&TEXT(MID(e2,7,6),"#年00月00日"))15/18位自动录入男女：=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男"))计算年龄（新旧身份证号都可以）：=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",109-MIDB(E2,9,2),109-MIDB(E2,7 ,2)))说明：109表示当前日期是2009年，如果是2000年后出生的，要从身份证号码中计算年龄，则公式可改为=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",109-MIDB(E2,9,2),09-MIDB(E2,9, 2))) (此公式只适合18位新身份证)。
(完整word版)EXCEL利用身份证号码计算年龄以及年龄分段的技巧
在EXCEL中如何利用身份证号码计算出生年月年龄及性别1、身份证号码简介（18位):1~6位为地区代码;7～10位为出生年份；11～12位为出生月份；13～14位为出生日期；15~17位为顺序号，并能够判断性别，奇数为男，偶数为男；第18位为校验码.2、确定“出生日期”：18位身份证号码中的生日是从第7位开始至第14位结束。
提取出来后为了计算“年龄"应该将“年”“月”“日”数据中添加一个“/"或“—”分隔符。
①正确输入了身份证号码。
（假设在D2单元格中）②将光标定位在“出生日期”单元格（E2)中,然后在单元格中输入函数公式“=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）”即可计算出“年龄"。
关于这个函数公式的具体说明：①TODAY函数用于计算当前系统日期。
只要计算机的系统日期准确,就能立即计算出当前的日期,它无需参数。
Excel公式计算出生日期、年龄、性别、工作年限、日期格式转换
Excel公式计算出生日期、年龄、性别、工作年限、日期格式转换根据身份证号计算出生日期、年龄、性别身份证号出生日期年龄性别入司时间入司时间2工作年限1330311957092639161957-09-2656男2002.05.012002-05-0111年7个月11天2002.05.012002-5-111年7个月11天性别身份证号性别133031************男出生日期身份证号出生日期1330311957092639161957-09-26年龄身份证号出生日期年龄1330311957092639161957-09-2656工作年限入司时间1入司时间2工作年限2002.05.012002-05-0111年7个月11天2002.05.012002-5-111年7个月11天初始格式目标格式1957-9-261957.09.261957.09.261957-09-26计算工作年限#=TEXT(SUBSTITUTE(A26,"-","."),"yyyy.mm.dd")#=TEXT(SUBSTITUTE(A27,".","-"),"yyyy-mm-dd")公式（不含“#”）↓ 分解↓日期格式转换公式（不含“#”）#=IF(VALUE(MID(A9,15,3))/2=INT(VALUE(MID(A9,15,3))/2),"女公式（不含“#”）#=MID(A13,7,4)&"-"&MID(A12,11,2)&"-"&MID(A12,13,2公式（不含“#”）#=INT((TODAY()-B17)/365)1.选择需要转换单元格或区域，单击菜单“数据→分列”，弹出“文本分列向导- 3之步骤1”对话框。
如果是Excel 2007，则在功能区中选择“数据”选项卡，在“数据工具”组中，单击“分列”按钮。