количество дней, перекрывающихся в массиве дат с фиксированным диапазоном дат

Question

У меня есть список задач с датами начала и окончания, а затем я пытаюсь создать количество активных дней в неделю каждую неделю в течение нескольких недель.

Снимок экрана Excel проблемы

Как я пытался показать на снимке экрана, используя =MAX(MIN(end1,end2)-MAX(start1,start2)+1,0) я могу получить перекрытие для одного набора дат против другого и затем суммировать по неделю, чтобы получить ответ за каждую неделю.

Это решение будет заканчиваться довольно большой матрицей вычислений, и я убежден, что должен быть более прямой путь.

Я думаю, массив формул? но изо всех сил.

Answer 1

Вы можете использовать эту формулу массива в G15

=SUM(TEXT(IF(C$3:C$6>F15+6,F15+6,C$3:C$6)-IF(B$3:B$6<F15,F15,B$3:B$6)+1,"0;\0")+0)

подтвердите нажатием CTRL+SHIFT+ENTER и скопируйте вниз

.... или версия без массива с COUNTIFS как это

=SUM(COUNTIFS(B:B,"<="&F15+{0,1,2,3,4,5,6},C:C,">="&F15+{0,1,2,3,4,5,6}))

В первом предложении, поскольку вы не можете легко использовать MIN и MAX в формуле этого типа, функции IF достигают одного и того же. Когда даты не перекрываются, это приводит к отрицательным числам, поэтому функция TEXT используется для преобразования их в нули

В версии COUNTIFS каждая дата недели проверяется, чтобы определить, попадает ли она в какой-либо из диапазонов дат.

Я ожидаю идентичные результаты для обеих формул

Версии обоих из них предложены здесь