Fórmula genérica
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Resumo
Para calcular as horas de trabalho entre duas datas com uma programação personalizada, você pode usar uma fórmula baseada nas funções WEEKDAY e SUMPRODUCT, com ajuda de ROW, INDIRECT e MID. No exemplo mostrado, a fórmula em F8 é:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Que retorna 36 horas, com base em uma programação personalizada em que 8 horas são trabalhadas de segunda a sexta, 4 horas são trabalhadas no sábado e segunda-feira, 3 de setembro, é feriado. Os feriados são fornecidos no intervalo nomeado G6: G8. O plano de trabalho é inserido como uma sequência de texto na coluna D e pode ser alterado conforme desejado.
Nota: Esta é uma fórmula de matriz que deve ser inserida com Control + Shift + Enter. Se você tiver um dia de trabalho padrão de 8 horas, esta fórmula é mais simples.
Explicação
Basicamente, essa fórmula usa a função WEEKDAY para descobrir o dia da semana (ou seja, segunda, terça, etc.) para cada dia entre as duas datas fornecidas. WEEKDAY retorna um número entre 1 e 7. Com as configurações padrão, Domingo = 1 e Sábado = 7.
O truque para essa fórmula é reunir uma matriz de datas que você pode inserir na função WEEKDAY. Isso é feito com ROW com INDIRETO:
ROW(INDIRECT(B6&":"&C6))
ROW interpreta as datas concatenadas como números de linha e retorna uma matriz como esta:
(43346;43347;43348;43349;43350;43351;43352)
Cada número na matriz representa uma data. A função WEEKDAY avalia a matriz e retorna uma matriz de valores de dia da semana:
(2;3;4;5;6;7;1)
Esses números correspondem ao dia da semana de cada data. Eles são fornecidos para a função MID como o argumento do número inicial, junto com o valor em D6, "0888884" para texto:
MID("0888884",(2;3;4;5;6;7;1),1)
Como estamos dando ao MID uma matriz de números iniciais, ele retorna uma matriz de resultados como este:
("8";"8";"8";"8";"8";"4";"0")
Esses valores correspondem às horas trabalhadas em cada dia desde a data de início até a data de término. Observe que os valores nesta matriz são texto, não números. Para converter em números reais, multiplicamos por uma segunda matriz criada para gerenciar feriados, conforme explicado abaixo. A operação matemática força o texto a valores numéricos.
Feriados
Para lidar com feriados, usamos ISNA, MATCH e o intervalo nomeado "feriados" como este:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Esta expressão usa MATCH para localizar datas que estão no intervalo nomeado de feriados usando a mesma matriz de datas gerada acima com INDIRETO e ROW. MATCH retorna um número quando os feriados são encontrados e o erro # N / A quando não são encontrados. A função ISNA "inverte" os resultados de forma que TRUE represente feriados e FALSE represente não feriados. ISNA retorna uma matriz ou resultados como este:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Finalmente, os dois arrays são multiplicados um pelo outro dentro do SUMPRODUCT. A operação matemática força TRUE e FALSE para 1 e zero, e os valores de texto na primeira matriz para valores numéricos (como explicado acima), então no final temos:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Após a multiplicação, temos uma única matriz dentro de SUMPRODUCT contendo todas as horas de trabalho no intervalo de datas:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT então soma todos os itens na matriz e retorna um resultado de 36.
