fechar

Aprender Excel

APRENDER EXCEL

DICAS E NOVIDADES SOBRE EXCEL

Planilha para o cálculo de horas trabalhadas e salário devido 9.0 no Excel

por: Maximiliano Meyer em Planilhas, no dia 11/08 | 17:03 atualizado em 07/11 | 13:00

 Hoje aprenderemos a criar uma planilha voltada a calcular as horas trabalhadas, aplicar o valor da hora e obter o valor devido pelo seu empregador. É o empregador? Então use a mesma planilha para manter os registros de seus funcionários e saber antecipadamente o custo da folha salarial do mês.

Na nossa planilha vamos colocar os intervalos para almoço, a hora de entrada e de saída para obtermos com precisão as horas trabalhadas. Também estão contempladas as horas extras e as horas noturnas.

Aproveite e clique aqui para ver todos os nossos posts sobre finanças pessoais. Tem controle de cartão de crédito, controle de gasolina, salário líquido a receber e muito mais.

E se você precisa de uma planilha mais específica, que gerencie de maneira prática e eficiente o registro e controle dos horários de entrada e saída de funcionários, bem como, horas exatasde entrada e saída e horários de intervalo, confira nossa planilha de Controle de Horas e Folha de Ponto.

Vejamos o seguinte exemplo:

Veja que já temos uma planilha pré-formatada, já preenchida com todos os horários de entrada, saída e intervalo para almoço do referido mês. Agora vamos aprender a calcular as horas trabalhadas em cada dia levando em consideração que é preciso descontar o intervalo.

Vamos utilizar a simples operação matemática de soma, com a sintaxe =(F5-E5)+(D5-C5). Veja o resultado;

Agora estenderemos o cálculo às demais células e teremos os valores para todos os dias trabalhados. Dica: faça isso facilmente com a alça de preenchimento, aprenda aqui a usá-la mesclado com os caracteres especiais, como o $ que tranca a referência.

Agora para calcularmos o total de horas trabalhadas no mês utilizaremos o botão 'Autosoma' que se encontra na guia 'Página Inicial', grupo 'edição'. Repare que após inserir a autosoma você verá que o resultado está 'errado', mas na verdade esta apenas desconfigurado, acontece que pelo fato de calcular como relógio, sempre que chegar as 23:59 ele zera e começa a contar novamente. Para consertar clique com o direito na célula e depois em 'Formatar Célula...'. Na janela que abrir, selecione a opção 'Hora' no menu à esquerda e selecione um formato que aceite mais de 24 horas, pronto.

Agora vamos calcular as horas trabalhadas com base no valor da mesma. Para isso faremos o seguinte: Não podemos apenas criar uma multiplicação de valor moeda por valor em hora, pois o Excel automaticamente interpreta que um dos dados é equivocado, e corrige, fazendo uma divisão, dividindo o valor hora por 24, sendo assim convertido para horas. Veja então que precisamos reverter este cálculo feito pelo Excel. Primeiramente temos de preencher o local específico com o valor da sua hora de trabalho. Ah, e já aproveite para preencher o valor da hora extra, e o percentual acrescido à hora noturna, a quantidade de horas que você deve trabalhar por dia e o seu dia de descanso semanal remunerado. Explicaremos já já a utilidade desses outros dados.

Bom, como dissemos antes precisamos desfazer essa conversão hora x valor monterário que o Excel faz para nós, ou seja, multiplicar por 24. A sintaxe correta para a coluna "Valor devido do dia" será =SE(A4=$N$7;"Folga Semanal";SEERRO((G4*$N$3*24+(I4*$N$4*24))+J4*$N$3*24+(J4*$N$3*24*$N$5);(G4*$N$3*24+(0*$N$4*24))+J4*$N$3*24+(J4*$N$3*24*$N$5))). Explicando resumidamente, pois a fórmula é longa e assusta: aqui estamos calculando as horas trabalhadas naquele dia e já checando se houver horas extras ou noturnas. Se houver o valor sai atualizado com esses complementos. Note que utilizamos os já referidos '$' são para garantir que as referências não serão alteradas, por mais que usemos a alça de preenchimento. Também colocamos ali a fórmula =Seerro necessária para quando não houver valores de horas extras e noturnas. Ahh, e claro que ele só faz todo esse cálculo se não for dia de folga ;)

Lembra que preenchemos o percentual da hora noturna e o esperado de horas a serem trabalhadas por dia? Pois bem, a coluna de horas extras será preenchida automaticamente com base no que você informar como horário de entrada, saída e carga horária de trabalho por dia. A Fórmula utilizada na coluna de h.e. é =SE(G4>$N$6;G4-$N$6;" - "). Já as horas noturnas você precisa inserir manualmente, já que depende muito do contrato que o trabalhador tem com o seu empregador.

Note no exemplo abaixo que como o funcionário trabalhou 08:15 e eu horário esperado era 08:00, os 15 minutos adicionais foram imediatamente para a coluna de horas excedentes, sendo calculados como tal.

