Excel: Calculando horas de trabalho entre duas datas

1

Resumo:

Eu sei que a função NETWORKDAYS pode ser usada para encontrar o número de negócios dias (= excluindo fins de semana e feriados) entre duas datas. Mas e se eu quiser saber o número de negócios horas entre, digamos, ontem, 14:00 e hoje 10:00?

- > Eu preciso de uma função NETWORKINGHOURS que não existe (Excel 2003). Em essência, use NETWORKDAYS e adicione dois parâmetros adicionais start of working day e end of working day . Parece simples, mas minhas tentativas de criá-lo pela fórmula rapidamente se tornam muito complexas.

Pergunta:
Como posso fazer meu networkhours funcionar mesmo quando a hora final do dia é anterior à hora do início?

Detalhes:

Eu tenho uma planilha do Excel com duas colunas de data e hora. Quero calcular o número de dias + horas + minutos entre os timestamps, mas só contarei o horário comercial.

A função NETWORKDAYS pode ser usada para encontrar os dias úteis excluindo fins de semana e feriados (subtraindo 2 de seu resultado, porque a função conta o dia de início e o dia de término, independentemente do horário). Por enquanto, tudo bem. Parece com isso ( inspiração daqui ):

=CONCATENATE(NETWORKDAYS($A6;B6;holidays)-1
;"d "
;HOUR(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))
;":"
;IF(MINUTE(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))>9
;MINUTE(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))
;CONCATENATE("0";MINUTE(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1))))))

dá-me resultados muito bons no formato 1d 2:30 assumindo que nomeei células para startofday (08:00), endofday (16:00) e holidays ( coluna com valores de data).

Isso funciona muito bem , exceto que a fórmula é interrompida quando a hora final do dia é anterior à hora do início do dia. Veja meus dados e fórmulas de teste:

networkdays-2 =NETWORKDAYS($A6;B6;holidays)-2
horas =MIN($B$3;MOD(B6;1))-MAX($A$3;MOD(A6;1))
minutos =MINUTE(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))
networkhours veja o bloco de códigos acima

    
por Torben Gundtofte-Bruun 21.01.2011 / 15:19

1 resposta

1

dias úteis inteiros = NETWORKDAYS - 2

tempo trabalhado no primeiro dia = MAX (0, fim de tarde - MAX (hora de início, início de semana))

tempo trabalhado no último dia = MAX (0, MIN (entime, endofday) - startofday)

dias totais de trabalho = dias úteis inteiros + INT ((tempo trabalhado no primeiro dia + tempo trabalhado no último dia) / (final de semana - começo de dia))

tempo extra (após o total de dias) = MOD (tempo trabalhado no primeiro dia + tempo trabalhado no último dia, fim de semana - início de semana)

    
por 21.01.2011 / 15:55