☰

如何取得一對日期與任意年份的 6 月至 11 月期間重疊的月數？

tag-icon • tag-icon microsoft-excel worksheet-function
如何取得一對日期與任意年份的 6 月至 11 月期間重疊的月數？

Range1 為 6/1/2021-11/30/2021，Range2 為 8/15/2021-3/1/2022（Range2 可以是任何其他日期範圍）。我想看看 Range2 與 Range1 有多少個月相交。答案應該是 4，因為 11 月、10 月、9 月和 8 月的部分時間與 Range2 相交。在我的問題中，即使是 3 個月 3 天，我也會說整整 4 個月。但是，我並沒有將該月的第一天算作一個完整的月份。

例如，Range1 為6/1/2021 - 11/1/2021，Range2 為2/1/2021 - 10/1/2021，則答案應為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,E80),0,E80),0,E80),0,E80),0,E80) )),""))))

它工作完美，但我現在需要它來比較一組日期，例如 2/1/2021 - 10/1/2021 和任何一年的任何 6 月至 11 月期間。在現在使用的公式中，它不適用於查找 2/1/2022 - 7/1/2022 中的重疊月份，因為 2/1/2022 - 7/1/2022 不與 6/1/2021 重疊- 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'))。

相關內容