文/华杰科技工作室


操作前必看：先备份你的原始数据！ 选中身份证号那列 → 右键复制 → 粘贴到另一个Sheet存档。公式操作涉及新增列，不影响原数据，但备份是铁律，养成习惯。


你们公司的HR，现在还在对着花名册一个一个手敲出生日期吗？

我见过太多这种场景了——表格里密密麻麻200个身份证号，旁边出生日期列空着，HR小姐姐戴着眼镜，一个数字一个数字往里对，对到眼睛酸，对到下班还没对完。

说真的，这活不是勤快，是在用时间换一个公式能解决的问题。

今天就一件事：一个公式，秒提出生日期，200行数据1分钟搞完。


⚠️ 开始前的关键前提（跳过必错）

身份证号这列，必须是文本格式，不能是数字格式。

为什么？Excel默认把超过15位的数字后三位变成0。18位身份证号用数字格式存，后三位全变0，公式提取出来的出生日期就是错的，而且你肉眼根本看不出来。

检查方法：选中身份证号列 → 看左上角格式框，显示"文本"才对。如果显示"常规"或"数字"，赶紧右键设置单元格格式 → 文本 → 重新录入（已有的数字格式身份证号必须重新手工输入，格式改了不会追溯）。



第1招：DATE+MID公式，一步提取标准日期

这是今天的核心公式，记住这一个就够了。

假设E列是身份证号，D2开始写公式：

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

逐段拆解，看一遍就懂：


MID(A2,7,4)：从第7位开始，取4位 → 年份（如1990）

MID(A2,11,2)：从第11位开始，取2位 → 月份（如08）

MID(A2,13,2)：从第13位开始，取2位 → 日期（如15）

DATE(年,月,日)：把三段数字合成Excel认识的真正日期值



公式输完后，结果显示的是一串数字怎么办？

别慌，那是日期序列号。选中D列 → 右键"设置单元格格式" → 日期 → 选"2012-03-14"样式 → 确定，立刻变成正常日期。


批量填充到整列：

点击B2单元格 → 鼠标移到右下角，出现小黑十字 → 双击，自动填充到最后一行。

注意：若A列中间有空白行，双击填充柄会在空白处中断。这种情况改用 Ctrl+D 批量填充：先选中B2到B列最后一行整列区域 → 按 Ctrl+D，直接填满，空白行不影响。


兼容性：Office 2010及以上 / WPS全版本，Windows和Mac均支持。



第2招：提取出来的日期，直接用来干3件高频的事

出生日期提完，接下来才是真正省事的地方。

场景一：员工生日提醒（本月生日自动标记）

E4列写公式：

=IF(MONTH(D4)=MONTH(TODAY()),"本月生日","")

本月过生日的员工，E列自动出现"本月生日"，空的就是其他月份。

想要颜色标记更直观？用条件格式：

选中D列 → 条件格式 → 新建规则 → 使用公式 → 输入：

=MONTH($D4)=MONTH(TODAY())

设置填充色为橙色或黄色 → 确定。本月过生日的行自动变色，HR再也不用手动划重点。



兼容性：Office 2013及以上 / WPS全版本，Windows和Mac均支持。


场景二：年龄核查（精确到整年，不受闰年影响）

G4列写公式：

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

DATEDIF 是Excel专门算时间间隔的函数，"Y"代表完整年数。这个公式比 TODAY()-出生日期/365 准得多——后者遇到闰年会差一天，做报表时出现小数，看着就很不专业。



兼容性：Office 2007及以上 / WPS全版本，Windows和Mac均支持。注意：DATEDIF在Mac版Excel部分旧版本中不显示函数提示，但公式仍然有效，直接输入即可。


场景三：按出生年代筛选（80后/90后分组）

数据 → 筛选 → 点击出生日期列下拉箭头 → 日期筛选 → 介于 → 输入起止日期范围。

例如筛选90后：起始日期填 1990/1/1，终止日期填 1999/12/31 → 确定，所有90后员工一秒筛出来。

如果要更精准分组，也可以用辅助列：=YEAR(B2) 提取年份数字，再用IF判断区间。



第3招：新手必看的2个避坑点

坑1：公式结果是"#VALUE!"错误

99%的原因是身份证号格式不对。回去看文章开头的前置条件，确认那列是文本格式，不是数字格式。

坑2：日期提取出来不对，年月日乱序

Excel日期显示格式受系统地区设置影响。Mac用户偶尔会遇到月日顺序颠倒的情况。解决方法：选中日期列 → 格式 → 自定义 → 输入 yyyy-mm-dd → 确定，强制统一格式。

用Excel做HR工作这几年，我发现一件事：

不是不会，是没人告诉你那个正确的公式。

一旦知道了，就再也回不去手动敲日期的日子了——不是懒，是真的没有任何理由再那么做。


领资料

学会了，转发给HR或行政的同事，说不定帮了大忙。

关注「华杰科技工作室」，后台回复【资料】，领取《6个基础模板》+《Excel快捷键大全速查卡》**，里面整理了今天讲的快捷键和常用公式。需要的朋友，关注「华杰科技工作室」
长期有效，随时可领。


你们公司的HR还在手动敲日期吗？评论区说一下，帮我做个统计。