1. 项目概述：为什么说日期时间函数是Excel的“隐形引擎”？

如果你经常和Excel打交道，处理过考勤、项目排期、销售报表或者任何带时间戳的数据，那你一定遇到过这样的场景：老板让你从一堆杂乱的日期里快速算出项目周期，或者从包含年月日时分秒的单元格里单独提取出月份来做月度汇总。这时候，如果还在手动拆分、心算或者写一长串复杂的文本函数，效率就太低了。日期时间函数，就是Excel里专门为解决这类问题而生的“隐形引擎”，它能把看似简单的日期和时间数据，变成驱动高效数据分析的核心动力。

我见过太多同事，因为不熟悉这些函数，在时间数据的处理上耗费大量精力。比如，用LEFT、MID、RIGHT去“硬拆”一个标准日期，结果遇到格式问题就报错；或者用计算器去加天数，一旦数据量上来就难免出错。实际上，Excel将日期和时间存储为序列号，这个设计本身就为函数计算提供了极大的便利。理解并掌握这十几二十个核心的日期时间函数，不仅能让你处理相关任务的效率提升十倍，更能让你的数据分析逻辑更加严谨和自动化。

今天要聊的这16种经典用法，绝不是简单的函数列表罗列。我会结合我十多年处理各类报表的实际经验，从最基础的日期提取、推算，到复杂的网络工作日计算、动态时间区间构建，为你拆解每一个函数背后的逻辑、适用场景，以及那些官方文档里不会写的“坑”和技巧。无论你是需要快速制作项目甘特图，还是分析销售数据的周环比，亦或是管理团队的排班计划，这些用法都能直接拿来套用。我们不止要“会用”，更要“懂为什么这么用”，以及“怎么用最稳”。

2. 核心基石：理解Excel的日期与时间系统

在挥舞函数这把“手术刀”之前，我们必须先彻底理解Excel如何看待和处理日期与时间。这是所有后续操作的基础，很多诡异错误的根源都出在这里。

2.1 序列号的本质：Excel的“时间戳”

Excel的核心设计是：日期是一个整数，时间是一个小数。

• 日期系统：Excel默认使用“1900年日期系统”，它将1900年1月1日视为序列号1，而2023年1月1日对应的序列号大约是44927。这意味着，日期本质上是可以进行加减运算的数字。2023/1/2-2023/1/1的结果是1，代表相差一天。
• 时间系统：时间被表示为一天24小时的小数部分。例如，中午12:00:00是0.5（因为是一天的一半），下午6:00:00是0.75。因此，一个完整的日期时间，如2023/1/1 18:00:00，在Excel内部实际存储为44927.75。

注意：这里有一个著名的“1900年闰年Bug”。Excel为了兼容古老的Lotus 1-2-3，将1900年错误地当作闰年，所以序列号60对应的是1900年2月29日（一个不存在的日期）。这个Bug被永久保留，但在实际使用中，它只影响1900年3月1日之前的日期计算，对我们现代数据处理几乎无影响，但你需要知道这个历史渊源。

2.2 格式与值的分离：最常见的“坑”

这是新手最容易困惑的地方。单元格的“显示格式”和“实际存储值”是两回事。一个单元格显示为“2023年1月1日”，它的实际值可能是44927。如果你用=LEFT(A1,4)想去提取“2023”，结果只会得到错误，因为你在对一个数字进行文本操作。

实操心得：在不确定时，最快速的检查方法是：选中单元格，将其格式临时改为“常规”。如果它变成了一串数字，那它就是日期/时间序列值；如果变成了一堆“#”或者还是文本样子，那它可能就是文本格式的“假日期”。处理前，务必用DATEVALUE或TIMEVALUE函数，或“分列”功能将其转换为真正的序列值。

2.3 基础构造函数：DATE, TIME, NOW, TODAY

这四个函数是生成日期时间的起点。

