ARTICLE · 1304730

Excel秒提身份证号生日这招公式最实用

发布时间：2026-11-04 11:03:32 最近访问：2026-11-04 11:03:36

点击上方蓝字「亮雅轩快印」关注我们 →

2026-11-03 · 办公小常识

摘要：Excel提取身份证生日：一条MID公式批量搞定，兼容15位与18位，附算年龄、判性别、校验格式的公式和6个避坑点。

18 位身份证号的出生日期固定在第 7 位到第 14 位，一条公式就能批量提取。只要文本格式的日期，用 =TEXT(MID(A2,7,8),"0-00-00")；要能排序、能算年龄的真日期，用 =DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))。下面把公式、步骤和踩坑点一次给全，照抄就能用。

一、先搞清位数：18位里哪8位是生日

18 位身份证号的结构是固定的：前 6 位是地址码，第 7-14 位是出生年月日（YYYYMMDD），第 15-17 位是顺序码，第 18 位是校验码。所以提取的关键就一个数——从第 7 位开始，取 8 位。

有人会想到用「数据 - 分列 - 固定宽度」硬切，切一刀确实能把生日切出来，但一列里混着 15 位和 18 位时就全乱了，切完还要手工删前删后。公式的好处是一次写好、向下填充，整列自动出结果，源数据改动后结果跟着变，后续算年龄、做透视都能直接引用。

第 1-6 位：地址码；
第 7-14 位：出生日期，例如 19900512；
第 15-17 位：顺序码，第 17 位单数为男、双数为女；
第 18 位：校验码，可能是 0-9 或 X。

二、三种写法，按用途挑一种

写法1：只要显示成 1990-05-12（文本）

=TEXT(MID(A2,7,8),"0-00-00")

优点是最短，缺点是结果是文本，排序会按字符排，也不能直接算年龄。

写法2：要真日期（推荐）

=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))

分别取年 4 位、月 2 位、日 2 位交给 DATE 函数，得到的是真正的日期值，可以排序、可以算年龄、可以做透视。算完记得把这一列的单元格格式设成 yyyy-mm-dd，否则会显示成 33000 这样的序列号。

写法3：文本结果一键转日期

=--TEXT(MID(A2,7,8),"0-00-00")

前面两个减号是把文本强行转成数值，结果同样是日期序列号，改格式即可。三种写法算出来的东西一样，选一种统一用，别在同一张表里混着来。

三、15位老身份证也要兼容，一条IF搞定

15 位的身份证号没有世纪位，年份只有 2 位，生日在第 7-12 位，需要补上 19 再拼日期。两种位数混在一张表里时，用 LEN 判断长度自动分流：

=IF(LEN(A2)=18,DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)),IF(LEN(A2)=15,DATE(1900+MID(A2,7,2),MID(A2,9,2),MID(A2,11,2)),""))

长度既不是 18 也不是 15 的，直接返回空白，方便你事后筛选出来单独核对。注意 15 位号码只覆盖 1900-1999 年出生的人，遇到极少数早期证件要人工确认。

▲ 公式录入 · 从第7位起取8位拼成日期

四、顺手把年龄和性别一起算出来

生日列算好后（假设在 B2），年龄用 DATEDIF 最稳，它按整年计算，不会出现虚岁偏差：

=DATEDIF(B2,TODAY(),"Y")

想要「35岁6个月」这种精度，用两个 DATEDIF 拼一下：

=DATEDIF(B2,TODAY(),"Y")&"岁"&DATEDIF(B2,TODAY(),"YM")&"个月"

性别看第 17 位，单数为男、双数为女，18 位用 17 位、15 位用 15 位：

=IF(MOD(MID(A2,17,1),2)=1,"男","女")

两种位数混排时用这个兼容版：

=IF(LEN(A2)=18,IF(MOD(MID(A2,17,1),2)=1,"男","女"),IF(MOD(MID(A2,15,1),2)=1,"男","女"))

再补一条格式自检公式，用来筛出录入错误的号码——长度对且生日能转成合法日期才算正常：

=IF(AND(LEN(A2)=18,ISNUMBER(--TEXT(MID(A2,7,8),"0-00-00"))),"格式正常","待核对")

