Tempo decorrido em horas entre várias datas no Excel

Question

Estou tentando encontrar uma fórmula que trabalhe fora do horário comercial entre duas datas no Excel.

O horário comercial começa às 09:00 e termina às 17:00 e não inclui finais de semana.

A planilha atualmente é apresentada da seguinte maneira:

A1: Start Time       -       B1: End Time   -       C1: Total Business hours
A2: 2016-01-04 10:31:17   -  B2: 2016-01-06 10:02:14

Existe uma fórmula que eu possa usar para calcular a diferença de horário (horário comercial) e exibi-la em C2?

    

Answer 1

Supondo que as datas estão no mesmo ano e negligenciando um pequeno erro de arredondamento, encontrei uma solução viável:

Primeiro explicarei e adicionarei a fórmula abaixo.

Eu dividi a solução em várias instâncias, se

IF 1: DATAS NA MESMA SEMANA

IF(WEEKNUMBER(A2)=WEEKNUMBER(B2);

SE 1.1: DATAS NO MESMO DIA

IF(DAY(A2)=DAY(B2));

Pegue as horas da data final menos as horas da data de início = resultado

(B2-A2)*24;

IF 1.2: DATAS NÃO NO MESMO DIA

Pegue o dia da data final menos o dia da data de início (= os dias entre as duas datas) e multiplique por 8 (= 17-9) e adicione a diferença em horas. A função MOD é usada para cortar os dias das datas e permanecer apenas com o tempo.

(DAY(B2)-DAY(A2))*(17-9)+(MOD(B2,1)-MOD(A2,1))*24);

SE 2: DATAS NÃO NA MESMA SEMANA

Eu calculo o número de horas das semanas no meio, então eu adiciono as horas da data de início até o final da semana de início (por sorte o ano começou em uma sexta para que eu pudesse fazer weeknumber (data inicial) * 7 e finalmente adiciono o número de horas desde o início da semana até a data final.

(WEEKNUMBER(B2)-WEEKNUMMER(A2)-1)*5*(17-9)+(WEEKNUMBER(A2)*7-QUOTIENT(A2;1))*(17-9)+17+(QUOTIENT(B2;1)-(WEEKNUMBER(B2)*7-4))*(17-9)+(MOD(B2;1)-MOD(A2;1))*24-9)

No final, você só precisa reunir todos esses códigos e calcular o horário comercial entre as datas.

    

Answer 2

Aqui está outra fórmula que usa a função NETWORKDAYS , que permite especificar feriados ou, se você tiver uma versão posterior do Excel, pode substituir NETWORKDAYS.INTL para especificar quais dias são seu fim de semana.

• Conte 8 horas para cada dia útil
• Subtraia o tempo entre 9:00 e o real start_time
• Subtraia o tempo entre o end_time real e as 17:00 nessa data:

=NETWORKDAYS([@[Start Time]],[@[End Time]])*8/24-
([@[Start Time]]-(INT([@[Start Time]])+9/24))-
(INT([@[End Time]])+17/24-[@[End Time]])

Como ajustamos para representar o tempo como frações de um dia, os resultados estarão no formato de data / hora do Excel e podem ser formatados para exibir segundos, se você desejar.