Blog

Postado em em 28 de outubro de 2019

Horas Extras Excel – Como criar um BANCO de HORAS no Excel

Nessa publicação vou te mostrar como se cria um banco de horas, calculando horas extras excel e também as horas negativas!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para baixar a planilha utilizada nessa aula clique aqui!

O que é um Banco de Horas?

O banco de horas nada mais é do que o armazenamento das horas geralmente relacionadas a trabalho feitas por um trabalhador. Esse banco é muito útil tanto para o empregado quanto para o empregador, pois com ele é possível fazer uma análise de quantas horas extras foram feitas, de quantas horas estão devendo e até mesmo o salário referente as horas extras.

Como muitas empresas tem o sistema de ponto e as vezes o funcionário fica na dúvida de como está o seu horário para saber se precisa pagar ou horas ou se tem horas extras é importante construir um banco de horas para que consiga fazer essa análise de uma forma mais completa.

 

Quando fazer contas de Horas Extras Excel?

Como foi dito a pouco vamos utilizar um banco de horas sempre que precisarmos fazer análises de horas trabalhadas para ter um controle desse tempo, seja para verificar as horas que o funcionário está devendo ou horas extras que ele possui e por fim verificar até a quantidade em valor de horas extras que ele irá receber ao final do mês ou da semana.

 

Como fazer calculo de Horas Extras Excel?

Como já temos um modelo de planilha com alguns dados vamos utilizar ele para que possamos aprender como fazer todos os cálculos necessários para a análise do funcionário.

 

Tabela inicial do Banco de Horas

Tabela inicial do Banco de Horas

 

Nesta tabela é possível observar os horários de entrada e saída do funcionário durante toda uma semana de segunda a sexta. A partir dessa tabela vamos fazer os cálculos de horas trabalhadas, horas extras, total e salário extra.

O primeiro passo é calcular as horas trabalhadas, para isso será utilizada a seguinte fórmula.

 

Fórmula para contabilizar as horas trabalhadas

Fórmula para contabilizar as horas trabalhadas

 

Estamos subtraindo o horário de saída menos o horário de chegada, e em seguida estamos retirando o período de almoço que o funcionário não está trabalhando. Caso fique difícil entender essa fórmula o usuário poderá utilizar F3-E3+D3-C3, que seria o horário que o funcionário fez no período da tarde mais o horário no período da manhã, as duas formas vão gerar o mesmo resultado.

OBS: Caso o usuário queira adicionar mais uma informação a tabela pode colocar uma coluna para informar o período que o funcionário tirou no horário de almoço, pois existem algumas regras para esse horário, então dessa forma é possível controlar melhor.

Agora basta arrastar a fórmula para as demais células e vamos prosseguir para o cálculo das horas extras de cada dia para esse funcionário.

 

Fórmula para o cálculo de horas extras excel

Fórmula para o cálculo de horas extras excel

 

Para calcular a hora extra basta pegar a quantidade de horas trabalhadas e subtrair da quantidade da jornada desse trabalhador, então teremos os resultados das horas extras daquele dia. Em seguida basta copiar a fórmula para as outras células. Lembrando de utilizar o trancamento na célula K2 (pressionando a tecla F4), pois essa referência não pode mudar.

 

Resultado das horas trabalhadas e horas extras excel

Resultado das horas trabalhadas e horas extras excel

 

É possível verificar que na célula H7 temos vários ######, isso é uma forma do Excel de indicar ao usuário que temos um valor de hora negativa e o Excel não faz essa representação de horas negativas, por isso ele nos mostra esse “aviso”. No entanto é possível fazer uma alteração na configuração do Excel para que possamos trabalhar com horas negativas, para isso basta ir até a guia Arquivo e em seguida em Opções. Feito isso será aberta a janela de opções dentro do Excel.

 

Janela de opções do Excel

Janela de opções do Excel

 

Dentro dessa janela vamos até ao menu Avançado e vamos descer até encontrar a opção de Ao calcular esta pasta de trabalho. Aqui será marcada a opção Usar sistema de data 1904.

 

Marcando a opção para utilizar o sistema de data 1904

Marcando a opção para utilizar o sistema de data 1904

 

Depois basta pressionar OK e verificar que a data negativa que havia gerado um erro no Excel agora está correta, mostrando um horário negativo.

 

