count dias sobrepostos em uma matriz de datas para um período fixo

Question

Eu tenho uma lista de tarefas com datas de início e término associadas e estou tentando criar uma contagem de dias de tarefas ativas a cada semana ao longo de várias semanas.

captura de tela do problema

Como tentei mostrar na captura de tela, usando =MAX(MIN(end1,end2)-MAX(start1,start2)+1,0) , posso obter a sobreposição de um conjunto de datas em relação a outro e, em seguida, somar toda a semana para obter a resposta para cada semana.

Esta solução vai acabar com uma grande matriz de cálculos para chegar lá e estou convencido de que deve haver uma rota mais direta.

Estou pensando em fórmulas de matriz? mas lutando.

    

Answer 1

Você pode usar essa fórmula de matriz em 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;COUNTIFS")+0)

confirme com CTRL + SHIFT + ENTER e copie para baixo

.... ou uma versão sem matriz com =SUM(COUNTIFS(B:B,"<="&F15+{0,1,2,3,4,5,6},C:C,">="&F15+{0,1,2,3,4,5,6})) como esta

MIN

Na primeira sugestão, porque você não pode usar facilmente MAX e IF neste tipo de fórmula, as funções TEXT obtêm a mesma coisa. Quando as datas não se sobrepõem, isso resulta em números negativos, então COUNTIFS function é usada para convertê-los em zeros

Na versão %code% , cada data da semana é testada para ver se ela está em algum dos períodos.

Espero resultados idênticos para ambas as fórmulas

Versões de ambos sugeriram aqui