Пересечение интервалов дат
Одна из типичных задач для пользователя Microsoft Excel. Имеем два диапазона дат вида «начало-конец». Задача состоит в том, чтобы определить пересекаются ли эти диапазоны и, если да, то на сколько дней.
Пересекаются или нет?
Начнем с решения вопроса о том, есть ли пересечение интервалов в принципе? Предположим, что у нас есть таблица рабочих смен сотрудников вот такого вида:
Хорошо видно, что рабочие смены Ярослава и Елены пересекаются, но как это вычислить, не прибегая к построению календарного графика и визуальному контролю? Нам поможет функция СУММПРОИЗВ (SUMPRODUCT).
Вставим в нашу таблицу еще один столбец с формулой, которая выдает логическое значение ИСТИНА в случае пересечения дат:
На сколько дней пересечение?
Если принципиально не просто понимать - пересекаются наши интервалы или нет, а точно знать сколько именно дней попадает в пересечение, то задача усложняется. Рассуждая логически, необходимо "прокачать" аж 3 разных ситуации в одной формуле:
- интервалы не пересекаются
- один из интервалов полностью поглощает другой
- интервалы пересекаются частично
На самом деле все можно сделать красиво с помощью функции МЕДИАНА (MEDIAN) из категории Статистические.
Если условно обозначить начало первого интервала за Н1, а конец за К1, и начало второго за Н2 и конец за К2, то в общем виде наша формула может быть записана как:
=МЕДИАНА(Н1;К1+1;К2+1)-МЕДИАНА(Н1;К1+1;Н2)
Компактно и изящно, не правда ли? ;)
Ссылки по теме
- Как Excel на самом деле работает с датами? Как вычислить количество календарных или рабочих дней между датами?
- Как построить календарный график (отпусков, тренингов, смен...) в Excel с помощью условного форматирования?
- Проверка одного или нескольких условий с помощью функций ЕСЛИ (IF)
=МИН(C6;C4)+1-МАКС(B6;B4)
Т.е. когда диапазоны не пересекаются
Например, Владимир и Федор работали по одной заявке с 10:00 до 12:00 вместе. Человеко*часов получится 4 (оплачиваемые продуктивные часы), а ремонтируемое оборудование находилось в ремонте всего 2 (астрономических) часа. Таблица учитывает возможность работы многих работников, съедает пересечения, разрывы в периодах работы, переходы с даты на дату.
За основу взята идея медианы, но саму формулу медианы не получилось применить к массивам. Поэтому, как и написал Николай, получилась монстр-формула.
А не подскажите как решить похожую задачу: необходимо узнать общее время работы сотрудников за исключением дней пересечения
Например, справа от столбца В добавить еще один столбец, в котором будет отображаться "чистое" (без пересечений) время работы: Ярослав работал - 6 дней, а Елена 1 день самостоятельно и 3 дня вместе с Ярославом: всего на работу было потрачено 7 дней
Спасибо!
Если бы я с таким столкнулся - писал бы макрос, скорее.
Помогите пожалуйста. Есть календарь в который попадают задачи сотруднику из отдельного листа. Календарь состоит из двух листов, текущая и будущая неделя. Как сделать так чтоб задача повторялась до конечного срока исполнения указанного в листе задачи.
=СУММПРОИЗВ(($A$2:$A$8=A2)*(H2<=$I$2:$I$8)*(I2>=$H$2:$H$8))
где А - номер соцкарты, H - начало поступления на работу, I - дата ухода с работы
В следующих 2х столбцах надо прописать начало и конец совпадения.
Если совпадений 2, то при помощи Сводной таблицы нахожу начало и конец периодов дат совпадений, а если больше 2, то минимум и максимум выдают не стыковочнве даты. Может есть другой вариант? Спасибо.
Количество пересекающихся дней это здорово, а как найти конкретные даты пересечений?