日期与时间——AI 帮你算工龄、账期、截止日

第6章：日期时间函数

日期是 Excel 里最容易被低估的数据类型。考勤计算、账期管理、合同到期预警、项目排期——这些职场刚需，几乎全部依赖日期函数。本章从 Excel 存储日期的底层原理讲起，深入 DATEDIF、WORKDAY、EDATE 等核心函数，配合 15 个真实案例和完整 AI Prompt 流程，让你彻底掌握日期函数的精髓。

日期时间函数的职场场景

在动手学函数之前，先思考一个问题：你的日常工作里，哪些事情跟"日期"有关？几乎每个职场人都会回答：到处都是。

**考勤管理：**每月要统计员工实际出勤天数、迟到次数、请假天数。HR 需要计算某员工本月应出勤多少天（排除周末和节假日），实际打卡多少天，差异是多少。这些全是日期函数的工作。

**账期管理：**供应商给你 45 天账期，从发票开具日往后算，何时到期？距今还有几天？快到期的账单要提前预警。财务部门每天面对这类计算。

**合同到期预警：**公司有几十份合同，每份合同有不同的到期日。哪些合同在 30 天内到期需要续签？哪些已经过期？这需要实时计算。

**项目排期：**项目从 3 月 1 日启动，计划用 60 个工作日完成，预计哪天结束？中间经过清明节、五一假期，这些都要排除在外。

**员工工龄计算：**入职 2018 年 7 月 15 日，今天工龄是多少年多少个月？精确到月的工龄直接影响年假天数、薪资等级。

所有这些场景，Excel 都有专门的函数解决方案。关键在于：你要先理解日期的底层逻辑，才能真正用好这些函数。

日期基础：Excel 如何存储日期

这是很多人跳过但最值得花 5 分钟搞清楚的知识点。Excel 里的日期，本质上是一个序列号（Serial Number）。

Excel 把 1900 年 1 月 1 日定为第 1 天（序列号 = 1），此后每过一天序列号加 1。所以：

• 2024 年 1 月 1 日的序列号 = 45292
• 2024 年 6 月 15 日的序列号 = 45458
• 两个日期相减，得到的就是天数差

这解释了为什么 Excel 里的日期可以直接做加减运算——它们本质上就是数字。

ℹ️ Note

为什么理解这个很重要：

当你在单元格里输入日期，Excel 存的是序列号。当你设置了"日期格式"，它只是把数字显示成你看到的"2024-01-15"样式。这就是为什么有时候你明明输入的是日期，复制到另一个地方却变成了数字——是格式没有随之复制过来。

四个基础日期函数

基础案例1：自动显示今天日期

场景：报表标题需要显示"数据截止至：2024年1月15日"，每天自动更新。

="数据截止至："&TEXT(TODAY(),"yyyy年m月d日")

这个公式把 TODAY() 得到的序列号，通过 TEXT 函数格式化成中文日期样式，再用 & 拼接文字。每次打开文件，自动显示当天日期。

基础案例2：计算年龄

场景：A1 单元格存了员工生日（如 1990-05-20），计算当前年龄。

=YEAR(TODAY())-YEAR(A1)-IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))>TODAY(),1,0)

逻辑分解：先用今年年份减去出生年份，得到一个粗略年龄。然后判断：今年的生日是否还没到？如果没到，说明今年还没过生日，年龄要再减 1。

⚠️ Warning

**注意：**这是"周岁"算法。中国法律、社保、年假计算一般用周岁。虚岁在函数层面处理起来更复杂，本书不展开。

基础案例3：生成动态标题

场景：月报标题需要自动显示当月，如"2024年1月销售报告"。

=YEAR(TODAY())&"年"&MONTH(TODAY())&"月销售报告"

DATEDIF——Excel 最神秘的隐藏函数

DATEDIF 是 Excel 里最神奇的存在：它存在于 Excel 中，能正常使用，但官方文档里查不到它，函数自动补全里也没有它的名字。

为什么 DATEDIF 不在函数列表里

