如何在Google Sheets或App Script中检查同一账户的日期重叠

一、公式实现（无需排序） #

假设数据结构如下：

• A列：账户名
• B列：开始日期
• C列：结束日期
• D列：Check结果列（需生成）

在D2单元格输入以下公式，然后下拉填充至所有行：

=IF(COUNTIFS(A:A,A2,B:B,"<"&C2,C:C,">"&B2)-1>0,"ERROR","OK")

公式逻辑说明： #

• COUNTIFS(A:A,A2,B:B,"<"&C2,C:C,">"&B2)：统计同账户下，满足「开始日期 < 当前行结束日期」且「结束日期 > 当前行开始日期」的行数
• 减1是因为COUNTIFS会把当前行本身统计进去，若剩余结果>0，说明存在其他重叠的日期段
• 最终通过IF判断，返回ERROR（有重叠）或OK（无重叠）

二、App Script自动化实现 #

如果数据量较大，公式性能不足，可使用脚本批量检测：

function checkDateOverlap() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headerRow = 1; // 第一行为表头
  const accountCol = 0; // 账户列（A列，索引从0开始）
  const startDateCol = 1; // 开始日期列（B列）
  const endDateCol = 2; // 结束日期列（C列）
  const checkCol = 3; // Check结果列（D列）

  // 按账户分组存储所有日期段
  const accountGroups = new Map();
  for (let i = headerRow; i < data.length; i++) {
    const account = data[i][accountCol];
    if (!accountGroups.has(account)) accountGroups.set(account, []);
    accountGroups.get(account).push({
      rowIndex: i,
      start: data[i][startDateCol],
      end: data[i][endDateCol]
    });
  }

  // 初始化结果数组，默认全为"OK"
  const resultArray = Array.from({ length: data.length }, () => ["OK"]);

  // 遍历每个账户的日期段，检查重叠
  accountGroups.forEach(segments => {
    segments.forEach((current, idx) => {
      let hasOverlap = false;
      // 和同账户的其他日期段逐一比较
      for (let j = 0; j < segments.length; j++) {
        if (idx === j) continue; // 跳过自身
        const compare = segments[j];
        // 日期重叠判断条件：current.start < compare.end 且 current.end > compare.start
        if (current.start < compare.end && current.end > compare.start) {
          hasOverlap = true;
          break;
        }
      }
      if (hasOverlap) resultArray[current.rowIndex] = ["ERROR"];
    });
  });

  // 将结果写入Check列
  sheet.getRange(headerRow + 1, checkCol + 1, resultArray.length - headerRow, 1)
       .setValues(resultArray.slice(headerRow));
}

使用说明： #

1. 打开目标Google Sheets，点击「扩展程序」>「Apps Script」
2. 粘贴上述代码，保存项目（命名如DateOverlapChecker）
3. 点击运行按钮，授权脚本访问权限
4. 运行完成后，D列自动生成ERROR/OK标记
5. 可设置时间驱动触发器或编辑触发器，实现自动检测更新

注意事项： #

• 确保B、C列的日期为Google Sheets可识别的日期格式（避免文本格式）
• 若表头行不是第1行，需修改代码中的headerRow值

内容的提问来源于stack exchange，提问作者houjicha