Excel 日期和时间函数 - DATE、NETWORKDAYS 及实用模式

序列号模型 - Excel 如何存储日期

Excel 将日期存储为从 1900 年 1 月 1 日开始计算天数的序列号。2026 年 5 月 21 日是整数 46163。时间存储为一天的分数:中午 12:00 是 0.5,下午 6:00 是 0.75。像“2026-05-21 18:00”这样的组合日期和时间变成 46163.75。理解了这一点,每个日期函数都可以被视为对数字的算术运算。

因为日期是数字,两个日期之间的差就是简单的减法,增加天数就是普通的加法。Excel 确实有一个历史遗留问题:它错误地将 1900 年视为闰年,这是为了与 Lotus 1-2-3 兼容而保留的产物。这只影响 1900-01-01 到 1900-02-28 之间的日期,在实际电子表格中几乎没有人会遇到。

DATE 和 TIME - 从组件构建值

DATE(year, month, day) 从三个整数构造日期。DATE(2026, 5, 21) 返回 2026 年 5 月 21 日的序列号。Excel 会自动规范化超出范围的值,因此 DATE(2026, 13, 1) 变成 2027 年 1 月 1 日,DATE(2026, 0, 31) 变成 2026 年 1 月 31 日。这对月份边界计算和滚动窗口报表非常有价值。

TIME(hour, minute, second) 返回 0 到 1 之间的值,表示一天中的时间。与 DATE 结合使用可获得完整时间戳。对于字符串输入,DATEVALUE 和 TIMEVALUE 将“2026/05/21”或“18:30:00”等文本解析为序列号,在从外部系统导入数据而不经过 Power Query 时很有用。

TODAY 和 NOW - 处理易失函数

TODAY() 返回当前日期,NOW() 返回当前日期和时间。两者都是易失函数,在工作簿重新计算时会重新计算,因此打开文件或编辑任何单元格都可能更新它们。它们对实时仪表板很有用,但不适合需要捕获固定时刻的场景。

要记录某行的创建时刻,使用键盘快捷键 Ctrl + ; (插入今天的日期) 或 Ctrl + Shift + ; (插入当前时间)。两者都插入不会后续更改的字面值。通过 VBA 或 Office Scripts,你还可以使用 Worksheet_Change 事件在特定列被编辑时将 Now 写入相邻单元格,提供自动审计追踪。到处使用易失函数也会减慢重新计算速度,因此将它们保留给真正需要实时更新的单元格。

NETWORKDAYS - 计算工作日

NETWORKDAYS(start, end, [holidays]) 返回两个日期之间的工作日数,排除周六和周日。NETWORKDAYS(“2026-05-04”, “2026-05-08”) 返回 5。可选的第三个参数接受包含节假日日期的单元格范围,这些日期也将被排除。在工作簿其他位置维护节假日列表可以让公式干净地处理公司特定或国家特定的日历。

对于非标准工作周,NETWORKDAYS.INTL(start, end, weekend, [holidays]) 接受从 1 (周六和周日) 到 17 (仅周二) 的周末代码,或自定义的 7 字符字符串如“0000110”来明确标记周末天。这可以处理中东的工作周 (周五和周六休息)、单日周末国家和轮班制度。结合 SUMPRODUCT,你可以计算跨复杂班次模式的月度工作时间。

EOMONTH 和 EDATE - 基于月份的算术

EOMONTH(date, months) 返回偏移指定月数后的月末日期。EOMONTH(TODAY(), 0) 给出本月末,EOMONTH(TODAY(), -1) 上月末,EOMONTH(TODAY(), 1) 下月末。这对结账期间、月度报表和总是落在月末的循环账单日期至关重要。

EDATE(date, months) 将同一天向前或向后偏移。对 2026 年 1 月 31 日使用偏移 1 的 EDATE 返回 2026 年 2 月 28 日,因为二月没有 31 日。这种安全的滚动正是抵押贷款还款计划、保险续期和订阅计费所需要的。对于年度偏移,只需乘以 12:EDATE(date, 12) 向前推进一年。

WEEKNUM 和 ISOWEEKNUM - 周数陷阱

WEEKNUM(date, [type]) 返回一年中的周数。第二个参数选择一周起始日的惯例:1 表示周日,2 表示周一。关键区别是 WEEKNUM 将第 1 周定义为包含 1 月 1 日的那一周,而 ISO 8601 标准将第 1 周定义为包含 1 月 4 日的那一周 (因此它必须包含新年的至少四天)。

这种差异在年度边界处最为明显。2026 年 1 月 1 日 (周四) 按 WEEKNUM 是第 1 周,但按 ISO 是 2025 年的第 53 周。国际项目报告通常使用 ISO 周,因此跨团队的周度汇总可能因各方使用 WEEKNUM 还是 ISOWEEKNUM 而显示不同的总数。为你的表格选定一种惯例,在顶部明确记录,就能避免之后数小时的困惑。