这要追溯到 Excel 的历史。DATEDIF 原本是 Lotus 1-2-3（Excel 的前身竞品）的函数，微软在早期 Excel 版本中兼容了这个函数。后来发现该函数在某些边界情况下有 bug，微软决定不在新版本中正式推广它，但为了向后兼容，保留了其功能。结果就是：它能用，但官方不想让你找到它。

不管怎样，DATEDIF 在处理日期差计算时极其好用，而且是很多 HR、财务人员的必备工具。

语法：

=DATEDIF(开始日期, 结束日期, 计算单位)

六种参数详解

⚠️ Warning

已知 Bug 警告："MD" 参数在某些月末日期组合下会给出错误结果（如起始日期在月底）。如果你的业务对精确度要求极高，建议改用 "D" 减去 "M" 对应天数的方法计算，或咨询 AI 给出更稳健的替代公式。

案例1：计算员工工龄（年+月精确版） 场景：HR 表格 A2 是入职日期，需要在 B2 显示"3年9个月"格式的工龄。

帮我写一个计算工龄的Excel公式

我有一个Excel员工信息表，A2单元格是员工入职日期（格式如2020-03-15），我需要在B2单元格计算工龄，显示格式为"X年X个月"，比如"3年9个月"。需要用TODAY()自动计算，每次打开文件自动更新。请给我完整的公式。

=DATEDIF(A2,TODAY(),"Y")&"年"&DATEDIF(A2,TODAY(),"YM")&"个月"

案例2：计算合同剩余天数 场景：C2 是合同到期日，需要在 D2 显示距今还有多少天，过期显示"已过期"。

