KutoolsforOffice — 一套方案，五大工具。事半功倍。

如何在 Excel 中查找重叠的日期或时间范围？

作者孙修改日期

在 Excel 中，重叠的日期或时间范围可能引发排班冲突、资源分配问题或数据完整性风险。高效识别此类重叠，对于管理排班表、活动规划、预订系统或项目时间线（其中任意时间段均不应与其他时间段重合）至关重要。本文为您提供多种实用方法的分步指南，助您轻松在 Excel 中查找重叠的日期或时间范围，如下方截图所示。
查找重叠日期

使用公式检查重叠的日期/时间范围

VBA 代码 —— 针对大型数据集或需生成报告自动检测重叠的日期/时间范围

使用条件格式 —— 在工作表中直接高亮显示重叠范围，便于快速识别

蓝色右箭头气泡使用公式检查重叠的日期/时间范围

当您需要系统性地检查日期或时间范围是否重叠时，Excel 公式可提供一种快速且灵活的解决方案。此方法适用于中小型数据集，或当您需要逐行输出逻辑结果（TRUE 或 FALSE）以明确标识是否存在重叠时。

典型应用场景：员工排班、活动预订、项目阶段跟踪或租赁管理，其中每行代表一个包含起止时间或日期的时间段。

局限性：尽管该方法适用于中等规模的列表，但在处理超大数据集或需要生成跨越多条记录的完整重叠报告时，可能并非最佳选择。

1. 选中包含所有开始日期的单元格区域。选中后，在名称框（位于编辑栏左侧的字段）中输入一个描述性名称，例如 startdate，然后按 Enter 确认。此步骤可让您在公式中轻松引用整个列表。参见截图：
为开始日期定义一个区域名称

2. 同样，选中结束日期单元格，在名称框中输入一个单元格名称，例如 enddate，然后再次按 Enter。命名区域可让您的公式更易读、更便于重复使用。
为结束日期定义一个区域名称

3. 单击与第一条记录同行的空白单元格（例如 C2），用于显示重叠结果，并输入以下公式：

=SUMPRODUCT((A2<enddate)*(B2>=startdate))>1

将 A2 替换为包含当前记录开始日期的单元格，将 B2 替换为其结束日期。enddate 和 startdate 使用您定义的名称。此公式用于检查当前时间段是否与列表中的其他时间段重叠。按 Enter 后，向下拖动填充柄即可应用到所有需要检查的行。对于每一行，TRUE 表示该范围至少与另一个范围重叠，否则表示未发现重叠。

使用公式检查相对日期范围是否与其他范围重叠

请确保 startdate 和 enddate 引用的是按整列排序、包含起始值和结束值的数据。如果您的列位置不同，或所选范围包含标题，请相应调整单元格引用。

重要提示与故障排除：

  • 如果出现 #VALUE！ 错误，请检查单元格名称和引用是否正确，并确保日期列中不含文本或格式错误的日期/时间数据。
  • 此方法已考虑时间段存在非完全互斥的重叠情形。通常，仅在端点处相接的时间段（即一个时间段的结束日期恰好等于另一个时间段的开始日期）不视为重叠，但您可通过调整公式中的不等式来灵活更改这一判定规则。
  • 对于包含小时/分钟的时间范围，只要单元格统一设置为时间/日期格式，该公式的工作方式就与处理日期时完全相同。

蓝色右箭头气泡 VBA 代码 —— 针对大型数据集或需生成报告自动检测重叠的日期/时间范围

如果您经常处理大型数据集，并希望以更自动化的方式识别时间重叠（尤其是在生成汇总报告或一次性标记所有冲突条目时），使用 VBA 可显著简化整个流程。该方法无需手动检查，轻松应对数百乃至数千个时间段，并可自定义高亮显示或列出所有重叠项。

适用场景：推荐给管理大型排程数据库或共享资源的高级用户，以及任何需要生成所有检测到的重叠日志（而非仅用 TRUE/FALSE 标记行）的用户。

潜在缺点：需启用宏，具备一定的 VBA 使用经验，并在首次运行前务必备份数据，以防意外覆盖。

1. 单击开发工具 > Visual Basic，打开 Microsoft Visual Basic for Applications 窗口；然后单击插入 > 模块，并将下方代码粘贴到模块窗口中：

Sub FindOverlappingDateRanges()
    Dim ws As Worksheet
    Dim i As Long, j As Long
    Dim lastRow As Long
    Dim overlapList As String
    Dim msg As String
    Dim Start1, End1, Start2, End2
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Assumes data starts in row 2
    overlapList = ""
    
    For i = 2 To lastRow
        Start1 = ws.Cells(i, 1).Value
        End1 = ws.Cells(i, 2).Value
        
        If Start1 <> "" And End1 <> "" Then
            For j = 2 To lastRow
                If i <> j Then
                    Start2 = ws.Cells(j, 1).Value
                    End2 = ws.Cells(j, 2).Value
                    
                    If Start2 <> "" And End2 <> "" Then
                        If Start1 < End2 And End1 > Start2 Then
                            overlapList = overlapList & "Row " & i & " overlaps with Row " & j & vbCrLf
                        End If
                    End If
                End If
            Next j
        End If
    Next i
    
    If overlapList <> "" Then
        msg = "The following rows have overlapping date/time ranges:" & vbCrLf & overlapList
    Else
        msg = "No overlapping date/time ranges found."
    End If
    
    MsgBox msg, vbInformation, "KutoolsforExcel"
