Пересечение интервалов дат

Одна из типичных задач для пользователя Microsoft Excel. Имеем два диапазона дат вида «начало-конец». Задача состоит в том, чтобы определить пересекаются ли эти диапазоны и, если да, то на сколько дней.

Пересекаются или нет?

Начнем с решения вопроса о том, есть ли пересечение интервалов в принципе? Предположим, что у нас есть таблица рабочих смен сотрудников вот такого вида:

date-intersection1.png

Хорошо видно, что рабочие смены Ярослава и Елены пересекаются, но как это вычислить, не прибегая к построению календарного графика и визуальному контролю? Нам поможет функция СУММПРОИЗВ (SUMPRODUCT).

Вставим в нашу таблицу еще один столбец с формулой, которая выдает логическое значение ИСТИНА в случае пересечения дат:

date-intersection2.png

На сколько дней пересечение?

Если принципиально не просто понимать - пересекаются наши интервалы или нет, а точно знать сколько именно дней попадает в пересечение, то задача усложняется. Рассуждая логически, необходимо "прокачать" аж 3 разных ситуации в одной формуле:

  • интервалы не пересекаются
  • один из интервалов полностью поглощает другой
  • интервалы пересекаются частично
Периодически реализацию подобного подхода я вижу у других пользователей с помощью кучи вложенных друг в друга функций ЕСЛИ и т.п.

На самом деле все можно сделать красиво с помощью функции МЕДИАНА (MEDIAN) из категории Статистические.

date-intersection3.png

Если условно обозначить начало первого интервала за Н1, а конец за К1, и начало второго за Н2 и конец за К2, то в общем виде наша формула может быть записана как:

=МЕДИАНА(Н1;К1+1;К2+1)-МЕДИАНА(Н1;К1+1;Н2)

Компактно и изящно, не правда ли? ;)

Ссылки по теме



MCH
21.12.2015 10:33:28
Вариант подсчета кол-ва дней пересечения (тоже достаточно компактно):
=МАКС(МИН(C6+1;C4+1)-МАКС(B6;B4);)
23.03.2016 18:52:48
С МСН практически бесполезно соревноваться в компактности формул :)
MCH
23.03.2016 18:58:09
Максим, я ведь не запрещаю соревноваться. Дерзайте
24.06.2016 16:13:48
так, кажется тоже работает
=МАКС(МИН(C6;C4)-МАКС(B6;B4)+1;)
21.05.2019 13:00:29
Можно еще компактнее:

=МИН(C6;C4)+1-МАКС(B6;B4)

:)
MCH
21.05.2019 13:19:33
Что посчитает формула, если МИН(C6;C4)+1 меньше МАКС(B6;B4)?
Т.е. когда диапазоны не пересекаются
21.05.2019 13:43:06
Да, отрицательное число :) Тогда как ограничитель всё нужен МАКС, который, собственно и стоит у вас.
13.11.2020 14:23:37
А как будет выглядеть формула если будет еще и третий интервал?
19.12.2023 04:23:05
Предположим, что бригада работников (несколько человек) выполняет работы по заявкам. В одной заявке могут быть заняты 1 и более работников. Приложенный файл подсчитывает, сколько чистого (астрономического) времени заняло выполнение заявки.
Например, Владимир и Федор работали по одной заявке с 10:00 до 12:00 вместе. Человеко*часов получится 4 (оплачиваемые продуктивные часы), а ремонтируемое оборудование находилось в ремонте всего 2 (астрономических) часа. Таблица учитывает возможность работы многих работников, съедает пересечения, разрывы в периодах работы, переходы с даты на дату.
Сумма пересечении интервалов временных дат (planetaexcel.ru)
За основу взята идея медианы, но саму формулу медианы не получилось применить к массивам. Поэтому, как и написал Николай, получилась монстр-формула.

https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=150512&TITLE_SEO=150512-summa-peresechenii-intervalov-vremennykh-dat&MID=1280232&result=reply#message1280232
07.04.2016 13:34:31
Здравствуйте!

