从身份证号提取出生日期、周岁、性别

发布时间：2026-09-26

7 阅读

0 评论

文章标签： Excel Office

先看这一条，否则后面全白做。
Excel 的数值精度只有 15 位有效数字，身份证是 18 位。如果你把身份证号当「数字」存进单元格，它会变成 1.10101E+17，后三位直接被抹成 0 —— 而且不可逆，救不回来。
正确做法：先把要放身份证的那一列设为「文本」格式（选中列 → 右键 → 设置单元格格式 → 文本），再粘贴。或者手动输入时前面加一个英文单引号 '110101199003074514。

一、先拆结构：18 位号码里都装了什么

所有提取公式的本质都是一句话：身份证号是「定长编码」，第几位到第几位是固定的，所以可以用 MID 按位置切片。不用判断内容，只认位置。

1 – 6
地址码
7 – 14
出生日期 YYYYMMDD
15 – 17
顺序码
18
校验码

两个关键约定：

第 7–14 位就是生日，8 位定长，年年月月日日依次排开。
第 17 位（顺序码的最后一位）是性别位：奇数 = 男，偶数 = 女。这是国家标准定的分配规则，不是巧合。

二、提取出生年月日

做法 A：得到一个「真日期」（推荐）

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

拆开看：MID(A2,7,4) 从第 7 位起取 4 位 = 年；MID(A2,11,2) = 月；MID(A2,13,2) = 日。DATE(年,月,日) 把三个数拼成一个真正的日期序列值。

机制：MID 返回的其实是文本「1990」，不是数字 1990。但 DATE 的三个参数位置要求数值，Excel 会在这里做一次隐式类型转换——文本数字参与算术运算时自动变数值。所以不用你手写 VALUE()，它自己转了。这也是为什么公式能这么短。

公式输完如果显示成 1990/3/7 或 36561 这类数字，不是公式错了，是单元格格式的问题：右键 → 设置单元格格式 → 日期，选你想要的样式。

做法 B：只要一个「长成日期样的文字」

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

结果是文本 1990-03-07。它不能参与日期计算（不能减、不能比大小），只适合直接展示或拼接。要真日期就用做法 A。

想两全其美？加两个负号把文本强转回数值：

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

-（负号）是算术运算符，逼 Excel 把文本转成数字；两个负号负负得正，值不变，类型变了。再把单元格设为日期格式即可。这是 Excel 里很通用的一个 coercion 小技巧。

三、判断男女

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

机制：MID(A2,17,1) 取出第 17 位，MOD(…,2) 取它除以 2 的余数。奇数余 1 → 男，偶数余 0 → 女。

更短的写法，结果一样：

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

原理是 Excel 里 0 等价于 FALSE、非 0 等价于 TRUE，所以 MOD 的余数可以直接当判断条件用。新手建议先写完整的 =1 版本，可读性更好。

也可以反过来用 ISEVEN：偶数为女，=IF(ISEVEN(MID(A2,17,1)),"女","男")。MOD 的优势是 Excel / WPS / 老版本通吃。

四、算周岁

做法 A：DATEDIF（最短）

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

B2 是上一步算出的出生日期。第三个参数 "Y" 表示「返回两者之间完整的整年数」——注意是「完整」，没到生日就不算，这正是周岁（不是虚岁）的定义。

关于 DATEDIF：它是 Excel 从 Lotus 1-2-3 时代继承下来的隐藏函数，在函数列表和自动提示里找不到，但一直能用，WPS 也支持。它的三个参数必须是「开始日期 ≤ 结束日期」，否则返回 #NUM!。

做法 B：不依赖 DATEDIF 的稳妥写法

=YEAR(TODAY())-YEAR(B2)-IF(TEXT(TODAY(),"MMDD")<TEXT(B2,"MMDD"),1,0)

这是把「周岁」这件事手动算了一遍，逻辑最清楚：

YEAR(TODAY())-YEAR(B2)：年份直接相减，得到一个「虚岁」候选值。
把今天和生日都转成 4 位「月日」文本（如 0926、0307）比大小。
如果今年的生日还没到（今天的月日 < 生日的月日），就减 1。

为什么用 TEXT(...,"MMDD") 比较，而不是拼 DATE(年份,月,日)？因为 2 月 29 日出生的人，DATE(2025,2,29) 这种非闰年组合会被 Excel 悄悄进位成 3 月 1 日，边界判断就会偏。转成文本比字符串就没有这个问题。