=IF(C2


  案例3：计算年龄（周岁）
  场景：B2 是员工出生日期，用 DATEDIF 精确计算周岁。

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

  案例4：账期到期预警（结合条件格式）
  场景：E 列是账单到期日，需要在 F 列显示剩余天数，并且通过条件格式让剩余不足 30 天的行变红色背景。

F列公式（F2填入，向下填充）： =E2-TODAY()

条件格式设置步骤：

1. 选中整个数据区域（如A2:F100）
2. 开始→条件格式→新建规则→"使用公式确定要设置格式的单元格"

红色规则（剩余不足30天或已过期）： 公式：=$F2=30,$F2

案例5：计算两个项目之间的工作日间隔 场景：G2 是项目A结束日，H2 是项目B开始日，计算两者之间有多少个工作日的间隔（排除周末，不排除节假日）。

=NETWORKDAYS(G2,H2)-1

AI 案例：让 AI 帮你选择正确的 DATEDIF 参数

DATEDIF 六个参数最容易搞混。遇到新的日期差计算需求，直接把需求描述给 AI 是最高效的方式：

工作日计算：WORKDAY / NETWORKDAYS / WORKDAY.INTL

工作日计算是日期函数里最实用的场景之一。三个核心函数分工明确：

• WORKDAY(开始日期, 天数, [节假日])：从某天起，N 个工作日后是哪天

• NETWORKDAYS(开始日期, 结束日期, [节假日])：两个日期之间有多少个工作日

• WORKDAY.INTL(开始日期, 天数, [周末定义], [节假日])：自定义哪些天算周末（适合六天工作制、弹性排班等）

  案例1：计算 N 个工作日后的日期（项目截止日） 场景：A2 是项目启动日，需要在 B2 计算 45 个工作日后的截止日（排除周末，不含节假日）。

=WORKDAY(A2,45)

案例2：计算两日期间的工作日数 场景：C2 是合同签署日，D2 是交付截止日，需要计算这段时间内有多少个工作日可以完成工作。

=NETWORKDAYS(C2,D2)

案例3：排除自定义节假日 场景：公司年底盘点期间（12月28日到12月31日）算停工日，项目从 E2 启动，需要 30 个工作日，排除这四天停工日。假设 H2:H5 存放了这四个停工日期。

=WORKDAY(E2,30,H2:H5)

案例4：考勤应出勤天数计算 场景：需要计算某员工 2024 年 1 月应该出勤多少天（排除周末和法定节假日）。月初日期在 A2，月末日期在 B2，节假日列表在 Sheet2 的 A 列。

=NETWORKDAYS(A2,B2,Sheet2!A:A)

案例5：AI 帮你处理中国法定节假日排除 场景：你想在项目排期表里排除 2024 年全年法定节假日，但懒得手动输入所有节假日日期。

帮我生成2024年中国法定节假日的完整日期列表（包含调休规则）。
我需要的格式是每个日期单独一行，格式为YYYY-MM-DD，方便直接粘贴到Excel的A列作为NETWORKDAYS的节假日参数。
注意：需要包含调休导致的补班日从节假日列表里排除（调休补班日是正常工作日，不应列入）。

日期格式转换：TEXT 函数 + 日期

TEXT 函数把日期序列号格式化为你想要的文本样式。语法：

=TEXT(日期值, "格式代码")

常用格式代码：

• "yyyy-mm-dd" → 2024-01-15

• "yyyy年m月d日" → 2024年1月15日

• "aaaa" → 星期一（中文星期全称）

• "aaa" → 周一（中文星期简称）

• "dddd" → Monday（英文全称）

• "m/d/yyyy" → 1/15/2024（美式格式）

  案例1：将日期转为"2024年1月15日"中文格式 场景：A2 存有日期值（Excel 日期格式），需要在 B2 显示中文格式。

=TEXT(A2,"yyyy年m月d日")

案例2：提取星期几 场景：排班表中，A2 是具体日期，需要在旁边显示对应的星期几（中文），以便排班人员快速识别。

=TEXT(A2,"aaaa")

案例3：将"20240115"文本转为日期 场景：从系统导出的数据，日期以"20240115"这种 8 位纯数字文本格式存储，需要转换为 Excel 可识别的日期格式。

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

案例4：不同国家日期格式互转 场景：出口业务中，中国同事习惯用 YYYY-MM-DD 格式，美国客户习惯用 M/D/YYYY 格式，需要互相转换。

中式→美式：=TEXT(A2,"m/d/yyyy")
美式文本→中式日期（假设A2是"1/15/2024"这种文本）：=DATEVALUE(A2)

EDATE / EOMONTH：月末与 N 月后日期

这两个函数在财务和合同管理中极为常用：

• EDATE(开始日期, 月数)：从某日期起，往后（或往前）N 个月的同一天

• EOMONTH(开始日期, 月数)：从某日期起，N 个月后那个月的最后一天

  案例1：信用卡账期计算 场景：信用卡账单日是每月 5 日，账单出来后有 25 天还款期。A2 是账单日（如 2024-01-05），需要计算还款截止日。

=EDATE(A2,1)-A2+A2+25

案例2：季度末日期 场景：需要计算从当前日期所在季度的最后一天（用于季度结算）。

=EOMONTH(TODAY(),MOD(3-MOD(MONTH(TODAY()),3),3))

案例3：月末自动填充（财务月度报表） 场景：财务需要生成 2024 年全年 12 个月的月末日期列表（1月31日、2月29日……12月31日），在 A1 输入年份，自动生成。

在B1输入月份序号（1-12），C1填入：
=EOMONTH(DATE($A$1,B1,1),0)

向右填充12列，配合B行输入1到12，即可生成全年12个月末日期。

综合案例：考勤自动统计表（完整 AI Prompt 设计流程）

这个综合案例模拟真实职场需求：HR 需要一个考勤统计表，能自动计算每位员工的应出勤天数、实际出勤天数、缺勤天数，以及出勤率。

需求分析

• 数据结构：员工姓名（A列）、部门（B列）、入职日期（C列）、本月实际打卡天数（D列）
• 需要自动计算：本月应出勤天数（排除周末和节假日）、缺勤天数、出勤率
• 节假日列表存储在 Sheet2 的 A 列
• 当月月初和月末需自动获取（不需要手动输入）

完整 AI Prompt 设计流程

✅ Tip

使用 AI 设计表格的最佳实践：

不要一次性把所有需求扔给 AI，而是拆分成步骤逐步确认。每步得到公式后，先在 Excel 里验证正确，再继续下一步。这样出错时容易定位，也更容易调整。

日期函数速查表

上一章 ← 第5章：文本函数 下一章 第7章：数学与统计函数 →