1. =DATE(年, 月, 日)：这是最可靠的日期构造器。给定年、月、日三个数字参数，它返回一个标准的日期序列值。它的智能之处在于参数溢出处理。例如，=DATE(2023, 13, 1)并不会报错，而是会智能地解释为2024年1月1日（2023年+13个月）。这在根据月份偏移计算未来日期时非常有用。
2. =TIME(时, 分, 秒)：与DATE类似，用于构造时间。=TIME(27, 0, 0)会被识别为第二天凌晨3:00。
3. =TODAY()：动态返回当前日期，没有参数。每次工作表重新计算（如打开文件或编辑单元格）时都会更新。常用于制作每日自动更新的报表标题或计算到期日。
4. =NOW()：动态返回当前的日期和时间。同样会随计算更新。

重要技巧：TODAY()和NOW()是易失性函数，即任何单元格的变动都可能触发其重算。在大型复杂工作簿中大量使用可能会略微影响性能。如果只需要一个固定的当前时间戳，可以在输入后按Ctrl+;（日期）或Ctrl+Shift+;（时间）输入静态值。

3. 核心拆解与提取：像外科手术一样处理时间数据

当拿到一个完整的日期时间数据时，我们常常需要将其中的一部分“解剖”出来单独使用。这时就需要提取函数家族。

3.1 年月日时分秒的精准提取

• =YEAR(serial_number)：提取年份，返回一个四位数的年份，如2023。
• =MONTH(serial_number)：提取月份，返回1到12之间的数字。
• =DAY(serial_number)：提取日期，返回1到31之间的数字。
• =HOUR(serial_number)：提取小时，返回0到23之间的数字。
• =MINUTE(serial_number)：提取分钟，返回0到59之间的数字。
• =SECOND(serial_number)：提取秒，返回0到59之间的数字。

经典应用场景1：创建月度汇总报表假设A列是详细的销售日期，你需要在另一个表按月度汇总销售额。你可以插入一个辅助列B，使用=MONTH(A2)提取出月份数字，然后以此作为数据透视表的行字段或SUMIFS函数的条件，轻松实现按月汇总。

经典应用场景2：分析用户活跃时段如果A列是用户操作的时间戳，用=HOUR(A2)提取小时数，然后通过数据透视表或COUNTIF函数，就能快速生成一张“24小时用户活跃度分布图”。

3.2 WEEKDAY与WEEKNUM：洞察时间节奏

• =WEEKDAY(serial_number, [return_type])：返回代表一周中第几天的数字。第二个参数[return_type]至关重要，也是最容易出错的地方。

  • return_type=1或省略：星期天=1，星期六=7。
  • return_type=2：星期一=1，星期日=7。这是国内最常用的系统。
  • return_type=3：星期一=0，星期日=6。
  • 其他类型（11-17）对应不同起始日，可根据需要选择。
  • 实操心得：我强烈建议在任何使用WEEKDAY的公式里都显式地写上return_type参数，例如=WEEKDAY(A2, 2)。这能避免因Excel默认设置不同导致的公式迁移错误，让公式意图更清晰。

• =WEEKNUM(serial_number, [return_type])：返回一年中的第几周。同样，return_type决定一周从哪一天开始（通常1=周日开始，2=周一开始），以及如何定义年度第一周（是包含1月1日的那周，还是第一个完整的周）。

经典应用场景：自动标记周末或计算工作日结合WEEKDAY和条件格式，可以高亮显示所有周末：选中日期区域 -> 条件格式 -> 新建规则 -> 使用公式=WEEKDAY($A2,2)>5-> 设置格式。这样，所有周六和周日就会被自动标记出来。

4. 日期与时间的推算与计算

这是日期时间函数最核心的价值所在：基于已知时间点，计算出过去或未来的时间点。

4.1 简单的加减运算

由于日期时间是数字，最直接的推算就是加减法。

• =TODAY()+7：一周后的日期。
• =A2+30：A2日期30天后的日期。
• =“2023/12/31” - “2023/1/1”：计算两个日期之间的天数差（需确保是真实日期值）。

4.2 EDATE与EOMONTH：处理月份偏移的利器

对于涉及“月”的推算，加减天数会非常麻烦，因为月份天数不固定。这时必须用专门函数。