这条公式能挡住 19901301 这种不存在的日期，也能挡住少一位、多一位的号码，比人眼扫一遍靠谱。

五、照抄的操作步骤（5步）

新建一列放身份证号，先把这列格式设成「文本」，再粘贴号码；
在旁边的空白列第 2 行输入上面的公式，回车；
选中这个单元格，双击右下角的小方块向下填充到最后一行；
选中整列，按 Ctrl+1 把格式设为 yyyy-mm-dd；
用筛选找空白和 #VALUE!，这些就是要人工核对的号码。

▲ 批量填充 · 双击填充柄一次算完整列

表里有几千行时，双击填充柄容易卡顿，换个更快的做法：先在编辑栏输入公式，按 Ctrl+Shift+↓ 选中整列数据区域，再按 Ctrl+Enter，公式一次性写入全部选中单元格，引用会自动按行调整。写完记得抽查首行、中间行、末行三条，确认取位没有整体偏移。

六、六个必踩的坑

起始位写成 6：第 7 位才是年份的第一位，写成 6 会整体错一位；
没设文本就粘贴：18 位数字会被转成科学计数法，末尾三位直接变成 000，源数据就废了，粘贴前一定要先把列设成文本；
看到 33000 以为出错：那是日期序列号，改单元格格式就行；
结果整列是 #VALUE!：多半是号码里带了空格或不可见字符，先套一层 TRIM 或 CLEAN 再取位；
复制公式后不相对引用：把 A2 写成了 $A$2，整列算出来都是同一个人的生日；
日期自动进位：遇到 0230 这类无效日期，DATE 会顺延到下个月，所以要用格式自检公式兜一层。

顺带一个实用技巧：想按月份统计员工生日，加一列 =TEXT(B2,"mm") 取出月份，再做透视表就能看出哪个月生日最集中，团建、蛋糕、贺卡的采购量心里就有数了。

▲ 月份统计 · 取月后做透视，生日分布一目了然

常见问题

问：为什么提取出来的生日是 33000 这样的数字？

答：那是 Excel 的日期序列号，不是错误。选中整列按 Ctrl+1，把格式改成 yyyy-mm-dd 就正常显示了。

问：公式输入后显示 #VALUE! 怎么办？

答：先查号码里有没有空格或不可见字符，用 =TRIM(CLEAN(A2)) 清洗后再取位；再确认号码是文本格式，不是被科学计数法截断过的。

问：15 位和 18 位混在一列能一起算吗？

答：能。用 IF(LEN(A2)=18,...,IF(LEN(A2)=15,...,"")) 判断长度分流，15 位按第 7-12 位取，年份补 19 即可。

这套公式不挑版本，Excel 2010 以后和 WPS 都能直接用，做人事台账、客户名单、员工档案都省一遍手工输入。

💡 我们能提供的服务

亮雅轩快印可提供办公文印配套：表格排版与打印、员工档案与人事台账的规范装订、证照与合同的高清扫描归档，以及大批量资料的分类整理，具体以门店排单为准。

📞 服务热线：010-63164001

📍 亮雅轩全国8家门店

北纬路店24h西城 📍 北京市西城区北纬路81号 📞 010-63164001	阜成门店24h西城 📍 北京市西城区阜成门外大街3号东润时代大厦2层 📞 010-69927288 / 010-68357288
百子湾店朝阳 📍 北京市朝阳区百子湾南二路32号苹果社区北区3号楼B座38号-2层 📞 010-58760868 / 13910694286	朝阳路店朝阳 📍 北京市朝阳区朝阳路甘露园南里二区6号楼1层6-2号商铺 📞 010-65008059 / 13522987337
昌平未来城店昌平 📍 北京市昌平区七北路7号 📞 13521072915 / 010-68690789	设计中心西城 📍 北京市西城区阜成门外大街甲9号 📞 13693101413 / 010-65136697
SM文件资料制作中心24h西城 📍 北京市西城区阜成门外大街甲9号 📞 13552386637	西双版纳店版纳 📍 云南省西双版纳傣族自治州景洪市勐泐大道59号 📞 0691-2133198 / 15393827277