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

Одна из типичных задач для пользователя 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;)
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)
У меня есть пару тысяч строк временных интервалов использования двух единиц техники и мне необходимо определить сколько времени техника работала одновременно. Что посоветуете в таком случае?
14.11.2017 09:51:05
добрый день. помогите пожалуйста, не нашел более подходящей темы. мне необходимо найти не пересечение, а наоборот количество дней между периодами. имеется таблица пациентов с периодами лечения, задача по каждому пациенту посчитать количество дней между соседними периодами лечения. периоды по одному пациенту не всегда стоят в порядке возрастания. и еще у каждого пациента не всегда два периода, может быть и три и больше.