如何用EXCEL自动提取身份证号中的出生日期和年龄?
身份证号以一敌四第二步:
如何在身份证号中提取出出生日期?
在18位的身份证号中,第7位开始到第14位,这8位数字代表了这个人的出生日期。在这里,可以用MID和TEXT两个函数来制作公式。
MID函数详解:
一、作用:此函数为文本函数,用于在文本字符串中,从指定起始位置起返回指定长度的字符。
二、共分为三个参数:
参数一Text:准备从中提取字符串的文本字符串,即从哪里取字符。在本例中,选择I2单元格,即身份证号所在的位置。
参数二Start_num:准备提取的第一个字符的位置,Text中,第一个字符为1,以此类推。即从第几位开始取字符。由于,身份证号中出生日期从第7位开始,此处需要录入数字“7”。
参数三Num_chars:指定所要提取的字符串长度。在本例中,需要取8位,所以此处需要录入数字“8”。
图3-17
当所有参数录入完成后,在函数参数录入页面可以看到此公式的计算结果,同时在函数对话框中可以看到完整的函数公式=MID(I2,7,8)(如图 317所示),确定无误后,最后点击“确定”按钮。
此时,取出的仅是一串文本型数字,并不是真正的日期格式,因此还需要用TEXT函数进行进一步的处理。
TEXT函数详解:
一、作用:此函数为文本函数,用于根据指定的数值格式将数字转成文本。
二、共分为二个参数:
参数一:Value,数值或能够返回数值的公式,或者对数值单元格的引用。在本例中,需要设置的是在身份证中取出的代表出生日期的8位数字,而取出的方式为公式,因此可以直接将=MID(I2,7,8)公式录入到此参数中。
参数二:Format_text,指定的数字格式,在本例中需要将这8位数字转换成日期格式,因此此参数设置为"0000-00-00"。
当所有参数录入完成后,在函数参数录入页面可以看到此公式的计算结果,同时在函数对话框中可以看到完整的函数公式=TEXT(MID(I2,7,8),"0000-00-00")。
最后,点击“确定”按钮。
图3-18
此时,原来的一串文本型数字就变成了日期格式。(如图 318所示)
知识扩充:如何将文本型日期转换为数值型日期?
通常,大家从身份证号中提取出生日期的工作,到此就告一段落了。但值得一提的是,此时的日期格式为文本型日期,而非数值型日期,如果希望将此日期转化为数值型日期,则需要再使用DATEVALUE函数转换一下。
DATEVALUE函数详解:
一、作用:此函数为时间与日期函数,主要用于将日期从字符串转化为序列数,即数值型日期格式。
二、参数Date_text:要转换的文本型日期。在本例中,只需要将前面制作的公式录入到此参数中即可。
此时完整的公式为:=DATEVALUE(TEXT(MID(I2,7,8),"0000-00-00"))(如图 319所示)
图3-19
最终效果如图 320所示:
图3-20
小琪听完了顾城的讲解,跟顾城说道,“EXCEL真厉害,本来我还想着日期是最难录入的,那么多数字不说,中间还要加个斜杠,太麻烦了,有了这个身份证号的自动提取,那岂不是省了我的大麻烦?”
“呵呵,小琪,EXCEL能帮你做的事情可多了,你这才学到哪里啊?”
“嗯,倒也是,对了,顾城哥,你说身份证号可以以一敌四,接下来,你要给我讲什么呢?”
“说完了出生日期,咱们就可以根据出生日期计算出这个人的年龄啦!”
在这一步中,需要用到两个函数:TODAY函数和DATEDIF函数。
TODAY函数详解:
一、作用:返回当前的日期。
二、参数:此函数无需参数。完整公式为:=TODAY()(如图 325所示)
PS:使用此函数录入当前日期,会根据电脑系统日期的变化而自动变化,无需人工更改。
如果需要录入一个当前日期,且此日期必须固定,不能随系统日期的变化而变化,则可以使用快捷键Ctrl+;来实现快速录入。
图3-25
DATEDIF函数详解:
一、作用: DATEDIF函数可以计算两个日期之差,也就是两个日期之间的时间间隔,并且可以根据要求返回不同的时间间隔的计算方式。
二、参数:此函数非常特殊,为隐藏函数。即无法在函数对话框中找到,因此必须记住此函数的参数。
DATEDIF共三个参数:
=DATEDIF(开始日期,截止日期,格式代码)
在本例中,开始日期即为员工的出生日期,而截止日期则要录入当前日期,由于手动录入当前日期无法自动变化,因此需要使用TODAY函数来实现当前日期的录入。这样DATEDIF函数才可以自动计算出此员工当前的年龄。
三、DATEDIF格式代码:
Y:两个日期以“年”为单位的时间间隔。
M:两个日期以“月”为单位的时间间隔。
D:两个日期以“日”为单位的时间间隔。
MD:忽略“月”和“年”后,两个日期以“日”为单位的时间间隔。
YD:忽略“年”后,两个日期以“日”为单位的时间间隔。
YM:忽略“月”后,两个日期以“月”为单位的时间间隔。
在此例中,由于要计算年龄,因此选择的格式代码为“Y”。
最后形成的完整公式为:=DATEDIF(K2,TODAY(),"Y")(如图 326所示)
城哥说:由于DATEDIF函数中的格式代码属于文本,因此在录入格式代码时,必须加双引号!
公式制作完成后,最终效果如图 327所示:
图3-27
“小琪,你看,现在身份证号是不是以一敌三了?”
“嗯,顾城哥,这样只需要录入身份证号,这个员工的出生日期和年龄就全部都自动出现了,而且年龄也不用每年再进行调整了!”
“没错
“接下来嘛,就说一说性别,身份证号可以自动确定这个员工的性别,只要你身份证号核实准确了,就不用担心性别会出现录入错误的情况了,怎么样?”
”嗯,顾城哥,你快讲讲,这样就不用数据验证功能再去选择性别了!“
小伙伴们,EXCEL如何自动提取身份证号中的性别呢?咱们明天接着聊!
举报/反馈
用Excel提取身份证中的出生日期
在整理员工或者学生档案信息时,输入身份证号码后,往往还需要从身份证中提取身份证所属者的出生日期。今天,我们就用下面一组模拟的数据,一起学习一下从身份证中提取出生日期的方法。
方法一,使用函数提取。C2单元格输入以下公式,向下复制填充。
=–TEXT(MID(B2,7,8),”0-00-00″)
如果说你输入公式后,公式结果变成了31104,别忘了把单元格格式设置为日期格式。下面咱们就来看一下这个公式的意思。
首先看MID函数的帮助文件:
MID 返回文本字符串中从指定位置开始的特定数目的字符,放到咱们的这个公式里,
MID(B2,7,8)的意思换成咱们自己的理解就是,从B2单元格的第7位开始截取,截取的字符长度是8位,得到的计算结果就是”19850226″。
接下来用TEXT函数,将MID函数得到的字符串变成日期的模样,TEXT(MID(B2,7,8),”0-00-00″)将文本字符串”19850226″从右侧的第二位和第四位之间各加上一个短横线,结果变成”1985-02-26″。
经过乔装打扮,文本字符串”19850226″就有了日期的样子了,但是这样的结果仅仅是掩人耳目,还不是真正的日期格式。经过TEXT函数、MID函数、LEFT函数、RIGHT等函数计算后得出的数值是文本格式,一般情况下,不能参与后续的计算处理。
说到这里,还有一句题外话,就是咱们在日常的日期数据录入时,一定要养成一个良好的习惯,类似“2014.2.1”、“20140201”这样的日期形式千万要不得。
TEXT函数前加了两个短横线是什么意思呢?通常把这种形式叫做减负运算,也就是负负得正,通过运算,将文本格式的数值变成真正的日期格式,如果将两个短横线换成1*(1乘以),也可以实现将文本格式向数值格式的转换。
除了方法一的函数提取出生年月,还可以使用基础操作的方法来提取。
选择B列数据区域,单击【数据】【分列】,在弹出【文本分列向导 第1步,共3步】对话框中,选择“固定宽度”,单击下一步。
在【文本分列向导 第2步,共3步】对话框数据预览窗口中,单击第6位和第7位之间的刻度,再单击第14位和第15位之间的刻度(也就是留出出生日期的宽度),单击【下一步】。
在【文本分列向导 第3步,共3步】对话框中,依次点击最左侧列,点选【不导入此列(跳过)】。
点击最右侧列,点选【不导入此列(跳过)】。
点击中间列,点选【日期】。
目标区域选择C2,单击完成。
这样,所有的出生日期就马上可以提取出来了。