员工信息乱、到期提醒全靠人、同名员工搞混档案……HR做台账最怕这些事。本文教你用3个公式让Excel自动帮你盯着，再也不用担心漏掉一个人。


看完这篇，你能做出一份能自动防重录入、自动提醒证件到期的员工台账模板，拿来就能用。
小李是一家制造企业的HR，公司有120多名员工。
她的台账是这样的：手动逐行填，靠自己记着谁的证件快到期了，人员流动大的时候偶尔会录错或漏录——上月就发生了一件事：新来的"张伟"和原来在职的老"张伟"，信息录进了同一行，老张伟的入职时间被覆盖了。
等到月底财务对工资、核算社保的时候，才发现工龄算错了，年终奖比例出了问题。最后小李被领导点名批了，在全员大会上解释这件事——那种尴尬，我不多说了。
这不是不认真，是Excel没设防线。
台账出问题，往往不是人的问题，是工具没配好。

今天教你用3个核心公式，给台账装上"自动驾驶"：
COUNTIF + 数据验证：防重复录入
TODAY + IF：证件/合同到期自动提醒
DATEDIF：自动计算司龄/试用期 一次设好，永久生效。


第一招：COUNTIF + 数据验证防重复录入

场景：录新员工工号时，如果已有相同工号，Excel弹窗阻止录入并提示"重复！"
为什么需要这个：台账越大，人工核查越困难。同工号录两次，后果是档案混乱、工资出错、社保乱发。
操作步骤：
选中"工号"列数据区域B2:B1000（从第2行开始，不含标题行）
点击「数据」→「数据验证」
允许条件：选「自定义」
公式输入：=COUNTIF($B$14:B14,B14)<=1
切换到「出错警告」标签，设置：
样式：停止
标题：重复录入
内容：该工号已存在，请核查后再录入
确定公式解释：
$B$2:B2= 检查范围从数据第一行（B2）到当前输入行的上一行（随行号下移自动扩展）
B2= 当前正在输入的工号
<=1= 已有记录中如果超过1个相同工号，立即弹窗阻止

关键：从$B$2开始，不含标题行（B1），避免表头干扰
踩坑：工号/姓名列有合并单元格，COUNTIF判断会乱。建议这两列全部不使用合并单元格。

金句：与其事后排查重复数据，不如源头拦截，不让它进来。


第二招：IF + TODAY 证件到期自动提醒

场景：员工劳动合同、居住证、驾照等证件到期前30天，自动显示"即将到期"；到期后显示"已到期"
先从简化版开始（只判断有没有过期）：
=IF(B23="","",IF(B23<TODAY(),"⚠️已到期","✅有效期内"))
理解了再看进阶版（区分3种状态+显示剩余天数）：
=IF(B23="","",IF(B23<TODAY(),"⚠️已到期",IF(B23-TODAY()<=30,"📌即将到期（剩"&(B23-TODAY())&"天）","✅有效期内")))

