Controle de horas detalhado

Esta não é a primeira vez que tratamos sobre assuntos relacionados a horas no Excel, já falamos sobre elas nos posts Somar horas, que te ensina a resolver problemas com soma de horas no Excel, que nem sempre saem como esperado, o qual você pode conferir clicando aqui; Horas negativas no Excel, o qual você pode conferir clicando aqui; além do artigo, curtíssimo, Controle de horas noturno que te ensina a como controlar horas de funcionários noturno, que você pode conferir clicando aqui (o controle de horas detalhado, este artigo, não cai nesse problema pelo modo como é montado).

Agora nesse iremos montar um controle de horas detalhado, que avalia 4 horários diferente, entrada, intervalo, volta do intervalo e saída. Sem mais enrolação vamos ver como fazer um controle de horas detalhado no Excel.

Somar horas – https://funcaoexcel.com.br/somar-horas/
Horas negativas – https://funcaoexcel.com.br/somar-horas/
Controle de horas noturnas – https://funcaoexcel.com.br/controle-de-horas-noturnas/

Vídeo – Controle de horas detalhado

O vídeo abaixo apresenta toda a explicação, no detalhe, de como tudo funciona, mas caso queira continuar a leitura, é só continuar mais abaixo.

Coloque o vídeo em tela cheia para assistir normalmente

Controle de horas simples

Este não é o intuito deste artigo, mas só para constar, um controle de horas simples e simplório, perdão pela redundância é muito… como dizer… simples, de fazer.

Digamos que utilizaremos apenas data, hora de entrada, hora de saída e saldo.

A imagem abaixo exemplifica isso, onde na primeira coluna colocamos a data, na segunda a hora de entrada, na terceira a hora de saída e na quarta uma subtração simples entre as duas colunas anteriores.

Com isso já conseguimos ter um certo controle, mas muito superficial, a ideia aqui é algo mais elaborado.

Funções utilizadas neste artigo

Este artigo possui algumas funções, ou combinações de funções do Excel que você precisa conhecer primeiro, antes de dar continuidade, para tanto, caso não conheça ainda o usa das funções a seguir, dê uma conferida nestes links abaixo, clique nas imagens para conferir.

PROCV + CORRESP – https://funcaoexcel.com.br/procv-turbinado-com-corresp-e-validacao-de-dados/
SEERRO – https://funcaoexcel.com.br/escondendo-os-erros-das-formulas-e-funcoes/

Controle de horas detalhado

Neste controle de horas teremos:

Uma tabela guia com os dias e horários que o funcionário deve cumprir.
Controle de horário detalhado com entrada, saída para intervalo, volta do intervalo e saída do trabalho.
Controle de saldo de horas por etapa.
Saldo total de horas por dia.

E como é sempre mais fácil, vamos usar um exemplo aqui, o mesmo utilizado no vídeo acima, então caso tenha dúvidas ao longo do texto, talvez o vídeo possa esclarecer. No final deste artigo deixo o arquivo utilizado para download, já com todas as fórmulas preenchidas.

Partiremos da tabela abaixo, que nos diz quais os dias que o funcionário trabalha, quando folga e qual o horário a cumprir. Essa tabela é interessante pois nosso controle de horas ficará vinculado a ela, fazendo com que qualquer mudança na tabela, altere diretamente o controle de horas, sendo fácil aplicar para diferentes funcionários.

O nosso controle será bem semelhante a essa tabela, mas vamos repetir algumas colunas, essas colunas repetidas servirão como controle individual de saldo, logo você verá que embora ocupe mais espaço visual (inicialmente, pois depois podemos ocultá-las), essas colunas tornam o cálculo mais fácil e compreensível (colunas em vermelho na imagem).

Nas cinco primeiras colunas exibidas na imagem acima, não há nada de especial, são apenas células onde preencheremos manualmente com as informações de datas e horários. São nas 5 últimas colunas que utilizaremos apenas fórmulas.

Controle do horário de entrada

Na coluna referente ao controle da hora de entrada, faremos uma fórmula que irá buscar qual é o horário de entrada do funcionário (lá na nossa tabela de referência) e subtrair esse horário da hora que o funcionário entrou. Então vamos usar um PROCV + CORRESP e também a função SEERRO, esta última é utilizada para quando for um dia de folga e não conseguirmos fazer a subtração, pois ao invés de buscar um horário na tabela de referência, buscaremos a palavra “folga”, calma, tudo vai fazer sentido.