• =EDATE(start_date, months)：返回与start_date相隔months个月的日期。months为正则向后推，为负则向前推。

  • 例如：=EDATE(“2023-1-31”, 1)结果是2023-2-28。Excel会智能处理月末日期，如果目标月份没有对应的日期（如1月31日加一个月到2月），则返回目标月份的最后一天。这个特性在处理财务月度报告、合同到期日（固定月数后）时极其可靠。

• =EOMONTH(start_date, months)：返回与start_date相隔months个月的那个月份的最后一天。

  • 例如：=EOMONTH(“2023-1-15”, 0)结果是2023-1-31。=EOMONTH(“2023-1-15”, 1)结果是2023-2-28。
  • 经典应用场景：快速生成每个月的最后一天日期，用于制作月度报表的标题或截止日期。结合TODAY()，=EOMONTH(TODAY(),0)就能动态获得本月最后一天。

4.3 WORKDAY与NETWORKDAYS：排除干扰，专注工作日

在项目管理、交货期计算、流程审批等场景中，我们只关心工作日（通常排除周末和法定假日）。

• =WORKDAY(start_date, days, [holidays])：返回在start_date之前或之后（由days正负决定）相隔指定工作日天数的日期。会自动跳过周末（周六、日）和可选的holidays列表。

  • 参数days：工作日天数，非自然日。
  • 参数[holidays]：一个包含法定假日日期的单元格区域。强烈建议将假日列表单独放在一个工作表区域并命名，方便所有公式引用和管理更新。

• =NETWORKDAYS(start_date, end_date, [holidays])：返回两个日期之间的工作日天数。同样排除周末和假日。

  • 注意：NETWORKDAYS的计算是包含start_date和end_date这两天的。例如，周一和周二之间，结果为2。

经典应用场景：项目交付日计算假设项目启动日是2023年10月1日（周日），需要15个工作日完成，且10月1-3日为国庆假期。计算交付日：=WORKDAY(“2023-10-1”, 15, {“2023-10-1”,“2023-10-2”,“2023-10-3”})公式会从10月1日开始，跳过国庆三天假期以及随后的周末，向后数15个工作日，给出确切的交付日期。

实操心得：WORKDAY和NETWORKDAYS默认周末是周六和周日。如果你的工作制不同（如周日单休），则需要使用它们的增强版函数WORKDAY.INTL和NETWORKDAYS.INTL。这两个函数增加了一个[weekend]参数，可以用数字代码（如“11”代表仅周日休息）或“0000011”这样的7位字符串（1代表休息，0代表工作，从周一开始）来定义周末，灵活性极高。

5. 动态日期范围的构建与应用

在制作动态仪表盘或滚动报表时，我们常常需要根据“当前时间”自动确定分析的时间范围，比如“本周至今”、“本月累计”、“过去30天”等。这需要函数组合来实现。

5.1 构建“本周”范围

假设以周一作为一周的开始。

• 本周一：=TODAY()-WEEKDAY(TODAY(),2)+1
  • 解析：WEEKDAY(TODAY(),2)得到今天是本周第几天（周一=1）。用今天减去这个数再加1，就回到了本周一。
• 本周日：=TODAY()-WEEKDAY(TODAY(),2)+7
• 上周一：只需在本周一公式基础上减7：=TODAY()-WEEKDAY(TODAY(),2)+1-7

5.2 构建“本月”范围

• 本月第一天：=EOMONTH(TODAY(),-1)+1
  • 解析：先通过EOMONTH(TODAY(),-1)得到上个月的最后一天，然后加1天，自然就是本月的第一天。
• 本月最后一天：=EOMONTH(TODAY(),0)
• 上月同期（如今天日期是15号，求上月15号）：=EDATE(TODAY(),-1)

5.3 在SUMIFS/COUNTIFS等函数中的应用

有了动态的起止日期，就可以创建动态汇总公式。 例如，统计“本月至今”的销售额（数据在SalesData表，日期列是Date，销售额列是Amount）：=SUMIFS(SalesData[Amount], SalesData[Date], “>=”&EOMONTH(TODAY(),-1)+1, SalesData[Date], “<=”&TODAY())

