EXCEL能从身份证号码中筛选出出生日期吗?如何将身份证号码中的年月日转化为日期?
本文目录:
材料/工具:Excel2010
1、打开事先准备好数据工作表Excel2010表格文件。如图所示
2、选定需要提取出生年月日的单元格,在编辑栏输入公式。示例图中,选定C2单元格,栏输入以下公式:=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))
3、向下拖动填充序列,即可实现从多个身份证号码提取身份证号码。
4、提取效果如图所示。
怎样将身份证号码中的年月日提取出来并转化为日期?
身份证号可以利用函数提取很多信息,如员工的出生日期、性别、现龄、号码位数等。利用身份证号提取年月日并转换成自己想要的日期格式。
步骤:
一、了解MID:
1、MID的作用:在选中的文本里指定的位置开始,得到自己指定的字符串。
2、MID的使用方法:第一栏用鼠标选中或手写输入我们所要提取函数的位置;第二栏输入该数字开始提取的位置在第几位;第三栏输入我们需要提取几个数字。函数是否引用成功,可以看箭头的位置。如图:
3、MID的使用结果,如图:
二、了解TEXT:
1、TEXT:将数值转换为按指定格式数字格式表示的文本。
2、TEXT的使用方法:它需要输入两栏内容,第一栏输入需要转化的文本内容,第二栏输入需要转化的文本格式。下图有各种文本格式的对照图。
三、日期文本格式:
日期文本格式,如图:
excel怎么从身份证中提取生日,只要月日。
=MID(A1,11,4)
或者:=MID(A77,11,2)&"月"&MID(A77,13,2)&"日"
如何从excel 中的身份证号码提取出生年月日,将格式显示为YYYY/MM/DD
=MID(A1,7,4)&"/"&MID(A1,11,2)&"/"&MID(A1,13,2)
将上面的A1换成身份证号码所在单元格地址
这是针对18位身份证号
如果是15位身份证号,基本方法相同,但年份只有两位数,可在公式前面加上 "19"& 或"20"&,要自动判断加哪一个,可用if()函数。
=IF(MID(A1,7,2)<50,"20"&MID(A1,7,2)&"/"&MID(A1,9,2)&"/"&MID(A1,11,2),"19"&MID(A1,7,2)&"/"&MID(A1,9,2)&"/"&MID(A1,11,2))
以上的判断以50为界,即小于50的算作20XX年,大于等于50的算作19XX,显然如果处理2050后或者1949年前出生的人会出错,但这没有办法,这也是新身份证用四位的原因。
考虑同时处理两种身份证号码,则可写成
=if(len(A1)=15,IF(MID(A1,7,2)<50,"20"&MID(A1,7,2)&"/"&MID(A1,9,2)&"/"&MID(A1,11,2),"19"&MID(A1,7,2)&"/"&MID(A1,9,2)&"/"&MID(A1,11,2)),if(len(a1)=18,MID(A1,7,4)&"/"&MID(A1,11,2)&"/"&MID(A1,13,2),"号码长度不对"))
注意这种方式得到的日期Excel不能识别为日期型数据!
要让Excel当日期,应用Date()函数,用 "/"& 这样的文本连接的方式只能是产生表面的日期格式的形式。
=IF(LEN(A9)=15,IF(MID(A9,7,2)<50,DATE("20"&MID(A9,7,2),MID(A9,9,2),MID(A9,11,2)),DATE("19"&MID(A9,7,2),MID(A9,9,2),MID(A9,11,2))),IF(LEN(A9)=18,DATE(MID(A9,7,4),MID(A9,11,2),MID(A9,13,2)),"号码长度不对"))
这种方式的显示格式是YYYY-M-D,要得到YYYY/MM/DD的效果,需设置数字格式(自定义)为yyyy/mm/dd