EXCEL中从身份证号码提取出生日期的方法

Excel表中身份证号码提取出生年月,性别,年龄的使用技巧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,3)),2)=0,男,女),身份证错))=IF(LEN(D3)=15,IF(MOD(VALUE(RIGHT(D3,3)),2)=0,女,男),IF(LEN(D3)=18,IF(MOD(VALUE(MID(D3,15,3)),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)),身份证错))以下答案不管B1单元格是15位还是18位身份证号码，通用年龄=DATEDIF(TEXT(MID(B1,7,LEN(B1)*2/3-4),00-00-00),Today(),Y)B1单元格日期到今天Today（）的年龄年龄=DATEDIF(TEXT(MID(B1,7,LEN(B1)*2/3-4),00-00-00),2014-08-31,Y)B1单元格日期到具体某日如2012-10-13的年龄=IF(LEN(D3)=15,IF(MOD(VALUE(RIGHT(D3,3)),2)=0,女,男),IF(LEN(D3)=18,IF(MOD(VALUE(MID(D3,15,3)),2)=0,女,男),身份证错))EXCEL中从身份证号码提取出生日期的方法首先先要说几个公式，明白了这几个公式后，就能简单完成了。①函数【left】作用：提取数据左边n个数字的内容默认公式：=left(text,[num_chars])翻译公式：=left(数据,n[数字])举例：=left(Hello,2)输出结果为最开始的2个字母“He”②函数【right】作用：提取数据右边n个数字的内容默认公式：=right(text,[num_chars])翻译公式：=right(数据,n[数字])举例：=right(Hello,2)输出结果为最末尾的2个字母“lo”好了，利用这两个公式，就可以做到简单的提取一个身份证号码中的日期了。举例，某人身份证为310123190102039527（18位），输入到A1单元格，建议用把单元格改成文本，不然会显示为科学计数法。【单元格改成文本的方法：鼠标右击A1单元格，选择“设置单元格格式(F)...”，在“单元格格式”对话框中，选择“数字”标签，“分类(C)”里选择“文本”，点“确定”】那用公式套用的话，先用left吧，最后4位是不要的，所以提取左边14位。这个身份证号码已经输入到A1单元格，那么在B1单元格可以这样输入：=left(A1,14)，那出现的结果就是“31012319010203”，然后你可以用right命令提取B2单元格里后8位数字，就可以提取出生日代码了。比如我们在C1单元格里输入=right(B1,8)，出现的结果就是“19010203”，基本工程完成了。接下来是进阶教程，其实，完全可以把2个命令合并使用，比如，我们在D1单元格里这样输入：=right(left(A1,14),8)，看看结果如何？是不是直接出现了正确结果？其实，如果left和right两个命令同时用到，可以用一个更好的命令【mid】来代替，接下来我来说一下mid的用法：③函数【mid】作用：提取数据，从左边起第n个数字开始，长度为m的内容默认公式：=mid(text,start_num,num_chars)翻译公式：=mid(数据,n[开始第n位],m[长度])举例：=mid(Hello,2,3)输出结果为从第二位开始，长度为3的字母“ell”那样，刚刚复杂的left和right嵌套，可以在E1单元格输入公式=mid(A1,7,8)试试，就算出了正确的结果“19010203”但是，又出现问题了，这样的日期，一个8位的数字，其实不是excel正式的日期格式，正式的日期格式应该为“1901-02-03”，那如何操作呢？我的思路是这样的，分别提取出“年”、“月”、“日”，然后利用公式把年月日连接起来，就成为了正式的日期格式，接下来要引入第四个公式【date】④函数【date】作用：提取“年”、“月”、“日”，使其转化成日期格式默认公式：=date(year,month,day)翻译公式：=date(x[年],y[月],z[日])举例：=date(1901,02,03)输出结果为日期格式的“1901-2-3”接下来，按照如下操作提取出年月日：提取年，在F1单元格输入：=mid(A1,7,4)，输出结果为“1901”。提取月，在G1单元格输入：=mid(A1,11,2)，输出结果为“02”。提取日，在H1单元格输入：=mid(A1,13,2)，输出结果为“03”。最后合并，在H1单元格输入：=date(F1,G1,H1)，看看输出结果如何？其实，这里也可以用date和mid的嵌套公式，=date(mid(A1,7,4),mid(A1,11,2),mid(A1,13,2))直接提取出日期。不过又出现一个问题，如果有些人的身份证号码是15位的呢？那么如何处理呢？其实一样的，15位身份证号码生日只有从第7位开始，6个数字，如果一口气写成嵌套公式，就是=date(mid(单元格,7,2),mid(单元格,9,2),mid(单元格,11,2)，比如我再举例一个身份证号码：310123010203952，把这个字符串输入A2单元格（记得先把A2单元格转换成文本格式），然后在B2单元格输入=date(mid(A2,7,2),mid(A2,9,2),mid(A2,11,2))。其实，date里面的数据是date(01,02,03)，公式会自动转换成1901-2-3，其实这里涉及到一个千年虫问题，其实现在已经是2010年，如果你把2001年2月3日简写成01年2月3日，由date命令就会转化成1901-2-3，以后在输入中一定要注意。不过有点可以放心，老的15位身份证不可能出现在2000年以后，也就是它2位数的年份正好直接转换成19XX，也不用多考虑，直接套用date公式。如果为了再严谨一些，确保日期为19XX年份的，可以在把公式改成=date(19&mid(A2,7,2),mid(A2,9,2),mid(A2,11,2)，这样就强制是19XX年了。其实这里大可不必这样做。现在问题又来了，如果我电脑里的数据，既有18位的，又有15位的，有什么办法只用一个公式搞定它？答案是有的。这里又要引入一个判别函数【if】⑤函数【if】作用：判别，如果成立，输出公式/结果1；如果不成立，输出公式/结果2默认公式：=if(logical_test,[value_if_ture],[value_if_false])翻译公式：=if(判别式,公式/结果1[判别式为真],公式/结果2[判别式结果为假])举例1：=if(1+2=3,答案正确,答案错误)输出结果“答案正确”【1+2=3，结果为真，所以输出结果1】举例2：=if(false,正确吗？,错误吗？)输出结果“错误吗？”【false直接判别为假，所以输出结果2】举例3：=if(0,1是正确,0是错误)输出结果“0是错误”【0直接判别为假（其他数字例如1、2、3的结果都为真），所以输出结果2】说个题外话if语句比较经典，我多举了几个例子，我经常用它来核对，比如有2列数字或者姓名，我要确保它们的位置一一对应，我就可以用if来判断，比如这两列分别是A列和B列，我在C1单元格输入=if(A1=B1,,X)然后选中C1单元格，鼠标按住这个单元格边框右下角的小黑方块往下拉（或者双击）可以直接判断出A列和B列的数据是否一样。这里if怎么用呢？对了，就是先在判别式里判断出身份证的长度，对了，还要说判断长度的公式【len】⑥函数【len】作用：输出结果为字符长度默认公式：=len(text)翻译公式：=len(数据)举例：=len(Hello)输出结果为“5”那开始用if语句来完成这个工作，比如我新建了一个表格，随便输入了如下4个身份证号码可以用LEN(A2)=18作为if语句的判别式，如果为真，输出结果1，即18位身份证的提取公式date(mid(A2,7,4),mid(A2,11,2),mid(A2,13,2))，那在结果2中输入15位的身份证判别式date(mid(A2,7,2),mid(A2,9,2),mid(A2,11,2))。写在B2单元格中，显示出的结果为“9748”。这是为什么勒？因为B2单元格的“单元格格式”不对，鼠标右击B2单元格，选择“数字”标签，“分类(C)”中的“日期”，确定即可，结果就对了。【当然，如果你喜欢，你可以选择比如“XXXX年XX月XX日”的格式，也可以只显示年月等】接下来就按住B2单元格右下角的小黑方块往下拉（双击小方块效果更好），结果就都出来了。这里再展开一下，为什么一开始B2单元格输出结果为9748呢？怎么说呢？我只能用一种自己理解的非官方回答来说明，其实你们看到单元格的日期是个假象，其实真正背后的内容是一个数字。不妨可以做个实验，你随便找个单元格，输入数字“1”，然后修改“单元格格式”，改成“日期”格式，看到结果是什么？“1900-1-1”，就是说数字“1”对应的日期是“1900-1-1”。然后，再输入一个日期“9999-12-31”，这个日期是现有电脑能判断出最“将来”的日期，然后修改“单元格格式