Sorteio
Concorra a três cursos on-line totalmente grátis:
1. Web Marketing
2. Técnicas de Vendas
3. Marketing Pessoal
Dicas de Leitura
Shopping Weblivre
Criando um Banco de Horas no Microsoft Excel - Parte 1
Aprenda agora como criar o famoso Banco de Horas no Microsoft Excel 2003. Veja como calcular o número de horas trabalhadas e quanto receber por elas.
Grau de Dificuldade: 7
Olá turma!
Hoje vamos aprender como criar um Banco de Horas com o auxílio do Microsoft Excel. Nesse tutorial, também vamos conhecer algumas dicas importantes para lidar com um dos tipos de dados mais chatos que existem no Excel: o formato Hora.
Pra começar, você precisa saber que o Excel reconhece / trata as horas como se elas fossem um valor serial, ou seja, 24 horas (um dia inteiro) corresponde ao número inteiro 1 (hum) e meio dia (12 horas) corresponde ao número decimal 0,5 (metade de um inteiro).
Para testar a informação supracitada, basta que você abra o Excel e digite os valores de 01:00 até 24:00 em uma coluna. Repita o mesmo processo na coluna ao lado, selecione os valores da segunda coluna e vá em Formatar / Células (CTRL + 1). Escolha formato Geral e pressione o botão OK. Voilà, você verá que o Excel converteu em decimal os valores dessa coluna, conforme a imagem abaixo:
Agora vamos deixar de conversa e criar o nosso famoso banco de horas, no qual vou utilizar algumas funções pré-definidas como a função Se. Caso você não conheça a utilidade dessa função, clique aqui para ler o artigo.
1) Ao abrir uma nova planilha (CTRL + O), selecione o intervalo de células A1 até Q1 (A1:Q1) e clique no botão mesclar, conforme a imagem abaixo:
2) Escreva o título BANCO DE HORAS NO EXCEL na célula que foi mesclada e formate-a como achar necessário, conforme a imagem abaixo.:
3) Selecione o intervalo de células A2 até B2 (A2:B2) e clique no botão mesclar. Escreva nessa célula o rótulo “DADOS GERAIS”. Em seguida, digite os valores DATA na célula A3 e Jornada de Trabalho na célula B3.
Observação: Lembre-se que para obtermos duas linhas dentro da mesma célula basta utilizar o atalho ALT + ENTER.
Ao fazer isso, basta que você preencha algumas Datas na coluna A e o correspondente da Carga Horária do funcionário na coluna B, conforme na imagem abaixo:
Observação: Quando estou trabalhando com hora no Excel, geralmente digito o valor desta maneira 8:0, já que o software é capaz de reconhecer esse valor corretamente.
4) Agora, selecione o intervalo de células C2 até D2 (C2:D2) e clique no botão mesclar. Escreva nessa célula o rótulo “MANHÔ. Em seguida, digite os valores HORA DE ENTRADA na célula C3 e HORA DE SAÍDA na célula D3. Ao fazer isso, basta que você preencha algumas Horas na coluna C e D, conforme na imagem abaixo:
5) Agora, selecione o intervalo de células E2 até F2 (E2:F2) e clique no botão mesclar. Escreva nessa célula o rótulo “TARDE”. Em seguida, digite os valores HORA DE ENTRADA na célula E3 e HORA DE SAÍDA na célula F3. Ao fazer isso, basta que você preencha algumas Horas na coluna E e F, conforme na imagem abaixo:
6) Agora, selecione o intervalo de células G2 até H2 (G2:H2) e clique no botão mesclar. Escreva nessa célula o rótulo “HORAS TRABALHADAS”. Em seguida, digite os valores HORA JORNADA na célula G3 e hora extra na célula H3, conforme a imagem abaixo:
7) Clique na célula G4 e insira a função =SE(((F4-E4)+(D4-C4))>B4;B4;(F4-E4)+(D4-C4)) para que possamos obter o número de horas cumpridas DENTRO DA JORNADA NORMAL de oito horas de cada funcionário:
Explicando o TESTE LÓGICO:
=SE(((SAÍDA TARDE-ENTRADA TARDE)+(SAÍDA MANHÃ-ENTRADA MANHÃ))>JORNADA;
OU SEJA: =SE(((17:00-13:00)+(12:00-8:00))>08:00;
Portanto, se a soma das horas trabalhadas (4 de manhã + 4 de tarde) for maior que a jornada de 8 horas diária fica claro que o funcionário fez hora extra. Então vamos exibir o valor verdadeiro que é exatamente de 8 horas. Não faz sentido exibir o número de hora extra juntamente com o numero de horas trabalhadas, pois esse cálculo será feito ao lado. Então vamos supor que o funcionário fez o seguinte: (20:00-13:00)+(12:00-8:00))>08:00;
Note que ao sair às 20:00, esse funcionário cumpriu o turno de 8 horas trabalhadas e mais 3 horas extras, totalizando um total de 11 horas trabalhadas. Só que na célula G4 vamos exibir somente o numero de horas trabalhadas pela jornada obrigatória, separando-a das horas extras que serão automaticamente calculadas na célula H4.
Explicando o VALOR VERDADEIRO:
Como foi dito, se o resultado do teste lógico for maior que a jornada de trabalho, vamos dizer somente que o funcionário cumpriu a jornada, conforme a imagem abaixo:
Explicando o VALOR FALSO:
Se o resultado do teste lógico for menor que a jornada de trabalho significa que o funcionário saiu mais cedo, ou seja, não cumpriu sua jornada diária de 8 horas. Então devemos calcular sua jornada conforme a imagem abaixo:
Então vamos supor que o funcionário fez o seguinte: (14:00-13:00)+(12:00-8:00))>08:00;B4; CALCULE ENTÃO O NUMERO DE HORAS TRABALHADAS
Note que ao sair às 14:00, esse funcionário NÃO cumpriu o turno de 8 horas trabalhadas, totalizando um total de apenas 5 horas trabalhadas. O teste lógico supracitado retornará o valor FALSO que é exatamente a soma do número de horas trabalhadas.
8) Agora vamos descobrir o número de HORAS EXTRAS que o funcionário fez em um determinado dia. Clique na célula H4 e insira a função =SE(((F4-E4)+(D4-C4))>B4;((F4-E4)+(D4-C4))-B4;0) para que possamos obter o número de horas extras de cada funcionário:
Explicando a função:
Se o resultado da soma de horas trabalhadas > jornada; subtraia esse número da jornada diária; caso contrário exiba ZERO de hora extra.
Exemplo:
Veja que no exemplo acima, o funcionário trabalhou 11 horas e 11 é maior que 8. Portanto, somamos o numero de horas trabalhadas 7 + 4 = 11 e subtraímos o resultado dessa soma 11-8, totalizando 3 horas trabalhadas. Caso o numero de horas trabalhadas não fosse maior que oito horas, seria exibido zero hora extra.
É isso aí turma, no próximo artigo vamos continuar a implementar nosso Banco de Horas calculando o total de horas, minutos e segundos trabalhados, bem como o valor a receber de acordo com o número de horas cumpridas. Qualquer dúvida, mande um e-mail para ronaldosdb@weblivre.net.
Caso deseje baixar a planilha utilizada nesse artigo, clique aqui.
Um forte abraço,
Ronaldo dos Santos Silva
Autor: Ronaldo dos Santos Silva
Compartilhe:
Comentários
brunairineu em 03/10/2008
gostaria de um modelo, ou de adequar este a minha empresa, onde acumulamos as horas trabalhadas de um mes para outro. é possivel?
Marilene em 17/09/2008
òtima está planilha, estou precisando de uma, como faço para adquerir?
Vera Lucia em 14/09/2008
Ola Ronaldo,
Excelente trabalho...Parabens!!!
Pedro Ornellas em 03/08/2008
Olá adorei a explicação, só que preciso calcular tb pelas faixas de horários, ex: 18:00 até 20:00 50% HE, 20:01 em diante 75% HE, obrigado, me ajude.
cristian Bueno em 31/07/2008
bom dia, gostaria se possivel que me fosse enviada uma planilha em excel para calculo de banco de horas para aperfeiçoamento no modelo encinado acima.agradeço e aguardo contato
Cleid em 31/07/2008
Olá, bom dia, você pode enviar uma planilha de banco de horas pra minha empresa? Grato, aguardo seu contato.
Jorge Luiz Pinheiro Nunes em 29/07/2008
para calculoos semanis
alcides em 29/07/2008
Gostei das explicações, porém para ficar completo deveria extrair por exemplo de uma Jornada das 21:00 à 6:00, a quantidade de H noturnas. Um abraço
AGNALDO em 29/07/2008
preciso fazer uma planilha neste modelo.
data: 01/07/2008
hora: 01:17 alt(mare) 1,3
hora: 07:49 alt(mare) 0,1
preciso fazer um formula que calcular o intervalo de em hora em hora e minuto.
elaine em 23/07/2008
planilha de soma de hora extra.
Cristiano Medeiros em 21/07/2008
Show
José Inácio Gonçalves em 16/07/2008
Gostaria de estar recebendo planilhas das areas tributárias e Fiscal.
EDERSON TAQUES NASCIMENTO em 13/07/2008
BOA TARDE PROFESSOR RONALDO,
TENHO UMA PLANILHA DE HORAS EXTRAS PARA TERMINAR, POREM NAO CONSEGUIR INSERIR ALGUMAS FORMULAS, POR EXEMPLO: O FUNCIONÁRIO FEZ 03 HORAS E 16 MINUTOS NA SEGUNDA FEIRA, POREM, NO TOTAL, TEM QUE SOMAR 03:30 MINUTOS, POIS ACIMA DE 15 MINUTOS CONSTA-SE 30 MINUTOS E NAO 16 MINUTOS, E ABAIXO DE 15 MINUTO, CONTA-SE APENAS 03:00 HORAS.
charles em 11/07/2008
Olá bom dia você como enviar uma planilha de banco de horas pra minha empresa??????
agradeço
aguardo seu contato
Patricia em 08/07/2008
oi, gostaria de saber mais sobre banco de horas extras. já q to comersando à aplica agora .
Wladimir C. e Silva em 03/07/2008
Prezado.
Bom dia.
Encontrando esse seu (precioso) material, gostaria de saber da possibilidade de envio de uma planilha para banco de horas de uma empresa com 100 funcionários e qual o custo para envio da mesma.
Desde já agradecido,
Wladimir
Edson em 17/06/2008
Caro,
Repito a solicitação da colega, pois não visualizo o total descrito.
Obrigado.
Gostaria de receber um modelo de como calcular o banco de horas no Excel, mas que me desse o total de minutos ou horas extras de cada funcionário, e que no final me desse o total de folgas que cada funcionário tem direito de receber devido as horas extras de cada mês. Agradeço desde já!!
Letícia Clemente dos Santos em 16/06/2008
Gostaria de receber uma planilha com banco de horas e cálculo de horas.
Grata
JOSE SILVA DE OLIVEIRA em 07/06/2008
Planilha Ótima, pratica, e util, parabéns, (por gentileza poderia verificar a celula (J7) da planilha), acho que está faltando a formula, Obrigado.
Magda Teodoro Silva em 03/06/2008
Gostaria de receber mais informações sobre dpto pessoal
Julio Pereira em 01/06/2008
Olá, preciso calcular as horas mas os turnos são da seguinte maneira:
23:00 ás 7:20
7:00 ás 15:20
15:00 ás 23:00
todos com 1 hora de descanço, como posso fazer para a planilha calcular as horas extras corretamente.
tania goncalves machado em 30/05/2008
gostaria de esta recebendo artigos na areoa de dp.obrigada
Vania Mariano em 27/05/2008
Comentário: Gostaria de receber um modelo de como calcular o banco de horas no Excel, mas que me desse o total de minutos ou horas extras de cada funcionário, e que no final me desse o total de folgas que cada funcionário tem direito de receber devido as horas extras de cada mês. Agradeço desde já!!
Fabiana Baptista em 25/05/2008
Excel_lente as suas planilhas.
não foi dificil as suas explicações.
Elaine em 13/05/2008
Há a possibilidade de me passar essa planilha??
Ricardo em 12/05/2008
preciso de planilhas para fazer banco de horas de 90 funcionários...
Selma em 08/05/2008
Vc poderia, por favor me enviar uma planilha com o Banco de Horas, preciso uma com tolerancia de horario. obrigado
eliomar araujo em 07/05/2008
boa tarde gostaria de receber uma planilha que faça calculos de horas e banco de horas.
obrigado.. eliomar
delio nogueira em 05/05/2008
Boa tarde,
Estou precisando de uma planilha para controle de banco de horas, porem a mesma precisa ter os campo de hora extra e hora faltosa.
Att;
Délio Nogueira
Rebeka de Campos em 05/05/2008
Bom dia!
Adorei essa planilha de banco de horas, porém, necessito de uma planilha com tolerância de horário, pois meu banco de horas tem tolerância de 5 min.
Como faço?
Obrigada.
Att. Rebeka
vanessa em 22/04/2008
Gostaria de receber um modelo de como calcular o banco de horas no Excel, mas que me desse o total de minutos ou horas extras de cada funcionário, e que no final me desse o total de folgas que cada funcionário tem direito de receber devido as horas extras de cada mês.
Agradeço desde já!!
Marcos em 08/04/2008
Excelente....
Luciene em 06/04/2008
OLA EU GOSTEI MUITO DESTE SITE AJUDOU ME EU FAZER MEU BANCO DE HORAS
JOSÉ AILTON FREIRE em 05/04/2008
Conteudo muito bom, facil entendimento. A abrangencia de assunto é louvavel.
mario em 01/04/2008
quero receber infomativos
Ligia Oshiro em 27/03/2008
Preciso de um extrato de banco de horas para fornecer a funcionários. Alguém pd me ajudar
Viviane Tomé em 26/03/2008
Boa Tarde venho novamente estou querendo transformar as horas que foram convertidaspara decimal(da planilha de hora extra Banco de Horas) para saber quantas folga terá que ser dada.
Muito obrigada.
Viviane Tomé
Viviane Tomé em 25/03/2008
Boa Tarde gostaria de receber uma planilha que calcule a quantidade de horas extras incluindo calculo de adcional noturno.
Muito Obrigada..
Viviane Tomé
Fabricio em 25/03/2008
Bom dia pessoal . gostaria de receber uma planilha de calculo de horas trabalhadas que tivesse a data, dia da semana , entrada ,saida ,entrada , saida , horas normais , horas extras 60% , horas estras de 100 % , adicional noturno 30% ...( segunda a quinta das 7:00 as 17:00 hs = horas normais ) ... ( sexta das 7:00 as 16:00 hs = horas normais , apos 60% ) ... ( sabado o dia todo 60% ) ...( domingo e feriado é 100% ) ... ( adicional noturno é 30% que vai das 22:00 as 6:00 ) sds...
Caroline em 24/03/2008
Gostaria de receber planilhas que calcule horas extras, controle de pagamentos e fluxo de caixa.
ilmar da silva em 20/03/2008
Gostaria de receber uma planilha de controle de chegada e saida de funcionario ou seja uma planilha ponto toda eletronica ou em VBA.
claudia cristina em 27/02/2008
muito bom
Liana em 27/02/2008
Mt interessante seu site...
ANGELA BOEIRA em 26/02/2008
Pessoal, estou a muito tempo procurando uma planilha que eu possa visualizar as horas extras ,pois tenho banco de horas manual, e é um caos.Sou gerente de uma loja e as funcionárias trabalham em horarios variados.Não consegui fazer esta planilha, pois sou leiga neste assunto.Gostaria de recebr uma onde eu só coloque os dados, alguem pode me ajudar??Agradeço,Angela
Lucia em 20/02/2008
Muito interessante esse portal. É uma lista de discussão? Como faço para participar?
Peterson em 04/02/2008
Bom dia! gostaria que me mandassem um modelo de recibo de banco de horas, um exemplo de como eu posso fazer um recibo....Obrigado!
Ana em 23/01/2008
Boa Noite,
A planilha ficou excelente, não fiz mas achei muito criativa, no momento estou precisando de uma planilha onde eu possa ser por ordem de cargo, quantidade e valor de horas extras 50% quantidade e valor de horas extras a 100%, quantidade e valor de ad. noturno. Mês a mês, me pediram com urgência um relatório dos últimos três anos com esses dados. Se alguém tiver alguma com fórmula e por ordem de cargo fico muito grata.
Ana
elivelton em 23/01/2008
parabéns gostaria de receber seus informativos
iran de lima em 22/01/2008
gostaria de receber uma planilha que calcule quantidades de hora extras
DENIZE SOUSA em 10/01/2008
quero receber planinhas de controle de pagamentos,calculos de horas extras.valor hora,
Sandra em 09/01/2008
Olá,
Gente, se tiver alguém aí que possa me ajudar... fiz tudo como manda o Porf., mas não dá certo, na minha planílha dá erro..(??), as células estão todas em "hora", e não funciona, não consigo!!! Se tiver alguém aí, que tiver a planilha Pronta e puder me enviar por e-mail, eu agradeço MUITO. consigo até calcular as horas extras, de uma forma diferente, mas não consigo inserir a tal função SE, dá erro SEMPRE!!! Me ajude!! Obrigada, Sandra.
JOZIVANIA em 26/12/2007
ei manda a planilha a gora fiquei curiosa.
JAIR em 15/12/2007
Ola, hoje eu vi esse tópico. Quando montei minha planilha de horas, há muitos meses atrás, fiquei numa dúvida forte, que esse tópico nao mencionou. E se começar a trabalhar tarde, e parar depois da meia noite? Voces já fizeram o teste de como fica?
Tenho um modelo, onde esses dados são aceitos, creio que ela esteja quase 100% boa, caso alguem queira, me passe e-mail solicitando. jairmattos(a)yahoo
Susana Cardoso em 29/11/2007
Muito Bom!
sebastiao em 31/10/2007
segue em anexo esta exemplo muito creativo
vanessa em 11/10/2007
Obrigada! me ajudou demais!
excelente!
milton em 21/09/2007
obrigado pela ajuda. muito útil.
mary em 08/09/2007
excelente