🤖 AI 跟我学 新手入门

Excel 日期计算 AI：工龄/账期/到期日一句话搞定

Excel 日期计算 AI 教程：用 AI 写工龄、账期、到期日、工作日计算公式，含 6 个真实场景 prompt 模板、DATEDIF 隐藏函数和"跨年/跨闰年"边界处理

发布 2026/05/20 📎 参考官方文档

HR 让你算 200 个员工的”工龄”——“X 年 X 个月”那种格式。财务让你算账期——“客户付款日 - 开票日”。运营让你算”促销活动还剩几天到期”。这三种活儿你打开 Excel，第一反应想用 YEAR() / MONTH() 拼，拼出来发现 2024 年 2 月 29 日和 2 月 28 日老是差一天。这时候让 Excel 日期计算 AI 出手，给一个隐藏函数 DATEDIF 直接搞定。

把日期计算需求用大白话告诉 AI，AI 给一个 DATEDIF 公式或者 NETWORKDAYS 公式，附带跨闰年 / 跨月的边界处理。本文给你万能 prompt 模板 + 6 个真实场景 + 3 个最常见的”日期算错”坑。

为什么 Excel 日期算起来这么烦

3 个根本原因：

  1. 闰年陷阱：2024 年 2 月有 29 天、2026 年 2 月 28 天，简单减天数会差一天
  2. 月份长度不一：2 月 28、4 月 30、7 月 31——按 30 天算月差肯定错
  3. 工作日 vs 自然日：账期算工作日（跳过周末和节假日），不能直接算天数

Excel 其实给了 5 个隐藏好函数专门解决这事：

  • DATEDIF：算两个日期相差年/月/日（菜单里找不到，能用）
  • NETWORKDAYS：算两日期间工作日数（自动跳过周末）
  • NETWORKDAYS.INTL：自定义”哪些天算休息日”（含中国法定节假日）
  • EOMONTH：算某月最后一天 / 下个月某天
  • EDATE：算 N 个月后的同一天（自动处理 2 月 30 这种异常）

AI 知道这 5 个函数怎么搭配，你只需要描述需求。

用哪个工具

工具准确性节假日支持国内可用备注
Claude优秀优秀需代理边界处理最稳
ChatGPT GPT-5优秀优秀需代理同样优秀
豆包 / Kimi良好良好直接可用国内节假日数据更准
WPS AI良好良好直接可用WPS 直接调用

国内场景（如算社保账期、合同到期）优先用国产 AI，它们对中国法定节假日表的训练数据更新更勤。

万能 prompt：Excel 日期计算专家

下面这段套到任何 AI 工具：

📋 Prompt 模板 
你是一位资深 Excel 日期计算专家，精通 DATEDIF / NETWORKDAYS / EOMONTH / EDATE / WORKDAY 全部日期函数，能处理跨年、跨闰年、工作日、法定节假日等所有边界场景。
我的需求：
[用大白话描述要算什么。比如「算员工工龄，格式 X 年 X 个月零 X 天，截至今天」]
数据情况：
    

  • 表格名：[]
    • 

  • 日期列在哪：[比如 B 列是入职日、C 列是离职日（如果有）]
    • 

  • 数据范围：[B2 到 B201]
    • 

  • 日期格式：[2026-05-17 / 2026/5/17 / 文本 “20260517”]
    • 

期望输出：
    

  • 结果格式：[X 年 X 月 / 总天数 / 工作日数 / 月数]
    • 

  • 结果放哪：[新列 D / 替换原列]
    • 

特殊要求：
    

  • 是否考虑闰年：[是 / 否]
    • 

  • 是否扣除周末：[是 / 否]
    • 

  • 是否扣除中国法定节假日：[是 / 否，如是请告诉我节假日清单]
    • 

  • 边界处理：[如果某行日期为空怎么办]
    • 

Excel 版本：[Excel 365 / Excel 2019 / WPS]
请按以下格式输出：
推荐公式
[直接给能粘的公式]
公式解释
[逐行说明 DATEDIF / NETWORKDAYS 等函数怎么用]
怎么用
    

  • 把公式粘到：[单元格]
    • 

  • 拖拽方向：[向下]
    • 

  • 注意事项：[闰年 / 跨月]
    • 

边界处理
    

  • 日期为空：返回什么
    • 

  • 起始日期晚于结束日期：返回什么
    • 

  • 同一天：返回什么
    • 

测试样例
给 3-5 行日期 + 预期结果，让我验证。

填好方括号发给 AI。下面 6 个场景示范。

6 个真实场景示例

场景 1：算工龄（X 年 X 月 X 日）