А не подскажите как решить похожую задачу: необходимо узнать общее время работы сотрудников за исключением дней пересечения
Например, справа от столбца В добавить еще один столбец, в котором будет отображаться "чистое" (без пересечений) время работы: Ярослав работал - 6 дней, а Елена 1 день самостоятельно и 3 дня вместе с Ярославом: всего на работу было потрачено 7 дней

Спасибо!
26.06.2016 09:22:13
А если посчитать общее время (конец - начало) и вычесть из этого длительность пересечения?
21.09.2016 16:54:03
Уважаемые участники, подскажите пожалуйста, как в первую формулу из примера с "СУММПРОИЗВ" завести критерий отбора по имени, т.е. если к примеру в списке два одинаковых имени, нужно чтобы показывало наложение именно по этим двум именам.
27.12.2016 11:33:13
здравствуйте, а подскажите, пожалуйста, если мне нужно понять, с каким сотрудником происходит пересечение, то что лучше применить? чтобы, например, в столбике напротив Ярослава было имя - Елена, и наоборот.
04.01.2017 09:38:13
Это не просто :) А если пересечение не с одним сотрудником, а с тремя?
04.01.2017 14:05:57
именно это и нужно:) это вообще возможно?
04.01.2017 16:15:54
Предполагаю, что возможно написать такую формулу, но это будет ад и ужас.
Если бы я с таким столкнулся - писал бы макрос, скорее.
01.05.2017 08:10:05
Добрый день.
Помогите пожалуйста. Есть календарь в который попадают задачи сотруднику из отдельного листа. Календарь состоит из двух листов, текущая и будущая неделя. Как сделать так чтоб задача повторялась до конечного срока исполнения указанного в листе задачи. http://www.fayloobmennik.net/6971482
27.08.2017 04:34:05
Как должна выглядеть формула если интервалов больше двух?
=МЕДИАНА(Н1;К1+1;К2+1)-МЕДИАНА(Н1;К1+1;Н2)
У меня есть пару тысяч строк временных интервалов использования двух единиц техники и мне необходимо определить сколько времени техника работала одновременно. Что посоветуете в таком случае?
01.10.2020 11:57:27
С течением времени вопрос не потерял актуальности. Существует ли возможность посчитать общее время пересечения более двух объектов?
14.11.2017 09:51:05
добрый день. помогите пожалуйста, не нашел более подходящей темы. мне необходимо найти не пересечение, а наоборот количество дней между периодами. имеется таблица пациентов с периодами лечения, задача по каждому пациенту посчитать количество дней между соседними периодами лечения. периоды по одному пациенту не всегда стоят в порядке возрастания. и еще у каждого пациента не всегда два периода, может быть и три и больше.
22.08.2018 12:05:32
Добрый день. Помогите пожалуйста. Мне надо найти совпадения в периодах работы одного и того же человека на разных предприятиях. Если он одновременно работал в 2 и более местах, тогда необходимо показать начало и конец совпадения по датам. Первую часть я получила, а вторая не получается.

=СУММПРОИЗВ(($A$2:$A$8=A2)*(H2<=$I$2:$I$8)*(I2>=$H$2:$H$8))

где А - номер соцкарты, H - начало поступления на работу, I - дата ухода с работы
В следующих 2х столбцах надо прописать начало и конец совпадения.
Если совпадений 2, то при помощи Сводной таблицы нахожу начало и конец периодов дат совпадений, а если больше 2, то минимум и максимум выдают не стыковочнве даты. Может есть другой вариант?  Спасибо.
18.02.2019 23:46:59
Подскажите, с помощью какой формулы в разделе Условное форматирование добавить на график закрашенные пересекающиеся данные - подсчитанные с помощью медианы
26.05.2020 11:29:30
Попробуйте такую формулу =И(E$2>=$B6;E$2<=$C6)
01.12.2022 14:23:28
а как посчитать сколько пересекающихся отрезков за на каждую дату?
Наверх