☰

如何获取一对日期与任意一年的 6 月至 11 月期间重叠的月份数？

tag-icon • tag-icon microsoft-excel worksheet-function
如何获取一对日期与任意一年的 6 月至 11 月期间重叠的月份数？

Range1 是 2021 年 6 月 1 日-2021 年 11 月 30 日，Range2 是 2021 年 8 月 15 日-2022 年 3 月 1 日（Range2 可以是任何其他日期范围）。我想看看 Range2 有多少个月与 Range1 相交。答案应该是 4，因为 11 月、10 月、9 月和 8 月的一部分与 Range2 相交。在我的问题中，即使是 3 个月零 3 天，我也会说 4 个完整的月份。但是，我没有将月份的第一天算作一个完整的月份。

例如，Range1 是 2021 年 6 月 1 日 - 2021 年 11 月 1 日，Range2 是 2021 年 2 月 1 日 - 2021 年 10 月 1 日，那么答案应该是 4，因为六月、七月、八月和九月与 Rang1 相交。请注意，我没有计算 2021 年 10 月 1 日，所以答案不可能是 5。

这个问题已经被一个热心的用户解决了斯科特·克雷纳

我们使用的公式是 =COUNT(UNIQUE(FILTER(MONTH(SEQUENCE(P60-P59,,P59)),ISNUMBER(MATCH(SEQUENCE(P60-P59,,P59),SEQUENCE(E81-E80,,E80),0)),""))))

它运行完美，但我现在需要它来比较一组日期，例如 2021 年 2 月 1 日 - 2021 年 10 月 1 日和任何一年的任何 6 月至 11 月期间。在目前使用的公式中，它不适用于查找 2022 年 2 月 1 日 - 2022 年 7 月 1 日中的重叠月份，因为 2022 年 2 月 1 日 - 2022 年 7 月 1 日与 2021 年 6 月 1 日 - 2021 年 11 月 30 日不重叠。我能否找到任何一组日期和任何一年的任何 6/1/## - 11/30/## 期间之间的匹配月份数？

答案1

我会尝试一下......我找到任何两个日期范围的重叠部分的方法是：

  1. 将日期范围 a - b 和 c - d 转换为自 1900 年以来的月份数
  2. 我们把这些月份计数称为 a' - b' 和 c' - d'
  3. 因此a' =(YEAR(a)-1900)*12 + MONTH(a)，例如 a=1/1/2020 则 a'=1441
  4. 要忽略每个月的第一天，请将-(DAY(a)<2)上述公式添加到
  5. 假设 b' > a' 且 d' > c'，则重叠为 MIN(d',b')-MAX(c',a')+1
  6. 如果上述是否定的，则没有重叠

请注意，我使用代数变量而不是单元格地址来简化数学。

附言：如果您想在 Excel 中有所建树，请使用相交运算符（空格字符）在空白列中相交列范围（表示自 1900 年以来的月份），然后计算空白数，例如=COUNTBLANK(Z1441:Z1446 Z1443:Z1447)将得到 4。这对于多个日期范围非常有用，因为您可以相交任意数量的 Excel 范围。要将月份计数日期范围（a' 到 b'）转换为行范围，请使用，=INDEX(Z:Z,a'):INDEX(Z:Z,b')以便最终重叠计数为=COUNTBLANK(INDEX(Z:Z,a'):INDEX(Z:Z,b') INDEX(Z:Z,c'):INDEX(Z:Z,d'))。

相关内容