需求：B 列是入职日期，截至今天，要在 C 列算工龄”X 年 X 个月零 X 天”。

=DATEDIF(B2, TODAY(), "Y") & " 年 " & DATEDIF(B2, TODAY(), "YM") & " 个月零 " & DATEDIF(B2, TODAY(), "MD") & " 天"

DATEDIF 三种单位：

  • "Y"：完整年数
  • "YM"：忽略年后的剩余月数
  • "MD"：忽略年月后的剩余天数

3 个拼起来就是”X 年 X 月 X 天”。注意 TODAY() 是动态的，每天打开表格自动更新。

场景 2：算账期天数（扣除周末）

需求：F 列开票日、G 列付款日。要算”实际账期”，扣除周末。

=NETWORKDAYS(F2, G2)

NETWORKDAYS 自动跳过周六和周日。

如果要扣除法定节假日，建一个 H 列「节假日」存所有放假日期，公式变：

=NETWORKDAYS(F2, G2, H2:H30)

第 3 个参数是节假日范围。每年的法定节假日清单可以让 AI 帮你列。

场景 3：算”距离合同到期还剩几天”

需求：合同到期日在 D 列，要算”距今天还剩 X 天”，过期了显示「已过期 X 天」。

=IF(D2-TODAY()>=0, "还剩 " & D2-TODAY() & " 天", "已过期 " & TODAY()-D2 & " 天")

或者更紧凑：

=TEXT(D2-TODAY(), "还剩 0 天;已过期 0 天;到期日")

TEXT 函数的”正/负/零”三段格式，一行 IF 完成。

场景 4：算”3 个月后的同一天”

需求：B 列是发货日，要在 C 列算”3 个月后的售后到期日”。

如果直接 =B2+90 是按 90 天算，跨月跨闰年会偏。正确用 EDATE：

=EDATE(B2, 3)

EDATE 处理边界：

  • 1 月 31 日 + 1 个月 = 2 月 28/29 日（自动取该月最后一天）
  • 跨闰年自动处理

如果要算”3 个月后那个月的最后一天”：

=EOMONTH(B2, 3)

场景 5：算”本月第 X 个工作日”

需求：政策规定”工资在每月第 5 个工作日发”，要算 2026 年每个月的第 5 个工作日。

=WORKDAY(EOMONTH(A2, -1), 5)

逻辑：

  • EOMONTH(A2, -1) 取上个月最后一天
  • WORKDAY(..., 5) 从这天起向后数 5 个工作日

A2 是任意”该月的某一天”，公式自动算出该月的第 5 个工作日。

加节假日参数：

=WORKDAY(EOMONTH(A2, -1), 5, 节假日表!A:A)

场景 6：算”两个时间戳的小时差”

需求：客户开单时间 A2 = “2026-05-17 09:30:00”，关单时间 B2 = “2026-05-19 14:00:00”，要算服务总小时数。

=(B2-A2)*24

Excel 日期减法得到的是”天数”（含小数），乘以 24 转小时。

只算工作时段（9 点到 18 点，扣除午休 12 点到 13 点）需要 AI 给更复杂的公式：

=NETWORKDAYS(A2, B2)*8 - (HOUR(A2)<9)*0 - ...

完整公式较长，让 AI 按你定义的”工作时段规则”专门写。

手把手 5 步走

第 1 步：先检查日期格式

打开 Excel 选中日期单元格，看右下角：

  • 显示”日期”是真日期，可以直接算
  • 显示”文本”或”常规”是文本格式的日期，先转

文本日期转真日期 2 种方法：

  1. 用 DATEVALUE：=DATEVALUE(A2) 把”2026-05-17”转成真日期
  2. 数据 - 分列 - 完成（不分列，但能强制重格式化）

第 2 步：明确要算什么

写 prompt 前想清楚：

  • 算”间隔”还是”加减”
  • 单位是”年/月/日/小时/工作日/自然日”
  • 含起始日还是不含（NETWORKDAYS 默认含两头）
  • 跨年 / 闰年 / 节假日怎么处理

第 3 步：发 prompt 拿公式

填好万能 prompt 发给 AI。贴 2-3 行真实日期样本 + 期望结果，AI 给的公式更精准。

第 4 步：边界值测试

公式拿到先用 4 类极端日期测：

  1. 同一天：起始 = 结束（应返回 0 或 1，看业务）
  2. 跨闰年：2024-02-28 → 2025-03-01
  3. 月末跨月：2026-01-31 + 1 个月（应该是 2026-02-28）
  4. 空值：起始日空 / 结束日空

4 类都符合预期再扩到全表。

第 5 步：考虑 TODAY() 是动态的

