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和金山电子表格通用)通过身份证号码提取年龄性别出生年月(excel和金山电子表格通用)电脑知识 2009-04-27 17:11:49 阅读365 评论0 字号:大中小订阅15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。
18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。
从身份证号码中提取出表示出生年、月、日的数字,用文本函数MID()可以达到目的。
MID()——从指定位置开始提取指定个数的字符(从左向右)。
对一个身份证号码是15位或是18位进行判断,用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。
综合上述分析,可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取:假如身份证号数据在A1单元格,在B1单元格中编辑公式=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),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,7,4)&"-"&MID(A1,11,2))-------------------------------------------------------------------------------------------------------------------------这是根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式,供需要的网友参考:说明:公式中的B2是身份证号1、根据身份证号码求性别:=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,"女","男"),"身份证错"))2、根据身份证号码求出生年月:=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2, 9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),".",MID(B2,11,2)) ,"身份证错"))3、根据身份证号码求年龄:=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),"身份证错"))----------------------------------------------------------------------------------------------------------------------------接下来我们应将光标移至D3单元格中,然后输入“=IF(MID(B3,15,1)/2=TRUNC(MID(B3,15,1)/2),"女","男")”。
在Excel中自动提取身份证中生日和性别
在Excel中自动提取身份证中生日和性别
1.转换身份证号码格式
我们先将学生的身份证号完整地输入到Excel2003表格中,这时默认为“数字”格式(单元格内显示的是科学记数法的格式),需要更改一下数字格式。
选中该列中的所有身份证号后,右击鼠标,选择“设置单元格格式”。
在弹出对话框中“数字”标签内的“分类”设为“文本”,然后点击确定。
2.“提取出”出生日期
=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"
/判断性别“男女”
=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")
求年龄问题,要看你的数据是哪种格式(日期、文本),举例说明:假定你的A1单元格为日期格式,内容为“1986-2-20”,在B1单元格可
输入如下公式得到年龄:
=INT((TODAY()-A1)/365)。
用EXCEL函数从身份证号中提取生日及计算年龄
用EXCEL函数从身份证号中提取生日及计算年龄
用EXCEL函数从身份证号中提取生日及计算年龄
众所周知,身份证号(如:371102************)18位的,从第7位到14位为生日(15位的则到12位)。
假设身份证号在A2,则提取生日、计算周岁的函数公式如下:
函数公式:
A取生日函数公式:
TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")
注释:如果A2位数为15位首部写作19,从A2第7位开始提取6位(如果A2长度为18位位数加2),以#-00-00文本格式输出。
B算周岁函数公式:
DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18) *2),"#-00-00"),T ODAY(),"Y")
注释:求今日与生日的相差年数,不足1年不计(即不过生日的那年不算)。
如何在Excel中填写身份证号码后自动提取出生年月日、年龄、性别
如何在Excel中填写身份证号码后自动提取出生年月日、年龄、性别本公式只适合于18位身份证号码即二代居民身份证。
假如:A1输入身份证号码、B1显示性别、C1显示出生日期、D1显示周岁。
(要注意A1、B1、C1、D1分别对应A列1行B列1行C列1行D列1行,如你的A1单元格不为你的表格第一项身份证编号则需将A1替换成你选取的单元格)需要设置:A1单元格为文本格式、B1和D1为常规格式、C1为日期格式。
(选中列右键设置单元格格式可选取格式)1、显示性别:B1输入公式:=IF(A1="","",IF(AND(LEN(A1)<>15,LEN(A1)<>18),"错误",IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))<VALUE ("1905-01-01"),(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))> TODAY()),"错误",IF(MOD(MID(A1,15+(LEN(A1)=18)*2,1),2)=0,"女","男")))))2、C1输入公式:=IF(A1="","",IF(AND(LEN(A1)<>15,LEN(A1)<>18),"错误",IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))<VALUE ("1905-01-01"),(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))> TODAY()),"错误",VALUE(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#年00月00日"))))))3、D1输入公式:=IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))<VALUE ("1905-01-01"),(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))> TODAY()),"错误",DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(), "y")))注意:A1、B1、C1、D1分别对应A列1行B列1行C列1行D列1行,如你的A1单元格不为你的表格第一项身份证编号则需将A1替换成你选取的单元格如下图那么上述B1、C1、D1输入函数中应将A1替换成F4即可。
电子表格中身份证号提取生日和年龄汇总
电子表格中身份证号提取生日和年龄汇总注:(以下公式假设身份证在A1列)1、身份证在excel里面输入的时候,18位的只能以文本方式输入或者前加‘单引号,如果是文本怎么提取?如果有’等其他符号如何提取?如果只提取年份yyyy 和只提取月日mm-dd怎么提取?a、如果是文本则输入此公式:=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"00-00 -00")b、如果是前加’附号的,那就试试这个公式:=TEXT((LEN(A1)<=16)*19&MID(A1,7,6+(LEN(A1)>=18)*2),"00-00-00")c、如果只提取年份:=YEAR(TEXT((LEN(A1)<=16)*19&MID(A1,7,6+(LEN(A1)>=18)*2) ,"00-00-00"))d、如果只提取月日:=TEXT(TEXT((LEN(A1)<=16)*19&MID(A1,7,6+(LEN(A1)>=18)*2) ,"00-00-00"),"mm-dd")补充:如果身份证输入时采用了特殊格式如510182000000000000自动填入,则将上述第二个公式改一下:=TEXT((LEN(A1)<=16)*19&MID(A1,3,6+(LEN(A1)>=18)*2),"00-00-00")-----------------------------------------------------2、假设你的生日是此格式*年*月*日,要修改为如下格式,则:a、如需修改为**.**的表达式如下:=CONCATENATE(TEXT(A1,"yyyy"),".",TEXT(A1,"mm"))b、如需修改为**.**.**的表达式如下:=CONCATENATE(TEXT(A1,"yyyy"),".",TEXT(A1,"mm"),".",TEXT (A1,"dd"))c、如需修改为**-**-**的表达式如下:=CONCATENATE(TEXT(A1,"yyyy"),"-",TEXT(A1,"mm"),"-",TEXT (A1,"dd"))------------------------------------------------------3、身份证号或日期提取年龄a、直接从身份证提取当年的年龄:=2012-MID(A1,7,4)b、假设你的日期格式为1970-3-27,则输入如下公式转化为你当天的年龄:=INT((TODAY()-A1)/365)。
Excel表格身份证号码提取出生日期的公式
Excel表格身份证号码提取出生日期的公式(B2表示身份证号码所在的列位置)=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)回车→向下填充1. Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2. 15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。
3. 18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。
一、提取出生年月:A、15位身份证号码:=MID(B2,7,2)&"-"&MID(B2,9,2)&"-"&MID(B2,11,2) 回车确认即可。
B、18位身份证号码:=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&”日”回车确认即可。
二、提取性别:18位身份证号码:=IF(MOD(MID(B2,17,1),2)=1,"男","女")回车确认即可。
*excel公式中=IF(MOD(MID(E4,17,1),2)=0,"女","男")是什么意思?IF是选择函数,当MOD(MID(E4,17,1),2)=0成立时,单元格显示“女”,否则显示“男”。
MOD是取模函数,即是一个求余函数,求MID(E4,17,1)除以2的余数。
实质是判断MID(E4,17,1)的奇偶性。
MID从一个文本字符串的指定位置开始,截取指定数目的字符。
MID(E4,17,1)是从E4单元格的文本中的第17个字符开始,取一个字符。
三、提取年龄:=year(today())-value(right(left(B2,10),4)) 回车确认即可。
Excel:根据身份证号码提取性别、年龄、出生年月日,建议收藏
Excel:根据身份证号码提取性别、年龄、出生年月日,建议收藏
Excel:根据身份证号码提取性别、年龄、出生年月日,建议收藏性别打开Excel,在表格中输入身份证信息,如图在二代身份证中的第17位是性别信息,奇数为男,偶数为女。
在【G2单元格】输入【=IF(MOD(MID(B2,17,1),2)=1,”男”,”女”)】
输入完成后按【回车Enter】,即可得到结果然后选中【G2】单元格,并把鼠标放置到G2单元格的右下角,当鼠标变成如图【粗加号】时,一直按【左键】向【下拉】,即可得到全部的性别信息
出生年月日、年龄二代身份证号的第7~14位数字表示:出生年、月、日。
在【C2单元格】中输入【MID(B2,7,4) 】
输入完成后按【回车Enter】,即可得到【出生年】在【D2单元格】中输入【MID(B2,11,2) 】输入完成后按【回车Enter】,即可得到【月】在【E2单元格】中输入【=MID(B2,13,2) 】输入完成后按【回车Enter】,即可得到【日】如果需要计算年龄,可以使用当年的年份(例如2018)减去提取出来的年,即在【F2单元格】中输入【=2018-MID(B2,7,4)】
输入完成后按【回车Enter】,即可得到【年龄】。
HR必备Excel轻松提取出生日期、性别、年龄和地区代码.doc
HR必备Excel轻松提取出生日期、性别、年龄和地区代码1、提取出生日期方法一:函数法如下动图所示,输入公式:=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)) 解析:身份证号码第7位至第14位(即第7位开始的前4个数字代表出生年份,第11和12为数字代表出生月份,第13和第14为代表出生日期)是出生年月日信息,这里我们用到Mid 函数线分别提取出身份证信息中的年、月和日,再结合Date函数即可。
或者如下动图所示,输入公式:=TEXT(MID(A3,7,8),0-00-00)解析:这里我们用Mid 函数直接提取出年月日信息,再结合Text函数转换到指定的日期格式。
方法二:分列法【数据】选项卡下的“分列”:固定宽度-在8位出生年月日前后建立分列线-保留代表生日的8位数字并设置成日期格式。
具体操作如下动图:方法三:快速填充如果是多个身份证信息的提取,可以利用快速填充法(2016版),非常便捷!在生日列第1个空格中输入第1个身份证号码中的8位生日数字,然后按Ctrl+E键(快速填充快捷键)向下填充。
最后设置自定义格式为:0-00-002、判断性别如下动图所示,分别输入公式:=IF(MOD(MID(A2,17,1),2),男,女)= IF(MOD(MID(A3,17,1),2),男,女)解析:身份证号码中的第17位数字是用来判断男女性别,如果是奇数性别为男,偶数则为女。
先用Mid函数提取出数字,结合Mod函数判断奇偶(Mod函数是求两个数相除的余数,若可以被2整除,余数0,为偶,否则为奇),最后再用If函数判断男女性别。
3、计算年龄如下动图所示,输入公式:=YEAR(TODAY())-MID(A2,7,4);=YEAR(TODAY())-MID(A3,7,4)解析:先用Mid函数提取出生年份,当前年份减去出生年份即可。
4、提取地区代码身份证号码信息中,前2位是省份代码,第3和第4位是城市代码,第5和6位是取现代码,我们可以利用Left函数进行数字提取,比如提取区县代码:=Left(A2,6),再根据代码在各区编码信息中查询对应的城市区县。
EXCEL根据身份证号码自动提取性别、出生年月日和年龄(通用公式)
EXCEL根据身份证号码自动提取性别、出生年月日和年龄(通用公式)EXCEL根据身份证号码自动提取性别、出生年月日和年龄(通用公式)这是根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式,供需要的网友参考:说明:公式中的B2是身份证号1、根据身份证号码求性别:=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,"女","男"),"身份证错"))2、根据身份证号码求出生年月日:=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2, 9,2)),IF(LEN( B2)=18,CONCATENATE(MID(B2,7,4),".",MID(B2,11,2) ),"身份证错"))3、根据身份证号码求年龄:=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year (now())-VALUE(MID(B2,7,4)),"身份证错"))其他通用公式:(新旧身份证号通用)(1)、根据“身份证号”获得“年龄”A1为身份证号,B1中输入公式=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=1 8)*2),"#-00-00"), TODAY(),"y")(2)、根据出生“年-月-日”获得“年龄”B1为出生日期,C1中输入公式=INT((TODAY()-B1)/365)或者=DATEDIF(B1,TODAY(),"Y")判断身份证格式是否正确:B1为身份证号,C1中输入公式=IF(OR(LEN(B1)=18,LEN(B1)=15),IF(LEN(B1)=18,IF(OR(MON TH(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))),"月日错误",""),IF(LEN(B1)=15,IF(OR(MONTH(DATE(1*(MID(B1,7,2))+19 00,1*(MID(B1,9,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))),"月日错误",""))),"身份证位数不对")&IF(AND(LEN(B1)=18,1*MID(B1,7,4)>YEAR(TODAY())-6),"年份错误","")。
EXCEL中通过身份证号读取出生年月日和性别
EXCEL中通过身份证号读取出生年月日和性别案例1:从身份证号中提取出生年、月、日=DATE(MID(K48,7,4),MID(K48,11,2),MID(K48,13,2))公式解读:从K48单元格中,分别读取从第7个字符开始的4个字符(年)、从第11个字符开始的2个字符(月)和从第13个字符开始的2个字符(日),返回并以日期格式显示。
1)DATE日期函数的基本构型是DATE(year,month,day),其作用是将提取的数字变为日期格式进行显示。
2)MID提取函数的基本构型是MID(text, start_num, num_chars),其作用是从text 所示的字符串中,从左边开始数,第start_num个位置开始,截取num_chars个的字符。
例如案例中MID(K48,7,4)是在K48单元格中,从左边第7个字符开始提取4个字符,提取的结果为:1993。
案例2:从身份证号中提取性别=IF(MOD(MID(K49,17,1),2)=1,"男","女")公式解读:读取K49单元格中,从第17个字符开始的1个字符,并用这个数除以2求余数,如果余数为1则是基数,性别为男,否则性别为女。
1)IF条件函数的基本构型是IF(logical_tes,[value_if_true],[value_if_false]),其作用是条件判断logical_tes,如果是真返回[value_if_true]值,如果是假[value_if_false]值。
例如if(B2>=60, "合格","不合格"),如果B2单元格数字大于等于60,返回"合格"否则反馈"不合格"。
2)MOD求余函数的基本构型是MOD(nExp1,nExp2),其作用是两个数值表达式作除法运算后的余数.例如MOD(4,2),返回值为0。
Excel表格身份证号码提取出生日期的公式
Excel表格身份证号码提取出生日期的公式(B2表示身份证号码所在的列位置)=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)回车→向下填充1. Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2. 15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。
3. 18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。
一、提取出生年月:A、15位身份证号码:=MID(B2,7,2)&"-"&MID(B2,9,2)&"-"&MID(B2,11,2) 回车确认即可。
B、18位身份证号码:=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&”日”回车确认即可。
二、提取性别:18位身份证号码:=IF(MOD(MID(B2,17,1),2)=1,"男","女")回车确认即可。
*excel公式中=IF(MOD(MID(E4,17,1),2)=0,"女","男")是什么意思?IF是选择函数,当MOD(MID(E4,17,1),2)=0成立时,单元格显示“女”,否则显示“男”。
MOD是取模函数,即是一个求余函数,求MID(E4,17,1)除以2的余数。
实质是判断MID(E4,17,1)的奇偶性。
MID从一个文本字符串的指定位置开始,截取指定数目的字符。
MID(E4,17,1)是从E4单元格的文本中的第17个字符开始,取一个字符。
三、提取年龄:=year(today())-value(right(left(B2,10),4)) 回车确认即可。
根据身份证号码提取出生日期,性别,年龄
根据身份证号码提取出生日期,性别,年龄根据身份证号码提取出生日期,性别,年龄在EXCEL中根据身份证号码提取出生日期,性别,年龄1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2.15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。
18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。
从身份证号码中提取出表示出生年、月、日的数字,用文本函数MID()可以达到目的。
MID()――从指定位置开始提取指定个数的字符(从左向右)。
对一个身份证号码是15位或是18位进行判断,用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。
综合上述分析,可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取:假如身份证号数据在C2单元格根据身份证号码求出生年月:=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))或者=TEXT(MID(J2,7,8),"0000-00-00")格式是1962-12-23=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)) “日”格式是1962年12月23日=IF(LEN(C2)=15,"19"MID(C2,7,2)MID(C2,11,2)MID(C2,13,2))格式是***-*****3.这是根据身份证号码(15位和18位通用)自动提取性别的自编公式:说明:公式中的C2是身份证号根据身份证号码求性别:=IF(LEN(C2)=15,IF(MOD(VALUE(RIGHT(C2,3)),2)=0,"女",""),IF(LEN(C2)=18,IF(MOD(VALUE(MID(C2,15,3)),2)=0,"女","男"),"身份证错"))男MID(C2,9,2) MID(C2,11,2),MID(C2,7,4)根据身份证号码提取出生日期,性别,年龄根据身份证号码求年龄=IF(LEN(C2)=15,109-VALUE(MID(C2,7,2)),if(LEN(C2)=18,2009-VALUE(MID(C2,7,4)),"身份证错"))。
Excel表格中身份证号码提取出生年月、性别、年龄等的使用技巧.pdf
Excel表中身份证号码提取出生年月、性别、年龄的使用技巧方法一:1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2.15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。
18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。
从身份证号码中提取出表示出生年、月、日的数字,用文本函数MI D()可以达到目的。
MID()——从指定位置开始提取指定个数的字符(从左向右)。
对一个身份证号码是15位或是18位进行判断,用逻辑判断函数IF ()和字符个数计算函数LEN()辅助使用可以完成。
综合上述分析,可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取:假如身份证号数据在A1单元格,在B1单元格中编辑公式=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),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,7,4)&"-"&MID(A1,11,2))3.这是根据身份证号码(15位和18位通用)自动提取性别的自编公式,供需要的朋友参考:说明:公式中的B2是身份证号根据身份证号码求性别:=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女", "男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0, "女","男"),"身份证错"))根据身份证号码求年龄:=IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007-VALUE(MID(B2,7,4)),"身份证错"))4.Excel表中用Year\Month\Day函数取相应的年月日数据;方法二:这是根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式,供需要的网友参考:说明:公式中的B2是身份证号1、根据身份证号码求性别:=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女", "男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0, "女","男"),"身份证错"))2、根据身份证号码求出生年月:=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),".",MID(B2,11,2)),"身份证错"))3、根据身份证号码求年龄:=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),"身份证错"))早期的身份证号码为15位数字,现在使用的身份证号码为18位数字,它们的含义如下:(1)15位:1-6位为地区代码,7-8位为出生年份(2位),9-10位为出生月份,11-12位为出生日期,第13-15位为顺序号,这3位奇数为男,偶数为女。
Excel中利用身份证号码(15或18位)提取出生日期和性别
Excel中利用身份证号码(15或18位)提取出生日期和性别需要的函数:LEN(C6)=15:检查C6单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位;INT:返回数值向下取整为最接近的整数,本例中用来判断身份证里数值的奇偶数。
RIGHT:返回文本字符串最后一个字符开始指定个数的字符;MID:返回文本字符串指定起始位置起指定长度的字符,MID(C6,7,2)表示:在C3中从左边第七位起提取2位数;"19"&MID(C6,7,2)表示:在C3中从左边第七位起提取2位数的前面添加19;……&""&表示:其左右两边所提取出来的数字不用任何符号连接;&"-"&表示:其左右两边所提取出来的数字间用“-”符号连接。
若需要的日期格式是yyyy年mm月dd日,则可以把公式中的“-”分别用“年月日”进行替换就行了。
一、提取出生日期如果我们要从一个人的身份证号码中批量提取其出生年月日,并表示成“yyyy-mm-dd”形式,可以这样做,假设身份证号码在C列,在D列中输入公式=IF(LEN(C6)=15,"19"&MID(C6,7,2)&"-"&MID(C6,9,2)&"-"&MID(C6,11,2),MID(C6,7,4)&"-"&MID(C6,11,2)&"-"&MID(C6,13,2)),这个公式的含义就是,当其检查到C6单元格中的数据是15位的时,就显示"19"&MID(C6,7,2)&"-"&MID(C6,9,2)&"-"&MID(C6,11,2)的计算结果,否则就显示MID(C6,7,4)&"-"&MID(C6,11,2)&"-"&MID(C6,13,2)的计算结果。
Excel中根据身份证号码自动填出生日期、性别、年龄
Excel中根据身份证号码自动填出生日期、性别、计算年龄
出生日期: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,"女","男")
计算年龄:
=IF(LEN(B2)=15,109-V ALUE(MID(B2,7,2)),if(LEN(B2)=18,2009-V ALUE(MID(B2,7,4)),"身份证错"))
说明:V ALUE将一个文本字符串转换成数值,LEN返回文本字符串中的字符个数,MID从文本字符串中指定的起始位置起返回指定长度的字符,MID(文本,开始字符,所取字符数)。
109表示当前日期为2009年,如果是2010年则改为110,2009表示当前日期为2009年。
DA TE代表日期的数字,MIDB自文字的指定起始位置起提取指定长度的字符串。
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
方法一:1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2.15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。
18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。
从身份证号码中提取出表示出生年、月、日的数字,用文本函数MID()可以达到目的。
MID()——从指定位置开始提取指定个数的字符(从左向右)。
对一个身份证号码是15位或是18位进行判断,用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。
综合上述分析,可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取:假如身份证号数据在A1单元格,在B1单元格中编辑公式=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),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,7,4)&"-"&MID(A1,11,2))3.这是根据身份证号码(15位和18位通用)自动提取性别的自编公式,供需要的朋友参考:说明:公式中的B2是身份证号根据身份证号码求性别:=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0,"女","男"),"身份证错"))根据身份证号码求年龄:=IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007-VALUE(MID(B 2,7,4)),"身份证错"))4.Excel表中用Year\Month\Day函数取相应的年月日数据;方法二:这是根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式,供需要的网友参考:说明:公式中的B2是身份证号1、根据身份证号码求性别:=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,"女","男"),"身份证错"))2、根据身份证号码求出生年月:=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),".",MID(B2,11,2)),"身份证错"))3、根据身份证号码求年龄:=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year (now())-VALUE(MID(B2,7,4)),"身份证错"))早期的身份证号码为15位数字,现在使用的身份证号码为18位数字,它们的含义如下:(1)15位:1-6位为地区代码,7-8位为出生年份(2位),9-10位为出生月份,11-12位为出生日期,第13-15位为顺序号,这3位奇数为男,偶数为女。
(2)18位:1-6位为地区代码,7-10位为出生年份(4位),11-12位为出生月份,13-14位为出生日期,第15-17位为顺序号,同上,第18位为效验位。
在做员工档案管理的工作时,有时希望通过身份证号码,自动提取出各个员工的出生日期、性别、年龄等信息。
现在我们就通过几个公式,来得到这些信息。
1、出生日期=IF(LEN(A2)=15,CONCATENATE("19",MID(A2,7,2),"/",MID(A2,9,2),"/",MID(A 2,11,2)),CONCATENATE(MID(A2,7,4),"/",MID(A2,11,2),"/",MID(A2,13,2))) LEN函数是检测这个身份证号码的数字位数,然后通过IF判断函数对15位或18位的情况做出不同处理。
CONCATENATE函数将提取出的分别代表“年”、“月”、“日”的信息,以及分隔符“/”等内容,合并成一个文本字符串。
MID函数的做用是从身份证的某个位置开始,取出若干字符。
比如MID(A2,7,2),就是将身份证号码(放在A2单元格中),从左边数第七个字符开始,取出2个字符。
2、性别=IF(LEN(A2)=15,IF(VALUE(RIGHT(A2,3))/2=INT(VALUE(RIGHT(A2,3))/2),"女","男"),IF(VALUE(MID(A2,15,3))/2=INT(VALUE(MID(A2,15,3))/2),"女","男"))由于代表性别的数字中,偶数为“女”,奇数为“男”,所以在这个公式中用VALUE函数将取出的字符串变成数字,再除以2,看是否能整除,因为奇数是不能整除的。
这里的INT函数是“取整”的意思,把上面除2后的结果用它取整,让IF函数判断一下,取整前后的结果是否相同,相同为偶数,不相同为奇数。
公式中的RIGHT函数是从字串的右边(即字符串最后面)取若干符,注意与MID 函数的区别。
3、年龄=CONCATENATE(DATEDIF(B2,TODAY(),"y"),"年",DATEDIF(B2,TODAY(),"ym"),"个月")由于我们用上面的公式,将“出生日期”提取到B2单元格,所以这里的公式是通过操作B2单元格的数据来得到年龄的。
函数DATEDIF是计算两个日期或时间的差值,通过第三个参数来确定所要的结果,比如“y”会得到两个日期相差的年;“ym”会得到除年外,所余的月数。
TODAY()函数的作用是得到当前系统日期,即电脑上今天的日期。
最终结果见下图:如果从网上找到前6位数所代表的地区代码列表,然后用VLOOKUP函数引用,就可以自动得到各员工的居住城市信息了。
公式写:="距离生日还有"&IF(DATE(YEAR(TODAY()),MID(H2,11,2),MID(H2,13,2))-TODAY()>0,DATE(YEA R(TODAY()),MID(H2,11,2),MID(H2,13,2))-TODAY(),DATE(YEAR(TODAY())+1,MI D(H2,11,2),MID(H2,13,2))-TODAY())&"天"变色就要用条件格式了,条件格式的公式写成:=--MID(H2,11,2)=month(today())总???分:=SUM(C3:C45)平均分:=AVERAGE(C3:C45)及格率:=COUNTIF(C3:C45,">=60")/COUNT(C3:C45)*100优秀率:=COUNTIF(C3:C45,">=85")/COUNT(C3:C45)*100及格人数:=COUNTIF(C3:C45,">=60")优秀人数:=COUNTIF(C3:C45,">=85")成绩排名:=RANK(D3,$D$3:$D$45,0)参考人数=COUNTA(A3:A45):男生人数:=CONCATENATE(COUNTIF($B$3:$B$45,"男"),"人")或=COUNTIF(B$3:B$45,"男")女生人数:=CONCATENATE(COUNTIF($B$3:$B$45,"女"),"人")或=COUNTIF(B$3:B$45,"女")自动平均分:=AVERAGEA(C3:C45)最高分:=MAX(C3:C45)最低分:=MIN(C3:C45)优秀人数:=COUNTIF(C3:C45,">=85")100分:=COUNTIF(C3:C45,"=100")90~100(不含100):=COUNTIF(C3:C45,">=90")-COUNTIF(C3:C45,">=100") 80~90(不含90):=COUNTIF(C3:C45,">=80")-COUNTIF(C3:C45,">=90")70~80(不含80):=COUNTIF(C3:C45,">=70")-COUNTIF(C3:C45,">=80")60~70(不含70):=COUNTIF(C3:C45,">=60")-COUNTIF(C3:C45,">=70")50~60(不含60):=COUNTIF(C3:C45,">=50")-COUNTIF(C3:C45,">=60")40~50(不含50):=COUNTIF(C3:C45,">=40")-COUNTIF(C3:C45,">=50")40以下(不含40):=COUNTIF(C3:C45,"<40")说明:C列为学生成绩,D列为学生成绩排名,60分及60分以上为及格,85分及85分以上为优秀,学生人数为43人,“成绩排名:=RANK(D5,$D$5:$D$45,0)”的0为降序排列,若是1则为升序排列。