Apenas para não passar em branco: na coluna que calcula as horas faltantes do dia temos a fórmula =SE(A4=$N$7;"Folga semanal";SE(G4<$N$6;$N$6-G4;" - ")) Ela leva em conta o dia da semana e só faz a conta caso não seja seu dia de folga. Nessa conta a planilha considera as horas realmente trabalhadas e aquelas que deveriam ter sido trabalhadas. Apenas um simples subtração.

Temos ainda um banco de horas na nossa planilha =) Preencha o campo referente às horas que devem ser trabalhadas no mês e com base no total de horas que você trabalhar, vai saber se ficou devendo horas (exibido na cor vermelha) ou se ficou com o saldo positivo (mostrado em azul).

Por fim veja que há um belo resumo com tudo que você devera receber ou pagar naquele mês. Temos o valor devido pelas horas regulares, horas extra, horas noturna e o total do mês.

Observação importante 1: Caso você comece a trabalhar em um dia e termine no outro, não coloque horário de saída como 02:00 (2 da manhã), mas sim como 26:00 (24 horas + 2 horas). Se você colocar 26 o Excel irá retornar erro.

Observação importante 2: Se o seu horário de entrada for às 15 horas da tarde e o horário de saída às 24 horas. Coloque como "horário normal" até às 18 horas e coloque o restante (= 6 horas do expediente) na coluna de horas noturnas. Só assim o adicional de hora noturna será calculada devidamente.

Observação importante 3: Quando for adicionar um horário coloque no formato hh:mm, somente assim você garante que dará tudo certo.

Observação importante 4: Para indicar a sua folga semanal não esqueça que as dapalvras tem de estar idênticas. O tipo que você usar na coluna "A" deve ser o mesmo formato a ser escrito em N7. Exemplo: Se for "segunda" na coluna A escreva "segunda" em N7, se for "Segunda-feira" atente para escrever "segunda-feira". Neste caso não importam maiúsculas e minúsculas. 

Agora sim estamos com tudo pronto, pessoal. Com essas informações em mãos fica fácil calcular o imposto de renda retido na fonte, contribuição ao INSS, FGTS, quanto você tem a receber, etc. Aproveite.

Abaixo segue o download da planilha.

E se você precisa de uma planilha mais específica, que gerencie de maneira prática e eficiente o registro e controle dos horários de entrada e saída de funcionários, bem como, horas extas e horários de intervalo, confira nossa planilha de Controle de Horas e Folha de Ponto.

Problemas com o download?

Veja nosso tutorial e saiba como resolver qualquer tipo de problema que você enfrentar para baixar, abrir ou executar nossas planilhas.

Versão 2.0

Hoje, 04/02/2015 atualizamos a planilha para a versão 2.0. Esta versão tem a opção de inserção e cálculo também das horas extra, como foi pedida nos comentários. Confira, clicando acima.

Versão 3.0

Colocamos hoje, 07/05/2015 a versão 3.0 que permite a inserção de horas noturnas. O pedido veio pelos comentários. Confira, clicando acima.

Versão 4.0

Colocamos hoje, 05/10/2015 a versão 4.0 que contém uma explicação sobre a utilização e inserção de horas noturnas. Confira abaixo como ficou:

Versão 5.0

Adicionamos hoje, 07/10/2015, uma nova versão com novas funções, algumas delas pedidas nos comentários. Agora você pode, por exemplo, definir um total esperado de horas trabalhadas por mês. Assim, a planilha vlhe dizer ao final dos 30 dias se ficou devendo ou sobrando horas. Outra coisa bem bacana é que voce define a quantidade de horas a serem trabalhadas por dia, por exemplo, 8 horas, e, automaticamente, ele lhe diz quantas horas faltaram ou sobraram naquele dia. E o mais legal é que tudo aquilo que ficar a mais, seja 1 minuto ou 1 hora irá para a coluna de horas extras =) Nessa versão também refizemos o post completamente, explicando tudo de novo com as infos atualizadas.

Versão 6.0

Adicionamos hoje, 15/10/2015, uma nova versão que inclui a possibilidade de inserir os dias da semana e também uma nova disposição das colunas que agora aceita valores para salários acima de 100 mil reais.

Versão 7.0

Adicionamos hoje, 20/10/2015, uma nova versão que corrige o bug que não ignorava o dia de domingo. Ahh, e se o seu dia de folga semanal não for o domingo você pode especificar outro qualquer.

Versão 8.0

Adicionamos hoje, 03/11/2015, uma nova versão que corrige alguns erros de fórmulas e traz a possibilidade de colocar até 2 dias de folga na semana.

Versão 8.1

Adicionamos hoje, 13/09/2016, uma nova versão que corrige alguns erros de fórmulas, traz uma nova tela de apresentação e mudança no VBA.

Versão 9

Adicionamos hoje, 07/11/2016, uma nova versão que agora possui a opção de até 2 dias de folga remunerada por semana, a inserção de feriados, entre outros recursos.

Qualquer nova sugestão nos avise e faremos uma planilha 10.0, cada vez melhor.

Aproveite e clique aqui para ver todos os nossos posts sobre finanças pessoais. Tem controle de cartão de crédito, controle de gasolina, salário líquido a receber e muito mais.