Horas de parada para cada turno dentro de um intervalo

Professor Leandro S.

Identidade verificada

• CPF verificado
• E-mail verificado

Respondeu há 4 anos

Infelizmente não conheço nenhuma fórmula do Excel que faça exatamente isso ou uma simples intersecção de intervalos de tempo, então temos que fazer uma fórmula ligeiramente extensa mesmo.

No final da resposta eu vou dar a fórmula completa, mas só para que você entenda o raciocínio, vou explicar em detalhes como pensei:

Seja o turno diário o intervalo [a,b] em horas e a duração do apontamento o intervalo [x,y]. A ideia é contar, separadamente, as horas trabalhadas no primeiro dia, nos dias completos e no último dia, para cada turno.

• PRIMEIRO DIA

Podemos enxergar três casos, em que o início do apontamento começa antes de um turno, no meio ou após.

Vemos que é sempre y menos o valor do meio entre a,x e y.

• DIAS INTERMEDIÁRIOS

Esses dias são completos, desde 0h até 24h, então sendo N o número de dias completos, as horas trabalhadas serão N*(y-x).

• ÚLTIMO DIA

Podemos enxergar também três casos, em que o fim do apontamento termina antes de um turno, no meio ou após.

Vemos que é sempre o valor do meio entre b,x e y menos o x.

No fim teremos algo assim:

y - med(a,x,y) + N*(y-x) + med(b,x,y) - x

• FÓRMULAS EXCEL

Agora basta somar essas três parcelas e substituir os valors de x, y, a e b para os valores reais.

Para representar um valor em horas, minutos e segundos, usaremos a função TEMPO([horas]; [minutos]; [segundos]) (TIME em inglês). Portanto, usando o turno B como exemplo:

Para obter o tempo da data de começo e fim, usaremos as funções HORA, MINUTO, SEGUNDO (HOUR, MINUTE, SECOND em inglês) para preencher os argumentos da função TEMPO. Estando a data de começo e fim de um apontamento nas células A2 e B2 respectivamente:

Para a mediana temos a função MED (MEDIAN em inglês).

E finalmente para o cálculo de dias completos, usamos a função DIAS([começo], [fim]) (DAYS em inglês), mas como ele conta o último dia, vamos subtrair de 1.

Para o turno A, por exemplo teremos:

Caso o apontamento tenha começado e terminado no mesmo dia, a função continuará válida pois o tempo a mais contado no primeiro e último dias serão descontados no valor negativo que resultará na conta dos dias intermediários. Essa fórmula também funciona para o turno B.

Já para o turno C temos uma pequena complicação na qual o turno é dividido no dia (de manhã até as 6h e à partir das 22h52 à noite). Nesse caso não temos opção senão considerar como dois turnos diferentes (um começando às 0h00 até 6h e outro das 22h52 ate 24h00) e somá-los. Como TEMPO(24;0;0) é considerado como 0:00:00, tive que mudar a função de mediana para a função de MAX. Aproveitei também para trocar a outra mediana pela função MIN apenas para retirar o valor TEMPO(0;0;0) da fórmula que já era grande.

No final temos, para os três turnos:

Aí seria só você mudar as referências para as células certas na sua tabela.

(Se você souber programar em VBA eu recomendaria criar uma função para isso, já que na fórmula que criei tem muitas repetições e ficou relativamente longa, mas ainda é válida.)

Espero ter ajudado.