这里 F2 是证件到期日期列。
操作步骤：
在"到期状态"列（比如 G2）输入进阶版公式
向下填充到 G1000
配合条件格式自动标色：
选中G列 → 「条件格式」→「新建规则」→「使用公式」
公式=$B23="⚠️已到期"→ 填充色选红色
公式=LEFT($B23,2)="📌"→ 填充色选橙色
公式逐层拆解：
第一层IF(F2="","")= F2为空就不显示，避免大片"已到期"刷屏
第二层IF(F2<TODAY(),"⚠️已到期"= 日期已过，红色报警
第三层IF(F2-TODAY()<=30= 30天内到期，橙色提醒，并显示剩余天数
最后"✅有效期内"= 不在以上情况，正常绿色

金句：Excel的 TODAY() 每天自动更新，你只需要把公式设好，它帮你天天盯着。

第三招：DATEDIF 自动计算司龄和试用期
场景：台账记录入职日期后，自动计算"当前司龄"（×年×月）和"是否已过试用期"
自动计算司龄
公式（放在"司龄"列，C2为入职日期）：

=IF(OR(B30="",B30>TODAY()),"",DATEDIF(B30,TODAY(),"Y")&"年"&DATEDIF(B30,TODAY(),"YM")&"月")

说明：第一层多加了 C2>TODAY() 的判断——如果录了未来入职日期（预入职），DATEDIF会报#NUM!错误，这个保护可以避免。
效果：自动显示"2年3月""0年8月"，每天打开文件自动更新，不用手动改。
自动判断是否过试用期
公式（放在"试用期状态"列，用EDATE精确计算整整3个月）：
=IF(OR(B30="",B30>TODAY()),"",IF(TODAY()>=EDATE(B30,3),"已转正","试用期内（剩"&MAX(0,DATEDIF(TODAY(),EDATE(B30,3),"d"))&"天）"))

为什么用EDATE而不是直接+90天：
+90天假设3个月=90天，但有的3个月是89天（含2月），有的是92天
EDATE(C2,3)精确计算3个整月后的日期，财务和法务角度都更严谨
WPS用户注意：DATEDIF在WPS中可直接使用，但不在函数下拉列表里，需手动输入 =DATEDIF(。


第四招：下拉菜单规范录入，数据永远统一

场景：部门、合同类型、在职状态如果让人自由填写，一定会出现"人力资源部"和"人资部"并存，导致后续筛选统计漏数据。
操作步骤：
新建一个"配置表"工作表
分列写好选项（举例）：
A2:A8：财务部、人资部、销售部、运营部、技术部、市场部、行政部
B2:B5：劳动合同-1年、劳动合同-2年、劳动合同-3年、劳务协议
C2:C5：在职、离职、停薪留职、外派
回到台账表，选中"部门"列D2:D1000
「数据」→「数据验证」→ 允许：序列 → 来源输入：=配置表!$A$2:$A$8
确定后，部门列出现规范下拉菜单
注意事项：
来源要选有数据的具体范围（如$A$2:$A$8），不要选整列（$A:$A），否则下拉菜单会有大量空白选项
如果以后部门有调整，记得同步更新配置表里的范围（比如新增一个部门，就把来源改为$A$2:$A$9）

好处：数据来源统一，后续COUNTIF统计、透视表分析完全准确。


高频场景实战

场景一：劳动合同到期提醒（提前60天）
劳动合同到期前需准备续签材料，提前60天提醒更稳妥。
H列为合同到期日期，状态列公式：
=IF(H2="","",IF(H2<TODAY(),"⚠️合同已到期",IF(H2-TODAY()<=60,"📌60天内到期，请准备续签","✅合同有效")))
条件格式配合标色：已到期红色，60天内橙色。

场景二：离职员工自动屏蔽提醒
离职员工的证件到期提醒应自动静默，避免干扰在职员工视图。
J列为在职状态，到期状态公式调整：
=IF(J2="离职","已离职-无需提醒",IF(H2="","",IF(H2<TODAY(),"⚠️合同已到期",IF(H2-TODAY()<=60,"📌60天内到期","✅合同有效"))))
离职员工行自动显示"已离职-无需提醒"，不再触发红色/橙色条件格式。

场景三：一键统计各部门在职人数
不用筛选，直接在汇总区用COUNTIFS：
=COUNTIFS(D:D,"财务部",J:J,"在职")
各部门各一行，一张汇总小表，数据随时看。

避坑指南
坑1：TODAY()公式结果显示成数字（如45789）
原因：日期格式没有设对，Excel把日期当成了序列数字。
解法：选中日期列 → 右键 → 设置单元格格式 → 分类选"日期" → 选 "2012-03-14" 格式即可。

坑2：DATEDIF函数找不到
原因：DATEDIF是Excel历史遗留函数，不在"插入函数"列表里，但完全可用。
解法：直接在单元格输入 =DATEDIF( 开始，强行输完即可正常计算。

坑3：新增一行员工信息，公式没有自动扩展
原因：公式是手动拖拽填充的，新增行不在填充范围内。
解法：选中台账数据区 → Ctrl+T → 勾选"表包含标题" → 确定，把台账变成"表格"格式，新增行时公式会自动延伸。

坑4：数据验证后仍然能粘贴进重复数据
原因：从外部直接粘贴整列会绕过数据验证规则。
解法：录入时用手动输入而非直接粘贴；如需批量导入，先在草稿列检查重复后再转移。

坑5：员工离职后直接删除行，历史记录永久丢失
原因：删除行会清空该员工全部记录，无法追溯。
解法：在"在职状态"列标记"离职"，保留原行。需要归档时，筛选离职员工 → 另存为"离职归档表"。

获取实战模板

按上面所有公式搭建这套台账需要一定时间，我帮你整理好了一份《HR员工入离职台账全自动模板》，可以直接上手用：
✅ 工号防重录入（数据验证已配好）
✅ 合同/证件到期自动提醒（三色提醒公式预设）
✅ 司龄/试用期自动计算（DATEDIF+EDATE）
✅ 下拉菜单规范录入（配置表已内置）
✅ 离职屏蔽提醒自动生效

关注【华杰科技工作室】后台回复【资料】获取《HR员工入离职台账全自动学习模板》，填上员工信息就能直接用。

你们公司的HR台账现在是什么状态？用Excel自建，还是用系统？评论区聊聊，遇到卡点华杰帮你出招。
转给HR同事——这套公式组合，真的能救一些人的台账。