Valor negativo de hora aparecendo normalmente

Valor negativo de hora aparecendo normalmente

 

Feito isso é possível agora fazer a soma tanto das horas trabalhadas quanto das horas extras, para isso podemos utilizar a fórmula SOMA e selecionar os intervalos, no entanto existe um atalho para utilizar essa fórmula de forma mais rápida, esse atalho é ALT + =. Desta forma o Excel já coloca a fórmula soma selecionando todos os números acima da célula contendo a fórmula.

 

Somando as horas trabalhadas

Somando as horas trabalhadas

 

Vamos repetir o mesmo procedimento para obter o total de horas extras.

 

Resultado da soma das horas trabalhadas e horas extras excel

Resultado da soma das horas trabalhadas e horas extras excel

 

OBS: É possível que quando o usuário fizer a soma das horas o Excel retorne apenas no formato de 24h, ou seja, ele pode não mostrar o horário superior a 24h por conta da formatação. Para isso vamos ter que alterar a formatação para permitir que o Excel mostre o somatório sem essa restrição de horário. Para isso basta selecionar a célula desejada e pressionar o atalho CTRL + 1.

Feito isso será aberta uma janela de formatação de células, dentro dessa janela vamos até a categoria Personalizado e dentro da caixa de texto de Tipo vamos escrever o seguinte formato: [h]:mm;@.

 

Alterando o formato da célula para o somatório passar o limite de 24h

Alterando o formato da célula para o somatório passar o limite de 24h

 

Essa formatação vai permitir que o Excel faça os cálculos sem o limite das 24h, ou seja, podemos verificar a soma normal do horário total.

Agora vamos partir para a tabela ao lado que possui algumas informações que precisam ser preenchidas.

 

Tabela com dados financeiros a ser preenchida

Tabela com dados financeiros a ser preenchida

 

O primeiro valor a ser preenchido é a quantidade de horas trabalhadas por mês, como o funcionário tem uma jornada de 8h e o mês tem aproximadamente 22 dias úteis vamos apenas multiplicar 8 por 22.

 

Cálculo da quantidade de dias trabalhados no mês

Cálculo da quantidade de dias trabalhados no mês

 

Para obter o valor por hora que o funcionário ganha, basta dividir o salário pela quantidade de horas trabalhadas.

 

Fórmula para obter o valor da hora trabalhada

Fórmula para obter o valor da hora trabalhada

 

Para a hora extra temos que o valor é geralmente mais alto do que o valor normal pago, portanto neste caso vamos utilizar a hora extra sendo 50% a mais do que o valor normal.

 

Fórmula para obter o valor da hora extra

Fórmula para obter o valor da hora extra

 

Agora temos todas as informações desta tabela preenchida, falta apenas preencher qual o salário extra que esse funcionário ganha.

 

Tabela com todas as informações preenchidas

Tabela com todas as informações preenchidas

 

Para fazer a conta do quanto o funcionário ganha pelas horas extras basta multiplicar a quantidade de horas extras pelo valor da hora extra, mas como estamos trabalhando com horas e valores temos uma peculiaridade, vamos ter que além de fazer essa multiplicação, fazer mais uma pelo número 24, que corresponde a quantidade de horas.

Isso se dá porque o Excel atribui a cada hora uma fração das 24h do dia, ou seja, o horário que é mostrado é representado pelo horário dividido por 24, por esse motivo temos que multiplicá-lo por 24 para obter o resultado correto.

 

Fórmula para obter o valor do salário de horas extras excel

Fórmula para obter o valor do salário de horas extras excel

 

Com isso vamos obter o valor correto do salário extra que o funcionário irá receber ao final dessa semana.

 

Banco de dados preenchida com horas extras excel

Banco de dados preenchida com horas extras excel

 

Então temos a tabela completa com tudo calculado para uma análise mais completa do funcionário com total de horas trabalhadas, total de horas extras e salário extra.

Para continuar lendo outras publicações da hashtag, acesse: www.hashtagtreinamentos.com/blog

Nosso curso de excel funciona para qualquer um, desde a pessoa que não sabe absolutamente nada de Excel até aquela pessoa que quer aquele algo a mais para impressionar!


Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?