A fórmula vai ficar assim:

=SEERRO(PROCV(DIA.DA.SEMANA($B7,1),$M$8:$R$14,CORRESP(G$6,$M$7:$R$7,0),FALSO)-C7,"Folga")

Que se formos desmembrar, é assim:

Uma função DIA.DA.SEMANA que converte a data em um número de 1 a 7, sendo 1 = domingo e 7 = sábado.
Este resultado e usado como parâmetro de busca para o PROCV que irá até a tabela de referência e com base no dia da semana encontrará qual é o horário de entrada do funcionário.
A função CORRESP é mais opcional, mas já que nossa tabela de controle de horas e a tabela de referência possuem os mesmos cabeçalhos, podemos usá-la para no argumento “núm_índice_coluna” da função PROCV, deixando o negócio mais automático.
Encerrada a nossa função PROCV com um DIA.DA.SEMANA e um CORRESP dentro, subtraímos esse PROCV (hora que o funcionário deve entrar) pela hora que o funcionário de fato entrou.
Para finalizar, envolvemos tudo isso com um SEERRO, para no caso do dia da semana ser um dia de folga do funcionário, que fará com que PROCV retorne um texto e não uma hora, causando um erro na subtração. Caso aconteça algum erro nas demais fórmulas, SEERRO garantirá que o resultado da função seja “Folga”.

Está feita a nossa fórmula de controle de hora de entrada, você já pode sair testando.

Controle do horário de intervalo

A fórmula utilizada aqui é praticamente igual a que acabamos de montar para o controle do horário de entrada, só tem uma diferença, ao invés de subtrair o horário de da tabela de referência, pelo horário que o funcionário de fato saiu para o intervalo, faremos exatamente o oposto, hora registrada menos hora de referência. A fórmula fica assim:

=SEERRO(D7-PROCV(DIA.DA.SEMANA($B7,1),$M$8:$R$14,CORRESP(H$6,$M$7:$R$7,0),FALSO),"Folga")

A explicação da fórmula é a mesma da já descrita acima para o controle de horário de entrada.

Controle de horário de volta do intervalo

A fórmula para controlar a hora que o funcionário volta de seu intervalo é exatamente a mesma utilizada para o controle de horário de entrada, logo:

=SEERRO(PROCV(DIA.DA.SEMANA($B7,1),$M$8:$R$14,CORRESP(G$6,$M$7:$R$7,0),FALSO)-C7,"Folga")

Controle de horário de saída

A fórmula para controlar a hora que o funcionário sai do trabalho é exatamente a mesma utilizada para o controle de horário de intervalo, logo:

=SEERRO(D7-PROCV(DIA.DA.SEMANA($B7,1),$M$8:$R$14,CORRESP(H$6,$M$7:$R$7,0),FALSO),"Folga")

Saldo de horas

A esse ponto você já deve ter notado que estamos fazendo o controle um a um, então sempre sabemos quanto tempo a funcionário está atrasado ou adiantado em cada uma das etapas, e para calcular o saldo disso tudo, é muito simples, é só somar todas as 4 colunas de controle de horário, logo a fórmula para o saldo de horas é:

=SOMA(G7:J7)

Note que todas as fórmulas foram aplicadas conforme a imagem do nosso controle, e somente na primeira linha da tabela, para inserir na tabela toda é só copiar a fórmula da primeira linha que já estará tudo pronto, mas claro, preste atenção nos congelamentos de células feitos nas fórmulas, muito importante.

Sugestões de alterações

A tabela de referência utilizada no exemplo conta com apenas um funcionário, uma ideia seria utilizar ou uma tabela única para todos funcionários, caso todos tenham os mesmo horários de entrada e saída.

Outra forma é fazer uma tabela semelhante utilizando o nome do funcionário, ou algum código de registro, caso o funcionário tenha sempre o mesmo horário, independente do dia.

E uma outra possibilidade é mesclar nome/código do funcionário com o dia da semana, assim você pode ter uma tabela completa onde seu PROCV busca não só o dia da semana, mas também o nome/código do funcionário, ao mesmo tempo.