这个公式的关键在于将动态日期函数用&连接符与比较运算符组合，构建出完整的条件。每次打开文件，汇总范围都会自动更新到最新状态。

6. 日期时间数据的整理与转换

原始数据往往不规范，我们需要将其“清洗”成标准格式才能进行计算。

6.1 文本转日期/时间：DATEVALUE与TIMEVALUE

• =DATEVALUE(date_text)：将存储为文本的日期（如“2023/1/1”、“1-Jan-2023”）转换为日期序列值。转换后需将单元格格式设置为日期格式才能正确显示。
• =TIMEVALUE(time_text)：将存储为文本的时间（如“18:30:00”、“6:30 PM”）转换为时间序列值。

常见问题：如果系统日期格式与文本格式不匹配（如系统为“月/日/年”，文本是“日/月/年”），DATEVALUE可能会转换错误或返回#VALUE!。更稳健的方法是使用“数据”选项卡下的“分列”功能，在向导中明确指定日期格式。

6.2 日期时间合并与拆分

• 合并：如果A列是日期，B列是时间，要合并成一个完整的日期时间：=A2+B2。因为日期是整数，时间是小数，相加即得完整的序列值。
• 拆分：
  • 取日期部分：=INT(A2)。INT函数向下取整，正好去掉时间的小数部分。
  • 取时间部分：=A2-INT(A2)。用原值减去日期部分，得到纯时间。记得将结果单元格格式设置为时间格式。

6.3 处理非标准日期时间字符串

有时数据源会给出“20230101”或“2023-01-01 18:30:00”这样的字符串。可以使用文本函数结合DATE和TIME来构造。

• 对于“20230101”：=DATE(MID(A2,1,4), MID(A2,5,2), MID(A2,7,2))
• 对于“2023-01-01 18:30:00”：=DATEVALUE(LEFT(A2,10)) + TIMEVALUE(MID(A2,12,8))

7. 高级应用与综合案例实战

掌握了单个函数，我们来看看如何将它们组合起来，解决复杂的实际问题。

7.1 案例一：自动生成项目甘特图时间轴

甘特图是项目管理的利器，其横轴是时间。我们可以用函数动态生成时间刻度。

1. 确定起始日期：在某个单元格（如B1）输入项目开始日。
2. 生成工作日序列：假设甘特图以周为单位显示。在B3单元格输入=B1。在C3输入=WORKDAY(B3, 1, HolidayList)，然后向右填充。这样就能生成一列连续的工作日日期。HolidayList是预先定义好的假日范围。
3. 标注周末：选中生成的时间轴，使用条件格式，公式为=WEEKDAY(C$3,2)>5，设置为浅色填充，即可自动高亮周末列，让甘特图更清晰。

7.2 案例二：计算员工考勤与加班时长

假设打卡记录中，A列是日期，B列是上班时间，C列是下班时间。

1. 计算每日工作时长：D2 = (C2 - B2) * 24。相减得到天数差（即时间长度），乘以24转换为小时数。注意单元格格式设为“常规”或“数字”。
2. 判断是否加班：假设标准工时为8小时。E2 = IF(D2>8, D2-8, 0)。
3. 汇总月度加班总时长：假设要计算2023年10月的加班总时长。=SUMIFS(E:E, A:A, “>=2023/10/1”, A:A, “<=2023/10/31”)。这里日期条件可以直接用字符串，Excel会进行隐式转换，但更稳妥的做法是引用包含DATE(2023,10,1)和EOMONTH(DATE(2023,10,1),0)的单元格。

7.3 案例三：创建动态的月度滚动报告

制作一个仪表盘，核心指标（如销售额、访问量）自动显示“本月累计”、“上月同期”、“同比变化率”。

1. 本月累计：如前所述，使用SUMIFS与EOMONTH(TODAY(),-1)+1和TODAY()。
2. 上月同期：这里“同期”指上月1号到上月今天号。但“上月今天号”可能不存在（如本月31号，上月可能只有30号）。因此需要更稳健的公式：=SUMIFS(DataRange, DateRange, “>=”&EDATE(TODAY(),-1)-DAY(TODAY())+1, DateRange, “<=”&EOMONTH(EDATE(TODAY(),-1),0))
   • 第一部分EDATE(TODAY(),-1)-DAY(TODAY())+1：上个月的同一天减去本月的日期数再加1，得到上个月1号。
   • 第二部分EOMONTH(EDATE(TODAY(),-1),0)：上个月的最后一天。
   • 这个组合确保了汇总范围是“上个月完整的一个月”，避免了日期不对应的问题。
