1、EXCEL中从身份证号码提取出生日期的方法由于工作需要，我需要单位人员的出生年月，单独一个一个输入？ NO太麻烦了。我的工资程序里有人员信息，而且有每个人的身份证号码，所以我就想出了利用 excel 来完成这个工作。PS，我是用 office2003 的 excel 完成这个操作的，office2000 可能有些操作有些不同，不妨试一下。首先先要说几个公式，明白了这几个公式后，就能简单完成了。函数【left 】作用：提取数据左边 n 个数字的内容默认公式：=left(text, num_chars)翻译公式：=left(数据,n数字)举例：=left(“Hello“,2) 输出结果为最开始的
2、2 个字母“He”函数【right 】作用：提取数据右边 n 个数字的内容默认公式：=right(text, num_chars)翻译公式：=right(数据,n数字)举例：=right(“Hello“,2) 输出结果为最末尾的 2 个字母“lo”好了，利用这两个公式，就可以做到简单的提取一个身份证号码中的日期了。举例，某人身份证为 310123190102039527（18 位），输入到“A1“单元格，建议用把单元格改成“文本“，不然会显示为科学计数法。【单元格改成文本的方法：鼠标右击“A1“单元格，选择“设置单元格格式(F).”，在“单元格格式”对话框中，选择“数字”标签，“ 分类(C)”
3、里选择“文本”，点“确定”】那用公式套用的话，先用 left 吧，最后 4 位是不要的，所以提取左边 14 位。这个身份证号码已经输入到“A1“ 单元格，那么在 “B1“单元格可以这样输入：=left(A1,14)，那出现的结果就是“31012319010203”，然后你可以用 right 命令提取“B2“单元格里后 8 位数字，就可以提取出生日代码了。比如我们在“C1“单元格里输入=right(B1,8)，出现的结果就是“19010203”，基本工程完成了。接下来是进阶教程，其实，完全可以把 2 个命令合并使用，比如，我们在 “D1“单元格里这样输入：=right(left(A1,14),8
4、)，看看结果如何？是不是直接出现了正确结果？其实，如果 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) 试试，就算出了正确的结果
5、“19010203”但是，又出现问题了，这样的日期，一个 8 位的数字，其实不是 excel 正式的日期格式，正式的日期格式应该为“1901-02-03”，那如何操作呢？我的思路是这样的，分别提取出“年”、“月”、“日” ，然后利用公式把年月日连接起来，就成为了正式的日期格式，接下来要引入第四个公式【date】函数【date】作用：提取“年”、“ 月”、“日” ，使其转化成日期格式默认公式：=date(year, month, day)翻译公式：=date(x年,y月,z日)举例：=date(1901,02,03) 输出结果为日期格式的“1901-2-3”接下来，按照如下操作提取出年月日：提取
6、年，在“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
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 年
8、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 位的，有什么办法只用一个公式搞定它？答案是有的。这里又要引入一个判别函
9、数【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 是错误“) 输
10、出结果“0 是错误” 【0 直接判别为假（其他数字例如1、2、3 的结果都为真），所以输出结果 2】说个题外话 if 语句比较经典，我多举了几个例子，我经常用它来核对，比如有 2 列数字或者姓名，我要确保它们的位置一一对应，我就可以用 if 来判断，比如这两列分别是 A 列和 B 列，我在C1 单元格输入=if(A1=B1,“,“X“)然后选中 C1 单元格，鼠标按住这个单元格边框右下角的小黑方块往下拉（或者双击）可以直接判断出 A 列和 B 列的数据是否一样。这里 if 怎么用呢？对了，就是先在判别式里判断出身份证的长度，对了，还要说判断长度的公式【len】函数【len】作用：输出结果为字符
11、长度默认公式：=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
12、单元格的“ 单元格格式”不对，鼠标右击“B2“单元格，选择“数字”标签，“分类 (C)”中的“日期”，确定即可，结果就对了。【当然，如果你喜欢，你可以选择比如“XXXX 年 XX 月 XX 日”的格式，也可以只显示年月等】接下来就按住“B2“单元格右下角的小黑方块往下拉（双击小方块效果更好），结果就都出来了。这里再展开一下，为什么一开始“B2“单元格输出结果为 9748 呢？怎么说呢？我只能用一种自己理解的非官方回答来说明，其实你们看到单元格的日期是个假象，其实真正背后的内容是一个数字。不妨可以做个实验，你随便找个单元格，输入数字“1”，然后修改“单元格格式”，改成“日期”格式，看到结果是什么
13、？“1900-1-1” ，就是说数字 “1”对应的日期是“1900-1-1”。然后，再输入一个日期“9999-12-31”，这个日期是现有电脑能判断出最“将来”的日期，然后修改“单元格格式”，改成“常规”，结果就是“2958465”，这就是日期格式的最大值和最小值。而且经过我的测试，存在小数点的数字也是可以转换成日期的，小数点后的内容就是时间。例如“123.456”，转化成日期格式【 注：这里的日期格式要带时间的】后输出结果为“1900-5-2 10:56:38 AM”，所以说，日期是可以计算的，例如：“A1“单元格输入你的出生日期，“A2“单元格输入今天的日期，然后在“A3“单元格输入： =
14、A2-A1，然后看到的结果为日期格式，别着急，把 “A3“单元格的格式改成“常规”，对了，这就是你出生以来活了多少天。当然，你可以在“A4“单元格输入：=A3/365，就可以算出自己几岁了（实足年龄）。另外，如果你输入“0”，然后转化成日期格式，是不成立的，“1900-1-0”这个数字自动左对齐，也就是说这个数据已经成为了文本格式，默认日期格式是右对齐的。当然，你输入类似“1856-5-9”之类的 1900 之前的日期格式，是死活不会转变成日期格式的，只会默认成为文本格式，自动左对齐。如果输入“-1” 再转换成日期格式就更离谱了，输出结果直接是“#”。当然，超过最大值的“2958465”后一个数字 “2958466”转化成日期格式，也是输出“#”，不存在。所以说，以前 1999 年面临最大的问题是“千年虫”问题，可能到了 2000 年，日期显示为 1900年。不过现在都已经解决了这个问题。但是，在比较远的未来，将会遇到“万年虫”问题，当然，这离我们很遥远，那时候地球在不在还是个问题，不过我感觉，这个“万年虫”问题可能比之前的“千年虫”问题更严重，也许，那时候我们的身份证号码要成为了 19 位也说不定。
