Общая формула
=SUMPRODUCT((start_date=start_dates))>1
Резюме
Чтобы выделить ячейки, в которых даты перекрываются, можно использовать условное форматирование с формулой, основанной на функции СУММПРОИЗВ. В показанном примере формула на юге E6:
=SUMPRODUCT(($C6=$C$5:$C$9))>1
Это та же формула, которая используется для выделения целых строк в таблице с помощью правила условного форматирования на основе формул.
Объяснение
Задумайтесь на мгновение, как работают перекрывающиеся даты. Чтобы проект совпадал с датами других проектов, должны выполняться два условия:
1. Дата начала должна быть меньше или равна (<=) хотя бы одной другой дате окончания и списку.
2. Дата окончания проекта должна быть больше или равна (> =) хотя бы одной другой дате начала в списке.
Если оба этих условия верны, даты проекта должны совпадать с датами другого проекта в этом списке.
Функция СУММПРОИЗВ идеально подходит для такого рода тестов, поскольку она элегантно обрабатывает сравнения массивов.
Чтобы проверить дату начала проекта и все даты окончания, мы используем это выражение:
($C6<=$D$5:$D$9)
Чтобы проверить дату окончания проекта на все даты окончания, мы используем это выражение:
($D6>=$C$5:$C$9)
Результирующие массивы значений TRUE FALSE умножаются друг на друга внутри SUMPRODUCT. Это автоматически переводит результаты ИСТИНА и ЛОЖЬ в единицы и нули, поэтому формула решается следующим образом:
=SUMPRODUCT((0;1;1;1;1)*(1;1;1;0;0))>1 =SUMPRODUCT((0;1;1;0;0))>1 =TRUE
