Calculo de Horas Úteis com Google Sheets - mais difícil do que parece. 🤯

Fórmula para copiar no final do artigo

Se você já precisou fazer alguma operações com data e hora, já deve ter se deparado com algumas dificuldades na hora de extrair a informação correta. Recebi esse desafio de um amigo que teve essa dificuldade e não soube como resolver e então decide compartilhar caso possa ajudar alguém... 🤗

Descrevendo o problema

O setor trata de reclamações e denúncias internas na empresa e precisa monitorar e registrar o tempo de resolução das reclamações/denúncias, porém com a ressalva, as horas precisam ser úteis.

Não sendo por isso o nosso calculo seria mais simples bastando subtrair a hora final menos a hora inicial. Pra nossa sorte o Google Sheets conta com um formula que pode auxiliar a tirar o dias não úteis.

A base de dados segue o seguinte esquema que preenchido diretamente na planilha (o que não é o mais recomendado se a ideia é manter o registro).

Visão geral da base: existem dois tipos de manifestações e ambas possuem dois status - "Em Aberto", "Concluída" - para encontrar a diferença entre dias úteis, basta aplicar função do próprio Google Sheets:

=DIATRABALHOTOTAL(data_de_inicio; data_de_termino; [feriados])

O resultado esperado é um número inteiro de dias, se quisermos converter em horas úteis só precisamos multiplicar pelo valor de horas que a jornada de trabalho dura. Nesse caso o departamento só funciona 8 horas por dia, por boa prática, recomendo informar a máquina qual o tipo de dado você está utilizando, então antes de multiplica utilize a função:

=TEMPO(horas, minutos, segundos)

Nesse ponto vamos deparar com mais um problema: as manifestações que são abertas e finalizadas na mesma data recebem "1" como resultante da função =DIATRABALHOTOTAL(), que é multiplicado por "8" totalizando em 8 horas como tempo de resolução, o que pode acabar penalizando setores que estão respondendo abaixo desse tempo.

"Então, por quê não unir a coluna data e hora, calcular a diferença de dias úteis e colocar no formato de duração?" - Pensei

Após alguns (muitos) testes, a solução mais fácil que consegui idealizar para essa solução foi através do teste das datas, validando se são iguais e calculando a diferença entre horas para os casos positivos e para os casos negativos fiz o calculo do total de dias úteis entre duas datas multiplicados pelo total de horas de jornada de trabalho, menos a diferença entre as horas.

Solução

Nessa solução a função que foi criada poderia receber 4 parâmetros, sendo que as horas poderiam estar em branco e a manifestação concluída ou em andamento com a hora inicial preenchida e a hora final em aberto.

Com a data ela pode receber a hora inicial sem a final e as duas, sem ambas a função acima considera que não há manifestação e o valor devolvido será uma célula em branco. Ou seja a função está apta a lidar com esse diferentes padrões de entrada de dados:

Para copiar:

=SEERRO(IFS(E(NÃO(ISDATE(H43));ÉCÉL.VAZIA(I43);NÃO(ISDATE(J43));ÉCÉL.VAZIA(K43));"";E(ISDATE(H43);NÃO(ISDATE(J43))); DIATRABALHOTOTAL((H43+I43);HOJE())*TEMPO(8;0;0);E(ISDATE(H43);ISDATE(J43);NÃO(H43=J43);ÉCÉL.VAZIA(I43);ÉCÉL.VAZIA(K43));DIATRABALHOTOTAL((H43);(J43))*TEMPO(8;0;0);E(ISDATE(H43);ISDATE(J43);NÃO(H43=J43));(DIATRABALHOTOTAL((H43);(J43))-VALOR.TEMPO(K43-I43))*TEMPO(8;0;0);E(ISDATE(H43);ISDATE(J43);H43=J43);K43-I43);"ERRO INESPERADO")

E você, como resolveria esse desafio?