Fórmula genérica
=(NETWORKDAYS(start,end)-1)*(upper-lower) +IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upper,lower)
Resumo
Para calcular o total de horas de trabalho entre duas datas e horas, você pode usar uma fórmula baseada na função DIAS DE REDE. No exemplo mostrado, E5 contém esta fórmula:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),upper,lower)
onde "inferior" é o intervalo nomeado H5 e "superior" é o intervalo nomeado H6.
Nota: este exemplo foi inspirado por um desafio de fórmula em Chandoo e uma solução mais completa fornecida pelo mestre de fórmulas Barry Houdini no fórum MrExcel.
Explicação
Esta fórmula calcula o total de horas de trabalho entre duas datas e horários, que ocorrem entre um horário "inferior" e um "superior". No exemplo mostrado, a hora inferior é 9h00 e a hora superior é 17h00. Eles aparecem na fórmula como os intervalos nomeados "inferior" e "superior".
A lógica da fórmula é calcular todas as horas de trabalho possíveis entre as datas de início e término, inclusive, em seguida, retornar todas as horas na data de início que ocorrerem entre a hora de início e a hora inferior, e quaisquer horas na data de término que ocorrerem entre o tempo do fim e o tempo superior.
A função NETWORKDAYS trata da exclusão de finais de semana e feriados (quando fornecidos como um intervalo de datas). Você pode alternar para NETWORKDAYS.INTL se sua programação tiver dias úteis não padrão.
Saída de formatação
O resultado é um número que representa o total de horas. Como todos os horários do Excel, você precisará formatar a saída com um formato de número adequado. No exemplo mostrado, estamos usando:
(h):mm
Os colchetes impedem a rolagem do Excel quando as horas são maiores que 24. Em outras palavras, eles possibilitam exibir horas maiores que 24. Se você precisar de um valor decimal para horas, pode multiplicar o resultado por 24 e formatar como um número regular.
Versão simples
Se os horários de início e término sempre ocorrerão entre os horários inferior e superior, você pode usar uma versão mais simples desta fórmula:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Sem hora de início e hora de término
Para calcular o total de horas de trabalho entre duas datas, presumindo que todos os dias sejam dias inteiros, você pode usar uma fórmula ainda mais simples:
=NETWORKDAYS(start,end,holidays)*hours
Veja a explicação aqui para detalhes.