3. 同比变化率：=(本月累计-上月累计)/上月累计。将单元格格式设置为百分比。

8. 常见问题排查与性能优化技巧

即使理解了原理，在实际操作中还是会遇到各种问题。这里记录一些我踩过的“坑”和解决方案。

8.1 为什么我的日期计算结果是#####或一个奇怪的数字？

• #####：这通常是列宽不够，无法显示完整的日期格式。加宽列即可。
• 奇怪的数字（如44927）：这是日期序列值本身。你需要将单元格格式设置为日期或时间格式。选中单元格 -> 右键 -> 设置单元格格式 -> 分类选择“日期”或“时间” -> 选择你喜欢的显示样式。

8.2 函数返回#VALUE!错误

这是最常见的错误，原因多样：

1. 参数类型错误：例如，给MONTH函数传递了一个文本字符串“2023年1月”。确保参数是真正的日期序列值。用ISNUMBER函数检查一下。
2. 区域设置冲突：在公式中直接写了“1/2/2023”，在某些区域设置下（月/日/年）是1月2日，在另一些设置下（日/月/年）是2月1日。最佳实践是：永远使用DATE(2023,1,2)或“2023-1-2”这种无歧义的格式。
3. “假日期”：数据看起来是日期，实为文本。用=ISTEXT(A1)检查。解决方法：使用DATEVALUE转换，或使用“分列”功能。

8.3 使用WORKDAY系列函数时，假日列表不生效？

1. 检查假日列表格式：假日列表必须是真正的日期值，而不能是文本。同样，用ISNUMBER检查列表中的每个“日期”。
2. 检查引用范围：确保holidays参数引用的单元格区域包含了所有假日，且没有多余的空格或空行。
3. 绝对引用：如果公式需要向下填充，确保假日列表的引用使用绝对引用（如$H$2:$H$10）或定义名称，防止填充时引用区域偏移。

8.4 大量日期时间公式导致文件变慢？

1. 减少易失性函数：TODAY()、NOW()、RAND()等函数会在任何计算发生时重算。如果文件中成百上千个单元格都包含TODAY()，性能会受影响。考虑将其放在一个单独的单元格（如T1），其他公式都引用T1。
2. 将常量计算转化为静态值：对于一些中间步骤或不会变化的历史数据计算，可以在得到正确结果后，复制 -> 选择性粘贴为“值”，以公式替换为静态结果。
3. 使用表格结构化引用和动态数组：如果使用Excel 365或2021，尽量使用动态数组函数（如FILTER、UNIQUE）和表格。它们比大量重复的SUMPRODUCT或数组公式（按Ctrl+Shift+Enter输入的旧式数组公式）效率更高，逻辑也更清晰。

8.5 处理跨夜时间差

计算两个时间点之间的时长，如果结束时间小于开始时间（如晚班从22:00到次日6:00），直接相减会得到负数。正确公式：=IF(结束时间<开始时间, 结束时间+1-开始时间, 结束时间-开始时间)或者更简洁的：=MOD(结束时间-开始时间, 1)。MOD函数求余数，正好可以处理这种“跨天”循环的情况。将结果乘以24即得小时数。

日期时间函数是Excel中逻辑性极强、应用极广的一组工具。从简单的提取到复杂的工作日推算，它们贯穿了数据清洗、转换、计算和分析的全过程。掌握它们的关键在于理解其背后的序列号逻辑，并勤加练习，将其组合应用到实际场景中。开始时可能会觉得参数复杂，但一旦熟悉，你会发现它们带来的效率提升是颠覆性的。我自己的习惯是，建立一个“函数实验区”工作表，把新学的函数和复杂组合公式放进去，用各种边界值测试，观察结果，这是最快的学习路径。