End Sub

2. 输入代码后，单击运行或按 Enter 即可执行代码。该宏将扫描 A 列（起始时间）和 B 列（结束时间）中的日期范围对，并报告所有发现的重叠项，随后弹出消息框列出所有存在冲突的行，便于您快速审核或深入调查。

故障排除：

  • 请确保起始时间位于 A 列、结束日期位于 B 列，数据从第 2 行开始（第 1 行为标题行）。若您的数据结构不同，请相应调整范围。
  • 所有单元格必须包含有效的日期/时间值，且比较范围内不得存在空白单元格。
  • 在运行或修改 VBA 代码前，请务必备份重要文件，以防数据丢失。

提示：您可以增强 VBA 代码，直接在工作表中标记重叠项——例如为整行着色，或将结果写入相邻列！

蓝色右箭头气泡使用条件格式 —— 在工作表中直接高亮显示重叠范围，便于快速识别

使用条件格式是一种高效实用的方法，能直观地在电子表格中标记出重叠的日期或时间区间。该方案尤其适用于繁忙的日程安排、甘特图或活动时间线，让您一眼识别存在冲突的记录。

最适合：希望即时获得工作表反馈或颜色提示的用户，无需在每一行输入公式或运行代码。非常适合交互式数据检查与演示！

局限性：处理大型数据集时可能导致响应变慢；虽然重叠项会被高亮显示，但不会生成详细的配对信息及统计数量。

应用方法：

  1. 选择开始日期范围（例如 ）A2:A100）和结束日期范围（B2:B100），或者如果两个范围相邻，也可同时选中这两列。
  2. 在开始选项卡中，单击使用条件格式 > 新建规则。
  3. 选择使用公式确定要设置格式的单元格。
  4. 将以下公式输入公式框中（假设您的选择从第 2 行开始）：
    =SUMPRODUCT(($A2<$B$2:$B$100)*($B2>$A$2:$A$100))>1
  5. 单击格式……，选择一种填充颜色以突出显示重叠范围，然后单击确定即可应用设置。

应用规则后，所选范围内与其他区间重叠的行将自动高亮显示，让您无需逐条查看即可轻松发现潜在问题。

提示：请根据您的实际数据区域调整 $A$2:$A$100 和 $B$2:$B$100，并确保引用与所选范围的首行对齐。

注意事项：如果您只想高亮显示两列中的某一列（例如仅高亮“开始日期”），仍需使用相应的公式逻辑。请根据具体业务需求，决定是否将端点接触视为重叠。

最佳办公效率工具

🤖KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行   |  生成代码|  创建自定义公式  |  数据分析及生成图表|  调用 Kutools Functions……
热门功能：查找、高亮或标记重复项   |  删除空白行   |  合并列或单元格且不丢失数据   |  不使用公式的四舍五入……
高级 LOOKUP：多条件 VLookup  |  多值 VLookup  |   跨多工作表 VLookup   |   模糊查找……
高级下拉列表：快速创建下拉列表   |  级联下拉列表   |  多选下拉列表……
列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列……
特色功能：网格聚焦   |  设计视图   |增强编辑栏   | 工作簿和表管理器   |  资源库（自动文本）|  日期提取   |  汇总工作表  |  加密/解密单元格   | 按列表发送邮件   |  超级筛选   |   特殊筛选（筛选粗体单元格/斜体/删除线……） ......
精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）|   50+ 图表 类型（甘特图，……）|   40+ 实用公式（基于生日计算年龄，……）|   19 插入工具（插入二维码，从路径插入图片，……）|   12 转换工具（小写金额转大写，汇率转换，……）|   7 合并和拆分工具（高级合并行，分割单元格，……）|……更多
在您的首选语言中使用 Kutools – 支持英语、西班牙语、德语、法语、中文及 40+ 种其他语言！

使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……

Office Tab 为 Office 带来标签式界面，让您的工作更轻松

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
  • 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
  • 将您的工作效率提升 50%，每天减少数百次鼠标点击！

所有 Kutools 插件，一个安装程序

Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。

ExcelWordOutlookTabsPowerPoint
  • 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
  • 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
  • 协同效果更佳— 在多个 Office 应用中实现高效协同
  • 30 天全功能试用— 无需注册，无需信用卡
  • 超值之选— 比单独购买插件更省钱