TODAY() 函数每次打开 Excel 都重新算。如果要”固定算今天”（保留快照），算完后用 Ctrl+C → 选择性粘贴 - 仅值 把公式转死值。

5 个让 AI 算日期更准的技巧

技巧 1：贴 5 行包含边界 case 的样本

不要只贴普通日期，贴：

  • 1 行同年同月
  • 1 行跨年
  • 1 行跨闰年（2024-02-28 → 2024-03-01）
  • 1 行起始 = 结束
  • 1 行起始 > 结束

让 AI 看到全貌再给公式。

技巧 2：明确”是否含起止日”

NETWORKDAYS / DATEDIF 默认含起止两头。如果要”不含起始日”，公式要 -1。在 prompt 里写清楚。

技巧 3：法定节假日建一张维表

每年元旦 / 春节 / 清明 / 劳动 / 端午 / 中秋 / 国庆的日期都不同。建一个 sheet 叫「节假日」，A 列列出当年所有放假日，NETWORKDAYS 引用这个表。让 AI 帮你列：「列出 2026 年中国法定节假日清单（含调休）」。

技巧 4：动态算今天用 TODAY()，固定快照用具体日期

工龄要”每天自动更新”用 TODAY()；要”算到某月某日的工龄”（如年报）用 DATE(2026,12,31)。两种用法 prompt 里讲清。

技巧 5：结果格式化用 TEXT

直接 DATEDIF 出来是数字。要”X 年 X 月”格式用 TEXT 或字符串拼接。让 AI 在公式里直接套好格式，省二次处理。

一个常见失败案例 + 怎么救

失败场景：用 =YEAR(C2)-YEAR(B2) 算工龄，结果 2024-01-01 入职、2025-12-31 算”工龄 1 年”，明显不对（实际 2 年差 1 天）。

救援步骤：

  1. 改用 DATEDIF：DATEDIF 按”满年满月”算，不会出这种锅
  2. 明确”满 1 年”定义：=DATEDIF(B2, C2, "Y") 算的是”满 1 周年”，不是”跨年数”
  3. 测边界值：拿 2024-01-01 → 2024-12-31（差 1 天满 1 年）和 2024-01-01 → 2025-01-01（满 1 年）测，看是不是符合预期

记住：日期计算永远先想清楚”业务上 1 年是什么意思”——是跨年数 / 满 365 天 / DATEDIF 满 1 周年——三种结果不一样。

进阶玩法 + 类似场景

进阶玩法 1：算”半年报截止日”

每年 6 月 30、12 月 31 是半年报截止日。任意日期自动算”下一个半年报截止日”：

=IF(MONTH(A2)<=6, DATE(YEAR(A2), 6, 30), DATE(YEAR(A2), 12, 31))

或者用 EOMONTH + 条件：

=EOMONTH(DATE(YEAR(A2), IF(MONTH(A2)<=6, 6, 12), 1), 0)

进阶玩法 2：动态考勤表

A 列日期、B 列是不是工作日（用 NETWORKDAYS 判断）、C 列实际到岗、D 列异常标记。让 AI 写一个完整的考勤模板。

进阶玩法 3：分段计费 / 阶梯账期

复杂账期规则：“前 30 天免息、31-60 天按 5% 算、60 天以上按 10% 算”。让 AI 用嵌套 IF 或 IFS 写完整公式。

类似场景

常见问题

Q：DATEDIF 在菜单里搜不到？ A：DATEDIF 是 Excel 隐藏函数，所有版本都能用，但函数列表里不显示。直接在单元格输入 =DATEDIF(...) 就能用，提示参数也会出来。

Q：单元格日期显示成数字（如 45810）怎么办？ A：数字是 Excel 的日期序列号（1900-01-01 是 1）。选中 - 右键 - 设置单元格格式 - 日期，挑个格式就显示成日期了。计算本身不受影响。

Q：跨闰年算工龄准吗？ A：DATEDIF 自动处理闰年，准。普通减法（C2-B2）算”日数”也准。唯一坑是 YEAR(C2)-YEAR(B2) 这种简单年份差，会忽略月日。

Q：怎么生成 2026 年的法定节假日清单？ A：让 AI 直接列：“列出 2026 年中国国务院公布的法定节假日清单（含调休上班日）“。AI 给出后对照国务院办公厅当年的发文（一般每年 10-12 月发次年）核对一遍，复制到 Excel 一列存好备用。

Q：WPS 表格能用 DATEDIF / NETWORKDAYS 吗？ A：能。WPS 支持全部 Excel 日期函数，本文公式 100% 在 WPS 直接跑。