☰

Как узнать количество месяцев, которые перекрываются между парой дат и любым периодом с июня по ноябрь любого года?

tag-icon • tag-icon microsoft-excel worksheet-function
Как узнать количество месяцев, которые перекрываются между парой дат и любым периодом с июня по ноябрь любого года?

Range1 — это 6/1/2021-11/30/2021, а Range2 — 8/15/2021-3/1/2022 (Range2 может быть любым другим диапазоном дат). Я хочу узнать, сколько месяцев Range2 пересекаются с Range1. Ответ должен быть 4, потому что Nov, Oct, Sep и часть Aug пересекают Range2. И в моей задаче, даже если бы это было 3 месяца и 3 дня, я бы сказал 4 полных месяца. Однако я не считаю первый день месяца полным месяцем.

Например, Range1 — это 6/1/2021 — 11/1/2021, а Range2 — это 2/1/2021 — 10/1/2021, тогда ответ должен быть 4, потому что июнь, июль, август и сентябрь пересекаются с Rang1. Обратите внимание, что я не учитываю 10/1/2021, поэтому ответ не может быть 5.

Это уже было решено полезным пользователемСкотт Крейнер

Формула, которую мы использовали, выглядит так: =COUNT(UNIQUE(FILTER(MONTH(SEQUENCE(P60-P59,,P59)),ISNUMBER(MATCH(SEQUENCE(P60-P59,,P59),SEQUENCE(E81-E80,,E80),0)),""))))

Он работает безупречно, но мне нужно, чтобы он работал сейчас для сравнения набора дат, например, 2/1/2021 - 10/1/2021 и ЛЮБОГО периода с июня по ноябрь ЛЮБОГО года. В формуле, используемой прямо сейчас, он не работает для поиска перекрывающихся месяцев в 2/1/2022 - 7/1/2022, поскольку 2/1/2022 - 7/1/2022 не перекрывается с 6/1/2021 - 11/30/2021. Могу ли я найти количество совпадающих месяцев между любым набором дат и любым периодом 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=01.01.2020, то a'=1441
  4. Чтобы игнорировать 1-й день каждого месяца, добавьте -(DAY(a)<2)к приведенной выше формуле
  5. Если предположить, что b' > a' и d' > c', то перекрытие равно MIN(d',b')-MAX(c',a')+1
  6. Если вышеприведенное отрицательно, то совпадения нет.

Обратите внимание: для упрощения математики я использую алгебраические переменные вместо адресов ячеек.

P.S. Если вы хотите поизвращаться в 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')).

Связанный контент