别这么写：=YEAR(TODAY())-YEAR(B2)。这是最常见的错误，它在生日没到时会多算一岁。

五、15 位老身份证怎么办

1999 年以前发的身份证是 15 位，部分地区还有人在用。它的结构不同：出生日期是第 7–12 位、年份只有 2 位（省略了 19），性别位在第 15 位。用 LEN 分支就能一套公式兼容两种：

=IF(LEN(A2)=18,
    DATE(MID(A2,7,4), MID(A2,11,2), MID(A2,13,2)),
    DATE("19"&MID(A2,7,2), MID(A2,9,2), MID(A2,11,2)))

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

"19"&MID(...) 里的 & 是文本连接符，把「19」拼回两位年份前面。15 位身份证不存在 1900 年之前的人，所以补 19 是安全的。

六、一栏式总表：四列公式一次配齐

假设 A 列放身份证号（文本格式），从第 2 行开始。在 B、C、D 列分别填下面的公式，然后向下填充：

列	内容	公式	单元格格式
A	身份证号	（原样录入，务必为文本）	文本
B	出生日期	=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))	日期
C	性别	=IF(MOD(MID(A2,17,1),2)=1,"男","女")	常规
D	周岁	=DATEDIF(B2,TODAY(),"Y")	常规

想省掉 B 列这个中间步骤，周岁也可以一步到位（公式会长一些）：

=DATEDIF(DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)), TODAY(), "Y")

两个虚构示例（用于验证公式是否写对）：

身份证号（虚构）	出生日期	性别	周岁算法
110101199003074514	1990-03-07	男（第17位=1）	已过今年生日 → 年份差
110101199205153122	1992-05-15	女（第17位=2）	已过今年生日 → 年份差

七、进阶：顺手验一下身份证是不是真的

第 18 位是校验位，由前 17 位按固定权重算出来。下面这条公式直接算出「正确的第 18 位应该是什么」，跟实际值一比就知道真伪：

=MID("10X98765432",
     MOD(SUMPRODUCT(MID(A2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}), 11)+1,
     1)

=IF(上式的单元格=RIGHT(A2), "有效", "无效")

机制：前 17 位每位乘一个固定权重（7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2，这是 GB 11643 标准规定的），求和后除以 11 取余数，余数再去查表 "10X98765432"（余 0 对应第 1 个字符「1」，余 2 对应「X」）。ROW($1:$17) 生成 1–17 的数组，让 MID 一次取出 17 个字符。

注意：这只能验证号码的编码规则对不对，验证不了这个人是否真实存在——那是公安库的事。

八、常见报错排查

现象	原因与处理
号码显示 `1.10101E+17` 或末三位是 000	存成了数字，精度已永久丢失。无法修复，只能改格式为文本后重新录入。
出生日期算出 1905 年之类的怪值	同上，数字被破坏后 MID 切出来的位置全乱了。
`#VALUE!`	号码里有空格或不可见字符。外面套一层 `TRIM(CLEAN(A2))`，或把 A2 替换成它。
周岁总是多 1 岁	用了 `YEAR(TODAY())-YEAR(B2)`，没做「生日是否已到」的判断。改用第四节写法。
`#NUM!`	`DATEDIF` 的开始日期大于结束日期，通常是出生日期本身取错了。
日期显示成 5 位数（如 36561）	公式没问题，是单元格格式还是「常规」。改成日期格式即可。
性别判断全反了	取位错成了第 16 位或第 18 位。性别位固定是第 17 位。
15 位号结果全乱	没有做 `LEN` 分支，按 18 位的偏移去切了 15 位字符串。

九、如果你用 Microsoft 365：用 LET 写成一个不重复的公式

上面的公式里 A2 出现了好几次，改起来累。365 支持 LET，可以给中间结果起名字：

=LET(
  id,   A2,
  y,    MID(id,7,4),
  m,    MID(id,11,2),
  d,    MID(id,13,2),
  birth, DATE(y,m,d),
  age,  DATEDIF(birth, TODAY(), "Y"),
  sex,  IF(MOD(MID(id,17,1),2), "男", "女"),
  birth & " / " & sex & " / " & age & "岁"
)

LET 的工作方式：成对给出「名字, 值」，最后一行是返回值。它只在这个公式内部生效，不会污染工作表。好处是每个片段只写一次、只算一次，也更好读。